SQL Server 2005 makes extensive uses of security principals and securables. An entity that can request a server, database, or schema resource is referred to as a security principal. Each security principal has a unique Security Identifier (SID). Security principals are managed at three levels: Windows, SQL Server, and Database. The level at which the security principal is defined sets its scope of influence. Generally, Windows- and SQL Server–level security principals have an instance-wide scope, and database-level principals have a scope of influence within a specific database.

Table 8-1 lists the security principals at each level. Some security principals, including Windows groups, database roles, and applications roles, can include other security principals. These security principals are also referred to as collections. Every database user belongs to the public database role. When a user has not been granted or denied specific permissions on a securable, the user inherits the permissions granted to the public role on that securable.

Security principals can be assigned specific permissions on hierarchical collections of entities referred to as securables. As Table 8-2 shows, the three top-level securables are server, database, and schema. Each of these securables contains other securables, which in turn can contain other securables. These nested hierarchies are referred to as scopes. Thus, you can also say that the main securable scopes in SQL Server are server, database, and schema.

Each SQL Server 2005 securable has permissions that can be granted to a security principal. These permissions begin with a keyword or keywords that identify the permission being granted, and these are summarized in Table 8-3.

SQL Server functions that you will find helpful for examining permissions granted to securables include:

You will learn more about how these functions are used in the sections that follow.

Each object class from the server scope down has a specific set of grantable permissions. The sys.fn_builtin_permissions function returns a description of the server’s built-in permissions hierarchy:

sys.fn_built_permissions( [ DEFAULT | NULL     
    | empty_string | < securable_class > } )

< securable_class >::= APPLICATION ROLE | ASSEMBLY | ASYMMETRIC KEY    
    | CERTIFICATE | CONTRACT | DATABASE | ENDPOINT | FULLTEXT CATALOG    
    | LOGIN | MESSAGE TYPE | OBJECT | REMOTE SERVICE BINDING | ROLE    
    | ROUTE | SCHEMA | SERVER | SERVICE | SYMMETRIC KEY | TYPE     
    | USER | XML SCHEMA COLLECTION

where DEFAULT, NULL, or an empty string return a complete list of built-in permissions, or you can specify the name of a specific securable class to return all permissions that apply to the class.

The sys.fn_builtin_permissions function is accessible to the public role. You can view the grantable permissions for all objects by using the following query:

USE master
GO
SELECT * FROM sys.fn_builtin_permissions(default)
GO

If you want to view the grantable permissions for a specific object class, you can use the following query:

USE master
GO
SELECT * FROM sys.fn_builtin_permissions('object_class')  
GO

where object_class is the object class you want to work with. The following example examines the grantable permissions for the LOGIN class:

SELECT * FROM sys.fn_builtin_permissions('login')

You can also list object classes for which a specific permission has been granted. In the following example, you list object classes that have the SELECT permission:

USE master
GO
SELECT * FROM sys.fn_builtin_permissions(DEFAULT) 
       WHERE permission_name = 'SELECT';
GO

The Has_perms_by_name built-in function returns the effective permission on a securable. Effective permissions include:

The Has_perms_by_name function is accessible to the public role. However, you cannot use Has_perms_by_name to check permissions on a linked server. The basic syntax of the Has_perms_by_name function follows:

Has_perms_by_name (
                   securable ,
                   securable_class ,
                   permission 
                   [, sub-securable ]
                   [, sub-securable_class ]
                   )

where securable sets the name of the securable or NULL if the securable is the server itself, securable_class sets the name of the securable class or NULL if the securable is the server itself, and permission is a non-NULL value representing the permission name to be checked. You can use the permission name "ANY" as a wildcard to determine if the securable has any effective permissions. The optional sub-securable and sub-securable_class values specify the name of the securable subentity and the class of securable subentity against which the permission is tested. Both of these optional values default to NULL. If the function returns true (1), the securable has the effective permission. If the function returns false (0), the securable does not have the effective permission. A return value of NULL indicates that the query failed.

You can determine if the currently logged-on user has a specific permission on the server by executing the following query:

USE master
GO
SELECT has_perms_by_name(null, null, 'permission_name'),
GO

where permission_name is the name of the permission to examine. The following example checks to see if the current user has the VIEW SERVER STATE permissions:

select has_perms_by_name(null, null, 'VIEW SERVER STATE'),

A true (1) or false (0) value is returned to indicate whether or not the user is granted the permission.

To determine if the current user has any permissions in a specific database, you can execute the following query:

USE master
GO
SELECT has_perms_by_name('database_name', 'DATABASE', 'ANY')
GO

where database_name is the name of the database for which you are determining permissions. The following example determines if the current user has any permissions in the Personnel database:

SELECT has_perms_by_name('Personnel', 'DATABASE', 'ANY')

If the query returns 1, the current user has some permissions for the specific database. You can indicate the current database with the db_name() function, such as:

SELECT has_perms_by_name(db_name(),'DATABASE', 'ANY')

You can determine the permissions of a specific user using EXECUTE AS. In the following example, you check to see if EdwardM has any permissions in the Personnel database:

EXECUTE AS user = 'EdwardM'
GO
SELECT has_perms_by_name('Personnel', 'DATABASE', 'ANY')
GO
REVERT
GO

Permissions on schema objects, like Tables and Views, can be examined as well. To do this, set the securable to name, the securable class to object, and permission to the permission you want to examine. To determine which tables the current user has SELECT permission on, you would use the following query:

USE Personnel
GO
SELECT has_perms_by_name(name, 'OBJECT', 'SELECT') as Have_Select,
    * from sys.tables;
go

The current user has SELECT permission on tables with a 1 in the Have_Select column. By specifying the two-part or three-part name, you can examine permissions on a specific table as well. For example, to determine if the current user has INSERT permission on the Address table in the current database, you would use a two-part name:

select has_perms_by_name('Employee.Address', 'OBJECT', 'INSERT')
       as Have_Select, * from sys.tables;

or a three-part name:

select has_perms_by_name('Personnel.Employee.Address', 'OBJECT', 'INSERT')
       as Have_Select, * from sys.tables;