Preserving SQLServer permissions

Question

How can I stop a particular user from accessing the database for a period of time but at the same time not lose the permissions the user has on db objects.

Basically when he comes back (ie when access is given back) he should have all the permissions he had before his access was cut off. If I use sp_revokedbaccess 'myuser' the user myuser loses all permissions he had on db objects.

Is there a way to preserve myuser's permissions without resorting to cumbersome backup permissions and restore permissions kind of workarounds?

Answer 1

How is the user accessing the database? If via an application, then just log the user out and require the user to re-authenticate.

Answer 2

we want to remove access during maintenance but bring him back after it is done.

Have the maintenance process acquire exclusive locks on whatever tables it is processing. This locks everyone out until the processing is complete.

Answer 3

Can you switch the database to single user mode? http://msdn.microsoft.com/en-us/library/ms345598.aspx

Or script up the permissions before you remove them: http://www.sql-server-performance.com/articles/dba/object_permission_scripts_p1.aspx. I know this is "backup permissions and restore permissions" - but this script makes the process a lot less cumbersome.

Answer 4

You should be able to explicitly DENY him a permission, then revoke the deny.

You can also disable the login with ALTER LOGIN ... DISABLE, but will block at server level, not database level.

A hack solution is to map the user to a different login, then map him back (ALTER USER .. LOGIN = ...), but is a hack and I'm not sure even works correctly.

Answer 5

The simplest way i see it is to disable the login. You can do this on the login properties in SSMS under the status page.

You can achieve the same thing with the following T-SQL:

Assuming your login is bob a sql login, but can also be a windows login

ALTER LOGIN bob DISABLE

Then, to enable the login

ALTER LOGIN bob enable

Answer 6

If the current password is known, change the password to a temporary value during the maintenance window. Re-setting the password to its initial value serves this purpose without making any material changes to the account.

Answer 7

If you are using Windows authentication and you can lock out this user from everything on the domain, you can set the times the user is allowed to log on in Active Directory. I can't provide detailed instructions but there should be something on ServerFault by now.

Answer 8

Thoughts:

1. DENY CONNECT user or DENY CONNECT SQL TO login (sp_revokedbaccess is deprecated)

2. You cannot use ALTER_LOGIN with the DISABLE argument to deny access to a Windows group (quoted from link)

3. Assign all rights to a database role, then revoke rights from user. Add user to role when needed. It's a bad idea to assign object rights to users directly anyway.

4. If user has sysadmin rights, you can't deny them anything at all (perhaps CONNECT SQL?)

5. If user has db_owner rights (assumes set up in DB), you can't deny them anything in the database except CONNECT