SQL Server 2008 - Update system catalog from stored procedure
We had some old stored procedures in SQL Server 2000 which updated system catalog from an application which was used for user application security which is tied to SQL Server roles to take advantage of SQL Server in-built security and NT logins.
When we migrate DB to SQL Server 2008 and try to run these stored procedure we get SQL Server 2008 error
Ad hoc updates to system catalogs are not allowed.
I searched around and found that from SQL Server 2005 onwards MS do not support catalog updates (unless using Dedicated Administrator Connection (DAC) ).
If anyone can help me with how to do this in new versions or any other alternative (like .Net code run in Sql server??) that will be great.
Some sample queries below
update sysusers
set roles = convert(varbinary(2048), substring(convert(binary(2048), roles), 1, @ruidbyte-1)
+ convert(binary(1), (~@ruidbit) & substring(convert(binary(2048), roles), @ruidbyte, 开发者_运维技巧1))
+ substring(convert(binary(2048), roles), @ruidbyte+1, 2048-@ruidbyte)),
updatedate = getdate()
where uid = @memuid
delete from syspermissions where grantee = @uid
delete from sysusers where uid = @uid
insert into sysusers
values(@uid, 0, @rolename, NULL, 0x00, getdate(), getdate(), @owner, NULL)
There is no routine reason to update system tables in SQL Server. Ever.
There is a whole array of commands and system stored procedures to do it properly in all versions of SQL Server.
You can't anyway as you noted: there is no hack or workaround
Updating a system table can be a dangerous task, as it may lead to unexpected results. So before you do that just make sure that you are very confident with whatever you are doing. It would be advisable that you do the changes on a replica of the original database to prevent unwanted results or crashes in the database.
The possible ways could be:
Use a DAC. You can get the technique after searching it on google. It is more of hacking the system tables.
Use the following code:
sp_configure 'allow updates',1 go
//your code
reconfigure with override go
reconfigure would configure back your database. setting 'allow updates' to '1' would allow you to update the system tables.
But the best way would be to find an another alternative for your task.!
精彩评论