Has_Perms_By_Name (Transact-SQL)

Article
12/03/2008

Evaluates the effective permission of the current user on a securable.

Topic link icon Transact-SQL Syntax Conventions

Syntax

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

Arguments

securable
Is the name of the securable. If the securable is the server itself, this value should be set to NULL. securable is a scalar expression of type sysname. There is no default.

securable_class
Is the name of the class of securable against which the permission is tested. securable_class is a scalar expression of type nvarchar(60).

permission
A nonnull scalar expression of type sysname that represents the permission name to be checked. There is no default. The permission name ANY is a wildcard.

sub-securable
An optional scalar expression of type sysname that represents the name of the securable sub-entity against which the permission is tested. The default is NULL.

sub-securable_class
An optional scalar expression of type nvarchar(60) that represent the class of securable subentity against which the permission is tested. The default is NULL.

Return Types

int

Returns NULL if the query fails.

Remarks

This built-in function tests whether the current principal has a particular effective permission on a specified securable. An effective permission is any of the followoing:

A permission granted directly to the principal, and not denied.
A permission implied by a higher-level permission held by the principal and not denied.
A permission granted to a role or group of which the principal is a member, and not denied.
A permission held by a role or group of which the principal is a member, and not denied.

The permission evaluation is always performed in the security context of the caller. To determine whether some other user has an effective permission, the caller must have IMPERSONATE permission on that user.

For schema-level entities, one-, two-, or three-part nonnull names are accepted. For database-level entities a one-part name is accepted, with a null value meaning "current database". For the server itself, a null value (meaning "current server") is required. This function cannot check permissions on a linked server or on a Windows user for which no server-level principal has been created.

The following query will return a list of built-in securable classes:

   SELECT class_desc FROM sys.fn_builtin_permissions(default)

The following collations are used:

Current database collation: Database-level securables that include securables not contained by a schema; one- or two-part schema-scoped securables; target database when using a three-part name.
master database collation: Server-level securables.
'ANY' is not supported for column-level checks. You must specify the appropriate permission.

Examples

A. Do I have the server-level VIEW SERVER STATE permission?

SELECT has_perms_by_name(null, null, 'VIEW SERVER STATE');

B. Am I able to IMPERSONATE server principal Ps?

SELECT has_perms_by_name('Ps', 'LOGIN', 'IMPERSONATE')

C. Do I have any permissions in the current database?

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

D. Does database principal Pd have any permission in the current database?

Assume caller has IMPERSONATE permission on principal Pd.

EXECUTE AS user = 'Pd'
GO
SELECT has_perms_by_name(db_name(), 'DATABASE', 'ANY')
GO
REVERT
GO

E. Can I create procedures and tables in schema S?

The following example requires ALTER permission in S and CREATE PROCEDURE permission in the database, and similarly for tables.

SELECT has_perms_by_name(db_name(), 'DATABASE', 'CREATE PROCEDURE')
    & has_perms_by_name('S', 'SCHEMA', 'ALTER') AS _can_create_procs,
    has_perms_by_name(db_name(), 'DATABASE', 'CREATE TABLE') &
    has_perms_by_name('S', 'SCHEMA', 'ALTER') AS _can_create_tables;

F. Which tables do I have SELECT permission on?

SELECT has_perms_by_name(SCHEMA_NAME(schema_id) + '.' + name, 
    'OBJECT', 'SELECT') AS have_select, * FROM sys.tables;

G. Do I have INSERT permission on the SalesPerson table in AdventureWorks?

The following example assumes AdventureWorks is my current database context, and uses a two-part name.

SELECT has_perms_by_name('Sales.SalesPerson', 'OBJECT', 'INSERT')

The following example makes no assumptions about my current database context, and uses a three-part name.

SELECT has_perms_by_name('AdventureWorks.Sales.SalesPerson', 
    'OBJECT', 'INSERT')

H. Which columns of table T do I have SELECT permission on?

SELECT name AS column_name, 
    has_perms_by_name('T', 'OBJECT', 'SELECT', name, 'COLUMN') 
    AS can_select FROM sys.columns AS c 
    WHERE c.object_id=object_id('T');

Share via