Understanding SQL Server Security Function HAS_Permis_BY_Name and Its USE Cases

There are multiple instances when we want to check the permission on a securable for a principal. Before going ahead, let’s see what principal, securables, and permissions are.

According to Microsoft Documentation,

1. Securables in SQL Server context are specific resources to which the SQL Server Database Engine authorization system controls access. They are divided into three categories: Server, Database and Schema. In general, any SQL Server or database objects can be securables.
2. Permissions are controls using which we assign grant or deny certain level of access to a securable.
3. Principal is an entity that receives permission to a securable. The most common principals are logins and database users.

SQL Server has a built-in security function HAS_Permis_BY_Name that will help us get whether an identified principal has specific permission on a given securable or not. This system function returns 1 if effective permission is assigned to that principal on a given securable, and it returns 0 if effective permission is not assigned. You will get the NULL value if the effective permission or securable class is not valid.

The HAS_Permis_BY_Name system function is also very useful in checking permission for your login. I will show you a step-by-step process to check specific permission on a securable for my principal and other principals in this article.

The T-SQL syntax of this system function is as follows:

--T-SQL syntax
HAS_PERMS_BY_NAME (securable, securable_class, permission)
   

Three mandatory parameters will be needed to execute this system SQL Server security function.

• Enter the name of the securable about which you want to check the permission. If a securable is a server itself, you should use NULL.
• Second parameter is securable_class which is the name of the class. If you are not sure it, you can use another system function sys.fn_builtin_permissions to get the full list of securable class and their available permissions.
• Third parameter is the permission where you need to enter the effective permission about which you want to check on the specified securable.

Now, let me show you all available securable classes by running the system function sys.fn_builtin_permissions. I have used DISTINCT to display rows per securable class.

--Display all securable_class
SELECT distinct class_desc FROM sys.fn_builtin_permissions(default)

Here, you can get a list of the securable class.

If you want to check all possible permissions for any securable class, you can also use this system function. Run the below T-SQL statement:

--Display each permission for all securable class
SELECT class_desc,permission_name FROM sys.fn_builtin_permissions(default);

We can see the list in the below image. The class_desc is a securable class and permission_name is a type of permission. If you are not sure about the securable class and permissions to be checked for any securable, you can use this system function.

HAS_Permis_BY_Name USE Cases

I will show you 5 use cases of checking various permissions for my own login to SQL Server instance and for an additional login named manvendra.

1. Check Server or Instance level Permissions
2. Check Database level Permissions
3. Check Object Level Permissions
4. Check Logins Permissions
5. Check Other Permissions like Full Text Catalog, Schema etc.

Let’s start with the first use case to check instance-level permissions.

USE CASE 1: Check SQL Server or Instance-Level Permission

This use case will show how to check various permissions for a server principal\login. You can run the above T-SQL statement using the system function sys.fn_builtin_permissions. You can use the WHERE clause in this function to list only server-level permissions. Here, I will show you permissions for my own connected login.

• View Server State
• Create Server Role
• Connect Any Database

If you are looking for any permission at the server level, you should always pass NULL as a securable argument. In this example, NULL will be securable as its server level and the above permission names will have permission argument. Run the below T-SQL statement to check server-level permissions.

--Display server level permission for your own login using which you have established the database connection
SELECT HAS_PERMS_BY_NAME(null, null, 'VIEW SERVER STATE') AS [VIEW SERVER STATE],
	HAS_PERMS_BY_NAME(null, null, 'CREATE SERVER ROLE') AS [CREATE SERVER ROLE],
	HAS_PERMS_BY_NAME(null, null, 'CONNECT ANY DATABASE') AS [CONNECT ANY DATABASE]

The output is shown in the below image. T-SQL has returned 1 for all permissions for my login. It means I have permissions for all three operations. I can view server state, I can create server role and I can also connect to any database on this server or instance.

Let me show you whether a login named ‘manvendra’ has permissions for the above 3 operations. We will use the EXECUTE AS LOGIN T-SQL statement to check the access level. Make sure you have IMPERSONATE permission on that login for which you are checking the permissions. Run the same T-SQL statement as above after EXECUTE AS LOGIN statement.

--Display server level permission for another login ‘manvendra’
EXECUTE AS LOGIN = ‘manvendra’
GO
SELECT HAS_PERMS_BY_NAME(null, null, 'VIEW SERVER STATE') AS [VIEW SERVER STATE],
	HAS_PERMS_BY_NAME(null, null, 'CREATE SERVER ROLE') AS [CREATE SERVER ROLE],
	HAS_PERMS_BY_NAME(null, null, 'CONNECT ANY DATABASE') AS [CONNECT ANY DATABASE]

Here, we can see the login ‘manvendra’ has no access to any of these 3 activities at the server level as their output has returned 0.

USE CASE 2: Check Database-Level Permissions

I have explained how to check various permissions for any principal at the server or instance level in the above section. Now, I will show you how to check various permissions for any principal at the database level. See the below statement:

--Display each permission for securable class ‘DATABASE’
SELECT class_desc,permission_name FROM sys.fn_builtin_permissions(default)
WHERE class_desc = ‘DATABASE’

You can see there are 82 permissions available at the database level in the below screenshot.

I have chosen the below permissions to check whether my login or an additional login ‘manvendra’ has permission to perform these activities.

• ANY
• CREATE Table
• CREATE PROCEDURE
• INSERT
• SELECT

Here, securable will be the database name on which you want to check the permissions, the securable class will be ‘Database’ and permission will be the above permission names. Run the below T-SQL statement to check various permissions.

--Display few specific permissions for your own connected login on a DATABASE
SELECT HAS_PERMS_BY_NAME(db_name(), 'DATABASE', 'ANY') AS [DB Access],
HAS_PERMS_BY_NAME(db_name(), 'DATABASE', 'CREATE TABLE') AS [CREATE TABLE],
HAS_PERMS_BY_NAME(db_name(), 'DATABASE', 'CREATE PROCEDURE') AS [CREATE PROCEDURE],
HAS_PERMS_BY_NAME(db_name(), 'DATABASE', 'INSERT') AS [INSERT Access],
HAS_PERMS_BY_NAME(db_name(), 'DATABASE', 'SELECT') AS [SELECT Access]

The output returned 1 for each permission. It means I have permission to perform all the above activities in the current database context.

Run the below T-SQL statement to check the same permissions for login ‘manvendra’ in the currently selected database context.

--Display few specific permissions for login ‘manvendra’ on current database
EXECUTE AS LOGIN ='manvendra'
GO
SELECT HAS_PERMS_BY_NAME(db_name(), 'DATABASE', 'ANY') AS [DB Access],
HAS_PERMS_BY_NAME(db_name(), 'DATABASE', 'CREATE TABLE') AS [CREATE TABLE],
HAS_PERMS_BY_NAME(db_name(), 'DATABASE', 'CREATE PROCEDURE') AS [CREATE PROCEDURE],
HAS_PERMS_BY_NAME(db_name(), 'DATABASE', 'INSERT') AS [INSERT Access],
HAS_PERMS_BY_NAME(db_name(), 'DATABASE', 'SELECT') AS [SELECT Access]

The output shows that login ‘manvendra’ has very limited permissions on this database. This login can only connect to the database and the rest of the permissions are not allowed.

Here, I have changed the database context and chosen database ‘AdventureWorksDW2019-TSQL’ as a securable argument and executed the below statement for login ‘manvendra’.

--Display few specific permissions for login ‘manvendra’ on database ‘AdventureWorksDW2019-TSQL’
EXECUTE AS LOGIN ='manvendra'
GO
SELECT HAS_PERMS_BY_NAME('AdventureWorksDW2019-TSQL', 'DATABASE', 'ANY') AS [DB Access],
HAS_PERMS_BY_NAME('AdventureWorksDW2019-TSQL', 'DATABASE', 'CREATE TABLE') AS [CREATE TABLE],
HAS_PERMS_BY_NAME('AdventureWorksDW2019-TSQL', 'DATABASE', 'CREATE PROCEDURE') AS [CREATE PROCEDURE],
HAS_PERMS_BY_NAME('AdventureWorksDW2019-TSQL', 'DATABASE', 'INSERT') AS [INSERT Access],
HAS_PERMS_BY_NAME('AdventureWorksDW2019-TSQL', 'DATABASE', 'SELECT') AS [SELECT Access]

The same login ‘manvendra’ has permission to INSERT and SELECT operations on this database ‘AdventureWorks2019-TSQL’

Similarly, we can run the above T-SQL statement to check permission for distinct databases for our login. The output shows that I have access to all permissions.

You can go ahead and check other database-level permission for any principal by using the above system SQL Server security function.

USE CASE 3: Check OBJECT-LEVEL Permissions

This use case illustrates using object-level permissions within a database. Again, you can run the below T-SQL statement to list all available permissions for the securable class ‘OBJECT’. I have used the WHERE clause in the system function sys.fn_builtin_permissions to display the object level permission list.

--Check all object level permissions
SELECT class_desc,permission_name FROM sys.fn_builtin_permissions(default)
WHERE class_desc ='OBJECT'

Here is the list of permissions for the securable class Object.

Now, I am going to check the below permissions on a specific object for any login account and see whether that account has access to perform the below operations.

• INSERT
• SELECT
• DELETE
• VIEW DEFINITION

I have used a database object ‘dbo.dimAccount’ as securable, OBJECT as a securable class, and the above permissions as permission argument. Run the below statements to display the permission details.

--Check some specific object level permissions for your own login
SELECT HAS_PERMS_BY_NAME('[dbo].[DimAccount]', 'OBJECT', 'INSERT') AS [Insert Permission],
HAS_PERMS_BY_NAME('[dbo].[DimAccount]', 'OBJECT', 'SELECT') AS [Select Permission],
HAS_PERMS_BY_NAME('[dbo].[DimAccount]', 'OBJECT', 'DELETE') AS [DELETE Permission],
HAS_PERMS_BY_NAME('[dbo].[DimAccount]', 'OBJECT', 'VIEW DEFINITION') AS [VIEW DEFINITION Access]

As I am a sysadmin on this instance, my account is returning 1 for any permission I am checking at any level. It means I have full permissions, and this can be verified by running the below statements as well.

Run the below statement to check the permissions for the login ‘manvendra’.

--Check some specific object level permissions for another login ‘manvendra’
EXECUTE AS USER ='manvendra'
GO
USE [AdventureWorksDW2019-TSQL]
GO
SELECT HAS_PERMS_BY_NAME('[dbo].[DimAccount]', 'OBJECT', 'INSERT') AS [Insert Permission],
HAS_PERMS_BY_NAME('[dbo].[DimAccount]', 'OBJECT', 'SELECT') AS [Select Permission],
HAS_PERMS_BY_NAME('[dbo].[DimAccount]', 'OBJECT', 'DELETE') AS [DELETE Permission],
HAS_PERMS_BY_NAME('[dbo].[DimAccount]', 'OBJECT', 'VIEW DEFINITION') AS [VIEW DEFINITION Access]

We can see the login ‘manvendra’ has access to INSERT, SELECT and DELETE permissions but this account doesn’t have permission to VIEW DEFINITION of this object in the database ‘AdventureWorksDW2019-TSQL’.

Let me apply the DENY access to DELETE operation for this account ‘manvendra’ on object ‘DimAccount’ in database AdventureWorksDW2019-TSQL. You can see this in the below image.

We can see that the output indicates that login ‘manvendra’ has only access to INSERT and SELECT statements and does not have permission to the DELETE statement.

Check various levels of access for any login on any database object by using the above system function.

USE CASE 4: Check Login Permission

This use case will help you to understand how to check various permissions for logins. You can get all these kinds of details using this system SQL Server security function HAS_PERMS_BY_NAME.

We can list all permissions available for a specific login by running the below T-SQL statement.

--List all available permission for securable class ‘LOGIN’
SELECT class_desc, permission_name FROM sys.fn_builtin_permissions(default)
WHERE class_desc ='LOGIN'

Below is the list of permission names for the securable class ‘LOGIN’.

I will check whether I have ALTER or VIEW DEFINITION permission on login sa by running the below T-SQL statements. I have used login sa as a securable argument, LOGIN as a securable class argument, and ALTER & VIEW DEFINITION as a permission argument in this system function.

--Check ALTER & VIEW DEFINITION permission on securable sa
SELECT HAS_PERMS_BY_NAME('sa', 'LOGIN', 'ALTER'),
HAS_PERMS_BY_NAME('sa', 'LOGIN', 'VIEW DEFINITION')

As a sysadmin, I will have these access levels, and the output is also validated by returning their value as 1.

Let’s check whether the login ‘manvendra’ has permission to ALTER or VIEW definition of the login sa.

--Check ALTER & VIEW DEFINITION permission on securable sa for principal ‘manvendra’
EXECUTE AS USER ='manvendra'
GO

SELECT HAS_PERMS_BY_NAME('sa', 'LOGIN', 'ALTER'),
HAS_PERMS_BY_NAME('sa', 'LOGIN', 'VIEW DEFINITION')

The output returned as zero (0), which means login ‘manvendra’ does not have permission to ALTER or VIEW DEFINITION login sa.

Use this system function to check and understand access levels for various logins.

USE CASE 5: Check Other Permissions

Here, I will cover some other securable classes like SCHEMA and FULLTEXT CATALOG, ENDPOINT, AVAILABILITY GROUP, etc.

Let’s first list out all permissions available for SCHEMA and FULLTEXT CATALOG securable class by running the below statement:

--List all available permission for securable class ‘SCHEMA’ & ‘FTCatalog’. FTCatalog is full text catalog.
SELECT class_desc, permission_name FROM sys.fn_builtin_permissions(default)
WHERE class_desc='SCHEMA' OR
class_desc= 'FULLTEXT CATALOG'

The next step is to identify which permission we are looking for to check for our principal. I am going to check the DELETE and ALTER permissions for securable class SCHEMA and the ALTER and VIEW DEFINITION permission on securable class FULLTEXT CATALOG.

We need to pass their respective securables like I have passed dbo for the SCHEMA securable class and FTCatalog for securable class FULLTEXT CATALOG in the below statement.

Run the below T-SQL statement to get permission for your login.

--List below permissions for securable class ‘SCHEMA’ & ‘FTCatalog’. 
SELECT HAS_PERMS_BY_NAME('dbo', 'SCHEMA', 'DELETE') AS [Schema Deletion Access],
HAS_PERMS_BY_NAME('dbo', 'SCHEMA', 'ALTER') AS [Schema Alter Access],
HAS_PERMS_BY_NAME('[FTCatalog]', 'FULLTEXT CATALOG', 'ALTER') AS [FTC Alter Access],
HAS_PERMS_BY_NAME('[FTCatalog]', 'FULLTEXT CATALOG', 'VIEW DEFINITION') AS [VIEW DEFINITION]

The below output shows that login ‘manvendra’ has access only to SCHEMA deletion and the rest of accesses have been denied or revoked.

Conclusion

I have explained the step-by-step process to check various permissions for multiple securable classes for any principal in this article. You can also and use this system SQL Server security function to fulfill your permission checking requirements. Please share this article and give your feedback in the comment section so that we can improve.

Tags: database security, sql functions Last modified: October 27, 2022