Events
Mar 31, 11 PM - Apr 2, 11 PM
The biggest SQL, Fabric and Power BI learning event. March 31 – April 2. Use code FABINSIDER to save $400.
Register todayThis browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
Applies to:
SQL Server
Azure SQL Database
Azure SQL Managed Instance
Evaluates the effective permission of the current user on a securable. A related function is fn_my_permissions.
Transact-SQL syntax conventions
HAS_PERMS_BY_NAME ( securable , securable_class , permission
[ , sub-securable ] [ , sub-securable_class ] )
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).
In Azure SQL Database, the securable_class argument must be set to one of the following: DATABASE, OBJECT, ROLE, SCHEMA, or USER.
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.
Note
Sub-securables cannot use brackets in the form '[sub name]'. Use 'sub name' instead.
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.
In Azure SQL Database, the sub-securable_class argument is valid only if the securable_class argument is set to OBJECT. If the securable_class argument is set to OBJECT, the sub-securable_class argument must be set to COLUMN.
int
Returns NULL when the query fails.
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 NULL when 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.
Applies to: SQL Server 2008 (10.0.x) and later
SELECT HAS_PERMS_BY_NAME(null, null, 'VIEW SERVER STATE');
Applies to: SQL Server 2008 (10.0.x) and later
SELECT HAS_PERMS_BY_NAME('Ps', 'LOGIN', 'IMPERSONATE');
SELECT HAS_PERMS_BY_NAME(db_name(), 'DATABASE', 'ANY');
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
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;
SELECT HAS_PERMS_BY_NAME
(QUOTENAME(SCHEMA_NAME(schema_id)) + '.' + QUOTENAME(name),
'OBJECT', 'SELECT') AS have_select, * FROM sys.tables
The following example assumes AdventureWorks2022
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('AdventureWorks2022.Sales.SalesPerson',
'OBJECT', 'INSERT');
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');
Permissions (Database Engine)
Securables
Permissions Hierarchy (Database Engine)
sys.fn_builtin_permissions (Transact-SQL)
Security Catalog Views (Transact-SQL)
Events
Mar 31, 11 PM - Apr 2, 11 PM
The biggest SQL, Fabric and Power BI learning event. March 31 – April 2. Use code FABINSIDER to save $400.
Register today