Evaluates the effective permission of the current user on a securable. A related function is fn_my_permissions.
This built-in function tests whether the current principal has a particular effective permission on a specified securable. HAS_PERMS_BY_NAME returns 1 when the user has effective permission on the securable, 0 when the user has no effective permission on the securable, and when if the securable class or permission is not valid. An effective permission is any of the following:
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.
A. Do I have the server-level VIEW SERVER STATE permission?
B. Am I able to IMPERSONATE server principal Ps?
C. Do I have any permissions in the current database?
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 (QUOTENAME(SCHEMA_NAME(schema_id)) + '.' + QUOTENAME(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');