How can I deny access to specific databases from a login? I know that I can walk through and make the login a db_owner on all the ones I want the login to have access to, but how do I do the opposite? I want the login to have access to everything but the ones that I specify
Sql-server – Deny access to one database, allow all the rest
Securitysql-server-2008-r2
Related Solutions
You should be able to just deny permissions on the entire sys and information_schema schema as a whole:
DENY SELECT On SCHEMA::sys To [user_name]
DENY SELECT On SCHEMA::INFORMATION_SCHEMA To [user_name]
That should basically just prevent that user from doing any selects in those two schemas.
I - To create a login with access to specific database only:
- Use SSMS and connect to the SQL server instance
- Open Security -> right click Logins -> New Login (e.g. login named SE)
3 Enter the login details. In Server Roles, make sure only 'public' is checked
4 In User Mapping, make sure only the vendor database is selected.
If public role for the vendor database is not enough, grant the necessary permissions but only on the vendor database
Another solution is to deny access to all databases and then grant just to the vendor database
DENY VIEW ANY DATABASE TO [SE]
See more info here: http://msdn.microsoft.com/en-us/library/ms177518.aspx
II- How to monitor his activity on database (what query he runs, is he try to took any backup and when)
I suggest using some third party tools. Transaction logs don't capture info about executed queries
III-Deny Any backup and restore task (I want to keep this only to sysadmin)
Having public role will prevent the user to backup or restore databases
he'll get the following messages when he tries to backup or restore a database
T-SQL statement to deny creating backups is:
DENY BACKUP ANY DATABASE to [SE]
Related Question
- Mysql – Allow access for localhost and named server
- Sql-server – How to deny access to see the logins in a database
- SQL Server Login – Access Tables Across Multiple Databases
- Sql-server – Grant access to all objects (with a few exceptions) to a role
- Sql-server – Need to deny DML access through SSMS using web app credentials
- Sql-server – Deny All DDL & DML Commands In Sql Server
- SQL Server Security – How to Deny Writes in All Databases
Best Answer
I would highly suggest you not assign a login to db_owner just "to have access to" a database unless you want them to have "admin" privileges in that database.
You also have to consider what permissions the login has at the instance level first. Assigning a user to
db_denyXXXwill have no affect if the login is sysadmin or securityadmin privileges.If we are talking about a "regular" login on the instance you prevent them from accessing the database by not mapping that login to a user in the database. If no database user exist in the database for the login then they cannot connect to it, as long as they do not have higher privileges at the instance level.