Title: Preserving SQLServer permissions
Tags: sql-server;sql-server-2005;tsql
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?
Comment: Thanks all - I will go with the DENY CONNECT - since my requirement is for sql 2005. I checked sql 2000. In 2000 would have gone for the disable login. Thanks again

Here is the accepted answer: Thoughts:
DENY CONNECT user or DENY CONNECT SQL TO login (sp_revokedbaccess is deprecated)
You cannot use ALTER_LOGIN with the DISABLE argument to deny access to a Windows group (quoted from link)
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.
If user has sysadmin rights, you can't deny them anything at all (perhaps CONNECT SQL?)
If user has db_owner rights (assumes set up in DB), you can't deny them anything in the database except CONNECT

Here is another answer: 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

Here is another answer: 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.

Here is another answer: 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.

Here is another answer: How is the user accessing the database?  If via an application, then just log the user out and require the user to re-authenticate.
Comment for this answer: It is an application user id - we want to remove access during maintenance but bring him back after it is done.

Here is another answer: 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.

Here is another answer: 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.

Here is another answer: 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.

{'source': 'StackExchangeDataset', 'subset': 'stackoverflow.com', 'metadata': {'AnswerCount': 8, 'CommentCount': 1, 'ContentLicense': 'CC BY-SA 2.5', 'CreationDate': '2009:00:08/11/09', 'FavoriteCount': 1.0, 'PostTypeId': 1, 'Tags': '<sql-server><sql-server-2005><tsql>', 'Title': 'Preserving SQLServer permissions', 'ViewCount': 318, 'id': 1263532, 'user_AboutMe': '', 'user_AccountId': 48670.0, 'user_DisplayName': 'OpenSource', 'user_Reputation': 2187.0, 'user_Views': 284.0, 'user_id': 144670.0}}