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>'

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.

  • Are you deleting and then recreating the same logins/users?
    – Mike Fal
    Jan 17 '13 at 20:41
  • Yes, just to ensure that the person at the remote location can't access the database during this process. Not the login but just the user Jan 17 '13 at 20:59
  • Have you considered simply revoking/denying permission to run jobs instead of deleting the users? I know this alters your approach, but might be a simpler way to solve the issue.
    – Mike Fal
    Jan 17 '13 at 21:04
  • Revoking access deletes the user. Revoking login is only for windows accounts but that did give me another idea - change the users rights Jan 17 '13 at 21:21
  • 3
    Have you considered getting a new boss? Jan 18 '13 at 0:04

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

  • 120 servers into a single one? that's going to be a small beast.
    – Marian
    Jan 18 '13 at 17:03
  • More like 150+. We already have 22 converted to 2008R2. The size really won't be much different than our warehouse Jan 23 '13 at 20:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.