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). Thedb_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_ddladminfixed
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_ddladminfixed 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_backupoperatorfixed
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 anddb_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 anddb_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 thedb_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 anddb_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 anddb_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 anddb_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 anddb_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 anddb_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
thedb_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 thedb_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. Thedb_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];
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
No comments:
Post a Comment