Sql-server – How to delete and create user remotely on SQL Server 2000

sql serversql-server-2000

My boss wants to make sure that no one runs a job accidentally so we are setting up lots of safeguards to ensure that no one sleepwalks, log into the system and run a job. We are updating the subscription at corporate in a merge replication so that it will populate back to the publisher which are spread out across 9 states. That process works great but if it is during business hours, the publisher will override some of the changes that we made to the database.

So to make sure that no one makes any changes to the database, we have decided to delete the user account at the remote locations so that the users can not make changes at 3 am, many, many hours after business hours but some of our managers have been known to come rather early.

I am running into security issues when I try to delete and then after the job is finished, create the user back again. Using a fully qualified name, it will delete the user but throw an error but won't even think about creating a user.

EXEC [server].databaseName.dbo.sp_revokedbaccess N'<user>'
GO 

results in "User has been dropped from current database.

Msg 0, Level 11, State 0, Line 0
A severe error occurred on the
current command. The results, if any, should be discarded.

but the user really has been dropped.

My create user statement and sp_addrolemember just throws up an error and doesn't run at all.

Any ideas on how I can accomplish this? All of our SQL Server 2008R2 don't have any issues and this is not required but we still have just over 100 SQL Server 2000 machines in the wild that I need to do this to.

Best Answer

I found a workaround

To change the permissions of the user to where it can't write

EXEC [Server].Database.dbo.sp_droprolemember N'db_owner', N'User'
EXEC [Server].Database.dbo.sp_addrolemember N'db_denydatawriter', N'User'

After I am done, change it back

EXEC [Server].Database.dbo.sp_droprolemember N'db_denydatawriter', N'User'
EXEC [Server].Database.dbo.sp_addrolemember N'db_owner', N'User'

Just tested it in my lab environment and it does the trick. Can't wait to get rid of all of our SQL Server 2000 databases! Just a few more months and we will be rolling 120 servers into one SQL Server 2012