Debunking the Myths: Cloud HA and DR common misconceptions

Learn more about SQL Server tools



solving sql server problems for millions of dbas and developers since 2006 join MSSQLTips for free SQL Server tips
































Top SQL Server Tools



































   Got a SQL tip?
            We want to know!

Database level permissions for SQL Server 2005 and 2008

MSSQLTips author K. Brian Kelley By:   |   Read Comments (3)   |   Related Tips: More > Security

Problem
I am new to SQL Server 2005/2008 having administered SQL Server 2000 and below. I have heard there are a lot of new permissions at the database level. What are they and what do they give rights to perform?

Solution
SQL Server 2005 introduced a new concept to SQL Server security and permissions: securables. Securables are anything within SQL Server that can have a permission assigned. One such securable is the database.

Below are the list of database-level permissions:

Permission Effect
ALTER This grants or denies the ability to alter the existing database.
ALTER ANY APPLICATION ROLE This grants or denies the ability to create, drop, or alter application roles. The db_securityadmin fixed database role has this permission implicitly.
ALTER ANY ASSEMBLY This grants or denies the ability to create, drop, or alter CLR assemblies. The db_ddladmin fixed database role has this permission implicitly.
ALTER ANY ASYMMETRIC KEY This grants or denies the ability to create, drop, or alter asymmetric keys for encryption. The db_ddladmin fixed database role has this permission implicitly.
ALTER ANY CERTIFICATE This grants or denies the ability to create, drop, or alter certificates for encryption. The db_ddladmin fixed database role has this permission implicitly.
ALTER ANY CONTRACT This grants or denies the ability to create and drop contracts for service broker. The db_ddladmin fixed database role has this permission implicitly.
ALTER ANY DATABASE DDL TRIGGER This grants or denies the ability to create, drop, or alter DDL triggers at the database level (not the server level). The db_ddladmin fixed database role has this permission implicitly.
ALTER ANY DATABASE EVENT NOTIFICATION This grants or denies the ability to create and drop database event notifications for service broker. The db_ddladmin fixed database role has this permission implicitly.
ALTER ANY DATASPACE This grants or denies the ability to create a partition schema within the database. The db_ddladmin fixed database role has this permission implicitly.
ALTER ANY FULLTEXT CATALOG This grants or denies the ability to create, alter, or drop fulltext catalogs within the database. The db_ddladmin fixed database role has this permission implicitly.
ALTER ANY MESSAGE TYPE This grants or denies the ability to create, alter, or drop message types for service broker. The db_ddladmin fixed database role has this permission implicitly.
ALTER ANY REMOTE SERVICE BINDING This grants or denies the ability to create, alter, or drop remote service bindings for service broker. The db_ddladmin fixed database role has this permission implicitly.
ALTER ANY ROLE This grants or denies the ability to create or drop user-defined database roles. The db_securityadmin fixed database role has this permission implicitly.
ALTER ANY ROUTE This grants or denies the ability to create, alter, or drop routes for service broker. The db_ddladmin fixed database role has this permission implicitly.
ALTER ANY SCHEMA This grants or denies the ability to create, alter, or drop schema within the database. The db_accessadmin, db_ddladmin, and db_securityadmin fixed database roles have this permission implicitly.
ALTER ANY SERVICE This grants or denies the ability to create or drop services for service broker. The user also must have REFERENCES permissions for all queues and contracts specified for the service. The db_ddladmin fixed database role has this permission implicitly.
ALTER ANY SYMMETRIC KEY This grants or denies the ability to create, drop, or alter symmetric keys for encryption. The db_ddladmin fixed database role has this permission implicitly.
ALTER ANY USER This grants or denies the ability to create, alter, or drop users within the database. The db_accessadmin fixed database role has this permission implicitly.
AUTHENTICATE Grants or denies the ability to extend impersonation across databases even though explicit access isn't normally permitted.
BACKUP DATABASE This grants or denies the ability to backup the database. The db_backupoperator fixed database role has this permission implicitly.
BACKUP LOG This grants or denies the ability to backup the transaction log of the database. The db_backupoperator fixed database role has this permission implicitly.
CHECKPOINT This grants or denies the ability to issue a CHECKPOINT statement against the database. The db_backupoperator fixed database role has this permission implicitly.
CONNECT This grants or denies the ability to enter the database. When a new user is created, it is granted by default.
CONNECT REPLICATION This grants or denies the ability to connect to the database as a subscriber for the purpose of retrieving a publication via replication.
CONTROL This grants the equivalent to ownership over the database. The db_owner fixed database role has this permission implicitly.
CREATE AGGREGATE This grants or denies the ability to create a user-defined aggregate function defined by an assembly. The REFERENCES permission on the assembly must also be possessed.
CREATE ASSEMBLY This grants or denies the ability to create or drop an assembly within a SQL Server database. If the assembly permission set requires EXTERNAL_ACCESS, the login must also have EXTERNAL ACCESS ASSEMBLY permissions. If the permission set requires UNSAFE, the login must be a member of the sysadmin fixed server role. Unlike ALTER ANY ASSEMBLY, the user must own or have CONTROL permissions on the assembly in order to drop it.
CREATE ASYMMETRIC KEY This grants or denies the ability to create or drop an asymmetric key. Unlike ALTER ANY ASYMMETRIC KEY, the user must own or have CONTROL permissions on the asymmetric key in order to drop it.
CREATE CERTIFICATE This grants or denies the ability to create or drop a certificate. Unlike ALTER ANY CERTIFICATE, the user must own or have CONTROL permissions on the certificate in order to drop it.
CREATE CONTRACT This grants or denies the ability to create a contract for service broker. Unlike ALTER ANY CONTRACT, the user must own or have CONTROL permissions on the contract in order to drop it.
CREATE DATABASE DDL EVENT NOTIFICATION This grants or denies the ability to create and drop database event notifications for service broker. Unlike ALTER ANY DATABASE DDL EVENT NOTIFICATION, the user must own the database DDL event notification in order to drop it.
CREATE DEFAULT This grants or denies the ability to create a default. This permission is granted implicitly to the db_ddladmin and db_owner fixed database roles. In SQL Server 2005 or higher compatibility mode, the user will still need ALTER SCHEMA rights to create one in a particular schema.
CREATE FULLTEXT CATALOG This grants or denies the ability to create and drop fulltext catalogs within the database. Unlike ALTER ANY FULLTEXT CATALOG, the user must own the fulltext catalog in order to drop it.
CREATE FUNCTION This grants or denies the ability to create a function. This permission is granted implicitly to the db_ddladmin and db_owner fixed database roles. In SQL Server 2005 or higher compatibility mode, the user will still need ALTER SCHEMA rights to create one in a particular schema.
CREATE MESSAGE TYPE This grants or denies the ability to create a message type for service broker. Unlike ALTER ANY MESSAGE TYPE, the user must own the message type in order to drop it.
CREATE PROCEDURE This grants or denies the ability to create a stored procedure. This permission is granted implicitly to the db_ddladmin and db_owner fixed database roles. In SQL Server 2005 or higher compatibility mode, the user will still need ALTER SCHEMA rights to create one in a particular schema.
CREATE QUEUE This grants or denies the ability to create, alter, or drop a queue for service broker. The user must own the queue in order to drop it.
CREATE REMOTE SERVICE BINDING This grants or denies the ability to create, alter, or drop remote service bindings for service broker. Unlike ALTER ANY REMOTE SERVICE BINDING, the user must own the remote service binding in order to drop it.
CREATE ROLE This grants or denies the ability to create or drop user-defined database roles. Unlike ALTER ANY ROLE, the user must own or have CONTROL permission over the role to drop it.
CREATE ROUTE This grants or denies the ability to create, alter, or drop routes for service broker. Unlike ALTER ANY ROUTE, the user must own the route in order to drop it.
CREATE RULE This grants or denies the ability to create a rule. This permission is granted implicitly to the db_ddladmin and db_owner fixed database roles. In SQL Server 2005 or higher compatibility mode, the user will still need ALTER SCHEMA rights to create one in a particular schema.
CREATE SCHEMA This grants or denies the ability to create schema in the database. Unlike ALTER ANY SCHEMA, a user with this permission can only drop a schema it owns it or has CONTROL permission over it.
CREATE SERVICE This grants or denies the ability to create or drop services for service broker. The user also must have REFERENCES permissions for all queues and contracts specified for the service. Unlike ALTER ANY SERVICE, the user must own the service in order to drop it.
CREATE SYMMETRIC KEY This grants or denies the ability to create or drop a symmetric key. Unlike ALTER ANY SYMMETRIC KEY, the user must own or have CONTROL permissions on the symmetric key in order to drop it.
CREATE SYNONYM This grants or denies the ability to create a synonym. This permission is granted implicitly to the db_ddladmin and db_owner fixed database roles. In SQL Server 2005 or higher compatibility mode, the user will still need ALTER SCHEMA rights to create one in a particular schema.
CREATE TABLE This grants or denies the ability to create a table. This permission is granted implicitly to the db_ddladmin and db_owner fixed database roles. In SQL Server 2005 or higher compatibility mode, the user will still need ALTER SCHEMA rights to create one in a particular schema.
CREATE TYPE This grants or denies the ability to create a type. This permission is granted implicitly to the db_ddladmin and db_owner fixed database roles. In SQL Server 2005 or higher compatibility mode, the user will still need ALTER SCHEMA rights to create one in a particular schema.
CREATE VIEW This grants or denies the ability to create a view. This permission is granted implicitly to the db_ddladmin and db_owner fixed database roles. In SQL Server 2005 or higher compatibility mode, the user will still need ALTER SCHEMA rights to create one in a particular schema.
CREATE XML SCHEMA COLLECTION This grants or denies the ability to create an XML schema collection. This permission is granted implicitly to the db_ddladmin and db_owner fixed database roles. In SQL Server 2005 or higher compatibility mode, the user will still need ALTER SCHEMA rights to create one in a particular schema.
DELETE This grants or denies the ability to issue the DELETE command against all applicable objects within the database. Best practices say not to use this at the database level, but rather at the schema level.
EXECUTE This grants or denies the ability to issue the EXECUTE command against all applicable objects within the database. Best practices say not to use this at the database level, but rather at the schema level.
INSERT This grants or denies the ability to issue the INSERT command against all applicable objects within the database. Best practices say not to use this at the database level, but rather at the schema level.
REFERENCES This grants or denies the ability to create relationships between objects such as foreign keys on tables referencing other tables or the use of SCHEMABINDING by views and functions. The permission is granted implicitly to the db_ddladmin fixed database role.
SELECT This grants or denies the ability to issue the SELECT command against all applicable objects within the database. Best practices say not to use this at the database level, but rather at the schema level.
SHOWPLAN This grants or denies the ability to see execution plans for queries executing within the database.
SUBSCRIBE QUERY NOTIFICATIONS This grants or denies the ability to create a subscription to a query notification for when the results of a particular query would change.
TAKE OWNERSHIP This grants or denies the ability to transfer ownership of an XML schema from one user to another.
UPDATE This grants or denies the ability to issue the UPDATE command against all applicable objects within the database. Best practices say not to use this at the database level, but rather at the schema level.
VIEW DATABASE STATE This grants or denies the ability to view conditions about the current database via the database-level dynamic management views or functions.
VIEW DEFINITION this grants or denies the ability to view the underlying T-SQL or metadata on objects within the database. The db_securityadmin database fixed server role has this permission implicitly.

While all of these database-level permissions are important, some of the ones to pay particular attention to are:

  • ALTER - can modify the database
  • CONTROL - has ownership of the database. Permissions within the database are bypassed for this user
  • ALTER ANY USER - can manage users within the database
  • BACKUP DATABASE - can create a backup of the database
  • BACKUP LOG - can create a backup of the log file.

Listing Permissions

A quick and easy script you can use to see what permissions are assigned at the database level is the following. It uses the sys.database_permissions catalog view along with sys.database_principals to tie to database users and roles:

SELECT prin.[name] [User]sec.state_desc ' ' sec.permission_name [Permission]
FROM [sys].[database_permissions] sec
  JOIN [sys].[database_principals] prin
    ON sec.[grantee_principal_id] prin.[principal_id]
WHERE sec.class 0
ORDER BY [User][Permission];

Granting Permissions

Granting rights is pretty straight forward.  To grant "ALTER" rights to user "DBUser1" you would issue the following command:

GRANT ALTER TO DBUser1

 

Next Steps



Last Update: 3/30/2009


About the author
MSSQLTips author K. Brian Kelley
K. Brian Kelley is a SQL Server author and columnist focusing primarily on SQL Server security.

View all my tips
Related Resources


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Comments and Feedback:
Friday, April 17, 2009 - 6:09:59 AM - DavidB Read The Tip
I grant database permissions to new database roles I create. Then I assign logins to this new database role. The database role names all begin with "permission_" so they are groups together and I can differentiate them between system databases roles and roles I created.

Wednesday, August 04, 2010 - 5:54:20 PM - Chris Read The Tip
I'm wondering if someone can help me out here.

It appears there is a missing privilege from your list. When granted the db_securityadmin role, one also has the ability to GRANT EXEC on a procedure. The only permissions you show granted to db_securityadmin are: ALTER ANY APPLICATION ROLE, ALTER ANY ROLE, ALTER ANY SCHEMA, CREATE PROCEDURE, and VIEW DEFINITION. When all of these are applied to a user that user cannot GRANT EXEC on a procedure that they just created, but when they are a member of db_securityadmin they can. What is missing?

Here is what I need. I want to give the developers the ability to only read data from any table (db_datareader) and Create/Drop procedures (CREATE PROCEDURE). They also NEED the ability to GRANT EXEC on the procedures they create to a role that the application uses to gain access to the procedures. The only way I have found to grant the GRANT EXEC permission is either make the developer a member of db_securityadmin, or grant them the GRANT SECURITY ON SCHEMA permission. Both of which give way to many other privileges. What is needed here is a GRANT GRANT EXEC ON PROCEDURE type of permission, which doesn't appear to exist. Any ideas how to get around this would be greatly appreciated.

Chris


Wednesday, January 23, 2013 - 10:14:43 AM - Ed - sqlscripter Read The Tip

Great article listing all the grants, nice script also. I use this to fetch sql type login permissions. It is setup this way to flow through sqlcmd\osql and pipe to a log file.

Set nocount ON
Print '==================================================================================================================='

Select @@servername as 'Server_Name'
select Convert(varchar(25),[name]), is_disabled, is_policy_checked, is_expiration_checked,
 create_date, modify_date from sys.sql_logins
where is_disabled = 1 or is_expiration_checked = 1 --or is_policy_checked = 1
Print '==================================================================================================================='
Print ''
Print ''



Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name   *Email Notify for updates



Comments
Get free SQL tips:

*Enter Code refresh code


 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | free t-shirt | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.