开发者

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:

  1. Use a DAC. You can get the technique after searching it on google. It is more of hacking the system tables.

  2. 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.!

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜