DBA Sensation

November 12, 2008

Mapping of Fixed Database Roles to Permissions

Filed under: 3. MS SQL Server — zhefeng @ 3:57 pm

db_accessadmin
Granted: ALTER ANY USER, CREATE SCHEMA
Granted: VIEW ANY DATABASE

db_accessadmin
Granted with GRANT option: CONNECT

db_backupoperator
Granted: BACKUP DATABASE, BACKUP LOG, CHECKPOINT
Granted: VIEW ANY DATABASE

db_datareader
Granted: SELECT
Granted: VIEW ANY DATABASE

db_datawriter
Granted: DELETE, INSERT, UPDATE
Granted: VIEW ANY DATABASE

db_ddladmin
Granted: ALTER ANY ASSEMBLY, ALTER ANY ASYMMETRIC KEY, ALTER ANY CERTIFICATE, ALTER ANY CONTRACT, ALTER ANY DATABASE DDL TRIGGER, ALTER ANY DATABASE EVENT, NOTIFICATION, ALTER ANY DATASPACE, ALTER ANY FULLTEXT CATALOG, ALTER ANY MESSAGE TYPE, ALTER ANY REMOTE SERVICE BINDING, ALTER ANY ROUTE, ALTER ANY SCHEMA, ALTER ANY SERVICE, ALTER ANY SYMMETRIC KEY, CHECKPOINT, CREATE AGGREGATE, CREATE DEFAULT, CREATE FUNCTION, CREATE PROCEDURE, CREATE QUEUE, CREATE RULE, CREATE SYNONYM, CREATE TABLE, CREATE TYPE, CREATE VIEW, CREATE XML SCHEMA COLLECTION, REFERENCES
Granted: VIEW ANY DATABASE

db_denydatareader
Denied: SELECT
Granted: VIEW ANY DATABASE

db_denydatawriter
Denied: DELETE, INSERT, UPDATE

db_owner
Granted with GRANT option: CONTROL
Granted: VIEW ANY DATABASE

db_securityadmin
Granted: ALTER ANY APPLICATION ROLE, ALTER ANY ROLE, CREATE SCHEMA, VIEW DEFINITION
Granted: VIEW ANY DATABASE

Bonus:
1. how to check the user’s server permission
select a.* from sys.server_permissions a,sys.server_principals b
where a.grantee_principal_id=b.principal_id
and b.name=’%username%’;

Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: