MSSQL 2005 security components
- SQL Server 2005 security is divided in 3 categories:
- Principals: entities that request the security authorization.
- Securables: resources that have the permissions granted or denied.
- Permissions: Granting, revoking or denying the securables to the principals.
Principals
Entities that can request permissions from the SQL Server resources:
- They are either users, groups or processes.
- Each principal has its own unique identifier.
- The principal set either at the Windows level through users and group or at the server level through login and roles.
Securables
Entities to which permissions can be granted, revokes or denied. They can be at:
- Server level:
- Login
- Databases
- Endpoints
- Database level:
- User
- Role
- Service
- Catalog
- Certificate
- Schemas...
- Schema level:
- Table
- View
- Function
- Procedure...
Available permissions
You can view the current permissions by looking at sys.fn_builtin_permissions.
- SELECT SUBSTRING(class_desc, 1, 30) AS 'Class',
- permission_name,
- covering_permission_name,
- parent_class_desc,
- parent_covering_permission_name
- FROM sys.fn_builtin_permissions(DEFAULT)
- ORDER BY class_desc, permission_name;
Class permission_name covering_permission_name parent_class_desc parent_covering_permission_name ------------------------------ ------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------ APPLICATION ROLE ALTER CONTROL DATABASE ALTER ANY APPLICATION ROLE APPLICATION ROLE CONTROL DATABASE CONTROL APPLICATION ROLE VIEW DEFINITION CONTROL DATABASE VIEW DEFINITION ASSEMBLY ALTER CONTROL DATABASE ALTER ANY ASSEMBLY ASSEMBLY CONTROL DATABASE CONTROL ASSEMBLY REFERENCES CONTROL DATABASE REFERENCES ASSEMBLY TAKE OWNERSHIP CONTROL DATABASE CONTROL ASSEMBLY VIEW DEFINITION CONTROL DATABASE VIEW DEFINITION ASYMMETRIC KEY ALTER CONTROL DATABASE ALTER ANY ASYMMETRIC KEY ASYMMETRIC KEY CONTROL DATABASE CONTROL ASYMMETRIC KEY REFERENCES CONTROL DATABASE REFERENCES ASYMMETRIC KEY TAKE OWNERSHIP CONTROL DATABASE CONTROL ASYMMETRIC KEY VIEW DEFINITION CONTROL DATABASE VIEW DEFINITION CERTIFICATE ALTER CONTROL DATABASE ALTER ANY CERTIFICATE CERTIFICATE CONTROL DATABASE CONTROL CERTIFICATE REFERENCES CONTROL DATABASE REFERENCES CERTIFICATE TAKE OWNERSHIP CONTROL DATABASE CONTROL CERTIFICATE VIEW DEFINITION CONTROL DATABASE VIEW DEFINITION CONTRACT ALTER CONTROL DATABASE ALTER ANY CONTRACT CONTRACT CONTROL DATABASE CONTROL CONTRACT REFERENCES CONTROL DATABASE REFERENCES CONTRACT TAKE OWNERSHIP CONTROL DATABASE CONTROL CONTRACT VIEW DEFINITION CONTROL DATABASE VIEW DEFINITION DATABASE ALTER CONTROL SERVER ALTER ANY DATABASE DATABASE ALTER ANY APPLICATION ROLE ALTER SERVER CONTROL SERVER DATABASE ALTER ANY ASSEMBLY ALTER SERVER CONTROL SERVER DATABASE ALTER ANY ASYMMETRIC KEY ALTER SERVER CONTROL SERVER DATABASE ALTER ANY CERTIFICATE ALTER SERVER CONTROL SERVER DATABASE ALTER ANY CONTRACT ALTER SERVER CONTROL SERVER DATABASE ALTER ANY DATABASE DDL TRIGGER ALTER SERVER CONTROL SERVER DATABASE ALTER ANY DATABASE EVENT NOTIFICATION ALTER SERVER ALTER ANY EVENT NOTIFICATION DATABASE ALTER ANY DATASPACE ALTER SERVER CONTROL SERVER DATABASE ALTER ANY FULLTEXT CATALOG ALTER SERVER CONTROL SERVER DATABASE ALTER ANY MESSAGE TYPE ALTER SERVER CONTROL SERVER DATABASE ALTER ANY REMOTE SERVICE BINDING ALTER SERVER CONTROL SERVER DATABASE ALTER ANY ROLE ALTER SERVER CONTROL SERVER DATABASE ALTER ANY ROUTE ALTER SERVER CONTROL SERVER DATABASE ALTER ANY SCHEMA ALTER SERVER CONTROL SERVER DATABASE ALTER ANY SERVICE ALTER SERVER CONTROL SERVER DATABASE ALTER ANY SYMMETRIC KEY ALTER SERVER CONTROL SERVER DATABASE ALTER ANY USER ALTER SERVER CONTROL SERVER DATABASE AUTHENTICATE CONTROL SERVER AUTHENTICATE SERVER DATABASE BACKUP DATABASE CONTROL SERVER CONTROL SERVER DATABASE BACKUP LOG CONTROL SERVER CONTROL SERVER DATABASE CHECKPOINT CONTROL SERVER CONTROL SERVER DATABASE CONNECT CONNECT REPLICATION SERVER CONTROL SERVER DATABASE CONNECT REPLICATION CONTROL SERVER CONTROL SERVER DATABASE CONTROL SERVER CONTROL SERVER DATABASE CREATE AGGREGATE ALTER SERVER CONTROL SERVER DATABASE CREATE ASSEMBLY ALTER ANY ASSEMBLY SERVER CONTROL SERVER DATABASE CREATE ASYMMETRIC KEY ALTER ANY ASYMMETRIC KEY SERVER CONTROL SERVER DATABASE CREATE CERTIFICATE ALTER ANY CERTIFICATE SERVER CONTROL SERVER DATABASE CREATE CONTRACT ALTER ANY CONTRACT SERVER CONTROL SERVER DATABASE CREATE DATABASE SERVER CREATE ANY DATABASE DATABASE CREATE DATABASE DDL EVENT NOTIFICATION ALTER ANY DATABASE EVENT NOTIFICATION SERVER CREATE DDL EVENT NOTIFICATION DATABASE CREATE DEFAULT ALTER SERVER CONTROL SERVER DATABASE CREATE FULLTEXT CATALOG ALTER ANY FULLTEXT CATALOG SERVER CONTROL SERVER DATABASE CREATE FUNCTION ALTER SERVER CONTROL SERVER DATABASE CREATE MESSAGE TYPE ALTER ANY MESSAGE TYPE SERVER CONTROL SERVER DATABASE CREATE PROCEDURE ALTER SERVER CONTROL SERVER DATABASE CREATE QUEUE ALTER SERVER CONTROL SERVER DATABASE CREATE REMOTE SERVICE BINDING ALTER ANY REMOTE SERVICE BINDING SERVER CONTROL SERVER DATABASE CREATE ROLE ALTER ANY ROLE SERVER CONTROL SERVER DATABASE CREATE ROUTE ALTER ANY ROUTE SERVER CONTROL SERVER DATABASE CREATE RULE ALTER SERVER CONTROL SERVER DATABASE CREATE SCHEMA ALTER ANY SCHEMA SERVER CONTROL SERVER DATABASE CREATE SERVICE ALTER ANY SERVICE SERVER CONTROL SERVER DATABASE CREATE SYMMETRIC KEY ALTER ANY SYMMETRIC KEY SERVER CONTROL SERVER DATABASE CREATE SYNONYM ALTER SERVER CONTROL SERVER DATABASE CREATE TABLE ALTER SERVER CONTROL SERVER DATABASE CREATE TYPE ALTER SERVER CONTROL SERVER DATABASE CREATE VIEW ALTER SERVER CONTROL SERVER DATABASE CREATE XML SCHEMA COLLECTION ALTER SERVER CONTROL SERVER DATABASE DELETE CONTROL SERVER CONTROL SERVER DATABASE EXECUTE CONTROL SERVER CONTROL SERVER DATABASE INSERT CONTROL SERVER CONTROL SERVER DATABASE REFERENCES CONTROL SERVER CONTROL SERVER DATABASE SELECT CONTROL SERVER CONTROL SERVER DATABASE SHOWPLAN CONTROL SERVER ALTER TRACE DATABASE SUBSCRIBE QUERY NOTIFICATIONS CONTROL SERVER CONTROL SERVER DATABASE TAKE OWNERSHIP CONTROL SERVER CONTROL SERVER DATABASE UPDATE CONTROL SERVER CONTROL SERVER DATABASE VIEW DATABASE STATE CONTROL SERVER VIEW SERVER STATE DATABASE VIEW DEFINITION CONTROL SERVER VIEW ANY DEFINITION ENDPOINT ALTER CONTROL SERVER ALTER ANY ENDPOINT ENDPOINT CONNECT CONTROL SERVER CONTROL SERVER ENDPOINT CONTROL SERVER CONTROL SERVER ENDPOINT TAKE OWNERSHIP CONTROL SERVER CONTROL SERVER ENDPOINT VIEW DEFINITION CONTROL SERVER VIEW ANY DEFINITION FULLTEXT CATALOG ALTER CONTROL DATABASE ALTER ANY FULLTEXT CATALOG FULLTEXT CATALOG CONTROL DATABASE CONTROL FULLTEXT CATALOG REFERENCES CONTROL DATABASE REFERENCES FULLTEXT CATALOG TAKE OWNERSHIP CONTROL DATABASE CONTROL FULLTEXT CATALOG VIEW DEFINITION CONTROL DATABASE VIEW DEFINITION LOGIN ALTER CONTROL SERVER ALTER ANY LOGIN LOGIN CONTROL SERVER CONTROL SERVER LOGIN IMPERSONATE CONTROL SERVER CONTROL SERVER LOGIN VIEW DEFINITION CONTROL SERVER VIEW ANY DEFINITION MESSAGE TYPE ALTER CONTROL DATABASE ALTER ANY MESSAGE TYPE MESSAGE TYPE CONTROL DATABASE CONTROL MESSAGE TYPE REFERENCES CONTROL DATABASE REFERENCES MESSAGE TYPE TAKE OWNERSHIP CONTROL DATABASE CONTROL MESSAGE TYPE VIEW DEFINITION CONTROL DATABASE VIEW DEFINITION OBJECT ALTER CONTROL SCHEMA ALTER OBJECT CONTROL SCHEMA CONTROL OBJECT DELETE CONTROL SCHEMA DELETE OBJECT EXECUTE CONTROL SCHEMA EXECUTE OBJECT INSERT CONTROL SCHEMA INSERT OBJECT RECEIVE CONTROL SCHEMA CONTROL OBJECT REFERENCES CONTROL SCHEMA REFERENCES OBJECT SELECT RECEIVE SCHEMA SELECT OBJECT TAKE OWNERSHIP CONTROL SCHEMA CONTROL OBJECT UPDATE CONTROL SCHEMA UPDATE OBJECT VIEW DEFINITION CONTROL SCHEMA VIEW DEFINITION REMOTE SERVICE BINDING ALTER CONTROL DATABASE ALTER ANY REMOTE SERVICE BINDING REMOTE SERVICE BINDING CONTROL DATABASE CONTROL REMOTE SERVICE BINDING TAKE OWNERSHIP CONTROL DATABASE CONTROL REMOTE SERVICE BINDING VIEW DEFINITION CONTROL DATABASE VIEW DEFINITION ROLE ALTER CONTROL DATABASE ALTER ANY ROLE ROLE CONTROL DATABASE CONTROL ROLE TAKE OWNERSHIP CONTROL DATABASE CONTROL ROLE VIEW DEFINITION CONTROL DATABASE VIEW DEFINITION ROUTE ALTER CONTROL DATABASE ALTER ANY ROUTE ROUTE CONTROL DATABASE CONTROL ROUTE TAKE OWNERSHIP CONTROL DATABASE CONTROL ROUTE VIEW DEFINITION CONTROL DATABASE VIEW DEFINITION SCHEMA ALTER CONTROL DATABASE ALTER ANY SCHEMA SCHEMA CONTROL DATABASE CONTROL SCHEMA DELETE CONTROL DATABASE DELETE SCHEMA EXECUTE CONTROL DATABASE EXECUTE SCHEMA INSERT CONTROL DATABASE INSERT SCHEMA REFERENCES CONTROL DATABASE REFERENCES SCHEMA SELECT CONTROL DATABASE SELECT SCHEMA TAKE OWNERSHIP CONTROL DATABASE CONTROL SCHEMA UPDATE CONTROL DATABASE UPDATE SCHEMA VIEW DEFINITION CONTROL DATABASE VIEW DEFINITION SERVER ADMINISTER BULK OPERATIONS CONTROL SERVER SERVER ALTER ANY CONNECTION CONTROL SERVER SERVER ALTER ANY CREDENTIAL CONTROL SERVER SERVER ALTER ANY DATABASE CONTROL SERVER SERVER ALTER ANY ENDPOINT CONTROL SERVER SERVER ALTER ANY EVENT NOTIFICATION CONTROL SERVER SERVER ALTER ANY LINKED SERVER CONTROL SERVER SERVER ALTER ANY LOGIN CONTROL SERVER SERVER ALTER RESOURCES CONTROL SERVER SERVER ALTER SERVER STATE CONTROL SERVER SERVER ALTER SETTINGS CONTROL SERVER SERVER ALTER TRACE CONTROL SERVER SERVER AUTHENTICATE SERVER CONTROL SERVER SERVER CONNECT SQL CONTROL SERVER SERVER CONTROL SERVER SERVER CREATE ANY DATABASE ALTER ANY DATABASE SERVER CREATE DDL EVENT NOTIFICATION ALTER ANY EVENT NOTIFICATION SERVER CREATE ENDPOINT ALTER ANY ENDPOINT SERVER CREATE TRACE EVENT NOTIFICATION ALTER ANY EVENT NOTIFICATION SERVER EXTERNAL ACCESS ASSEMBLY UNSAFE ASSEMBLY SERVER SHUTDOWN CONTROL SERVER SERVER UNSAFE ASSEMBLY CONTROL SERVER SERVER VIEW ANY DATABASE VIEW ANY DEFINITION SERVER VIEW ANY DEFINITION CONTROL SERVER SERVER VIEW SERVER STATE ALTER SERVER STATE SERVICE ALTER CONTROL DATABASE ALTER ANY SERVICE SERVICE CONTROL DATABASE CONTROL SERVICE SEND CONTROL DATABASE CONTROL SERVICE TAKE OWNERSHIP CONTROL DATABASE CONTROL SERVICE VIEW DEFINITION CONTROL DATABASE VIEW DEFINITION SYMMETRIC KEY ALTER CONTROL DATABASE ALTER ANY SYMMETRIC KEY SYMMETRIC KEY CONTROL DATABASE CONTROL SYMMETRIC KEY REFERENCES CONTROL DATABASE REFERENCES SYMMETRIC KEY TAKE OWNERSHIP CONTROL DATABASE CONTROL SYMMETRIC KEY VIEW DEFINITION CONTROL DATABASE VIEW DEFINITION TYPE CONTROL SCHEMA CONTROL TYPE EXECUTE CONTROL SCHEMA EXECUTE TYPE REFERENCES CONTROL SCHEMA REFERENCES TYPE TAKE OWNERSHIP CONTROL SCHEMA CONTROL TYPE VIEW DEFINITION CONTROL SCHEMA VIEW DEFINITION USER ALTER CONTROL DATABASE ALTER ANY USER USER CONTROL DATABASE CONTROL USER IMPERSONATE CONTROL DATABASE CONTROL USER VIEW DEFINITION CONTROL DATABASE VIEW DEFINITION XML SCHEMA COLLECTION ALTER CONTROL SCHEMA ALTER XML SCHEMA COLLECTION CONTROL SCHEMA CONTROL XML SCHEMA COLLECTION EXECUTE CONTROL SCHEMA EXECUTE XML SCHEMA COLLECTION REFERENCES CONTROL SCHEMA REFERENCES XML SCHEMA COLLECTION TAKE OWNERSHIP CONTROL SCHEMA CONTROL XML SCHEMA COLLECTION VIEW DEFINITION CONTROL SCHEMA VIEW DEFINITION (186 row(s) affected)