SQL Server Login and User Permissions with fn_my

By: Manvendra Singh | Comments (2) | Related: 1 | 2 | 3 | 4 | > Security

Problem

As a SQL Server DBA, we often get requests to check permissions on databases or database objects. Sometimes we need to check what permissions a user or login has on a specific object or check the permissions on a securable. In this article I will explain how to check permissions for a user or login or for your own login.

Solution

The permissions of a principal to a securable in a database consists of permissions explicitly granted to either the user or permissions derived from a group the user belongs to. I assume you are aware about the terms securable, principal and permissions because I am going to talk about these three terms throughout this article. These terms are not new, but some of the terms we use in our day to day DBA work like login, user, objects, etc.

Below is a list of security terms that you generally hear when you work with SQL Server security.

Securable: These are the resources to which the SQL Server Database Engine authorization system controls access. There are three broader categories under which a securable can be differentiated:
- Server – For example databases, logins, endpoints, availability groups and server roles
- Database – For example database role, application roles, schema, certificate, full text catalog, user
- Schema – For example table, view, procedure, function, synonym
Permission: Every SQL Server securable has associated permissions like ALTER, CONTROL, CREATE that can be granted to a principal. Permissions are managed at the server level using logins and at the database level using users.
Principal: The entity that receives permission to a securable is called a principal. The most common principals are logins and database users. Access to a securable is controlled by granting or denying permissions or by adding logins and users to roles which have access.

All three terms are needed to complete an access request. You need an object that comes under a securable, then you need a level of permissions and finally you need a login or user which will use the permission on the given securable. You cannot proceed with any access request if you are missing any value from the above three items.

Now, let’s cover the topic of checking the permission level of any principal on any securable using the SQL Server system function sys.fn_my_permissions.

Check Effective Permissions of Server or Database Principal

SQL Server includes a very useful system function sys.fn_my_permissions to list all the permissions of a particular principal (user or login) and this system function will help you list all permissions of a principal on a specific database object (securable). If you are not aware of all of the permissions you have on your SQL Server instance then you can use this system function to list all of your permission levels assigned on a specific securable.

Below is the syntax of this system function:

--List all built in permissions
fn_my_permissions ( securable , 'securable_class' )

If you want to check all built in permissions for various securables in SQL Server to decide what level of permission you need to assign for a specific principal on a specific object then you can get this information by using another system function sys.fn_builtin_permissions. I am going to explain both system functions in this article to understand how to check permissions of a principal on a securable in SQL Server.

List All Built in Permissions in SQL Server

Before starting with checking the level of permissions for a principal on any of securable, I would like to explain a bit about all available permissions and securables in SQL Server. You can get this by using one of the system function sys.fn_builtin_permissions, which is a table-valued function that emits a copy of the predefined permission hierarchy.

Run the below statement to list all built in permissions and securables in SQL Server. The first column class_desc shows the securable class whereas the second column permission_name shows the type of permission that can be granted to any principal on the securable.

--List all built in permissions
SELECT * FROM sys.fn_builtin_permissions(DEFAULT);

This is a very big list and cannot be captured in a screenshot. You can see securable "Database" has many types of permissions available that can be granted to a principal for various uses.

If you want to check how many types of securable classes are present in SQL Server, then you can run the below command to get this data. You can see there are 27 securable classes in SQL Server on which you can assign various levels of permissions (that is given in column permission_name) to a principal in SQL Server.

--List all securables
SELECT distinct class_desc FROM sys.fn_builtin_permissions(DEFAULT);

Here is the output of above T-SQL and lists all available securables in SQL Server.

XML SCHEMA COLLECTION
TYPE
EXTERNAL LANGUAGE
DATABASE SCOPED CREDENTIAL
ASYMMETRIC KEY
APPLICATION ROLE
LOGIN
REMOTE SERVICE BINDING
CONTRACT
MESSAGE TYPE
AVAILABILITY GROUP
SERVER ROLE
ROUTE
OBJECT
ASSEMBLY
USER
ENDPOINT
SERVER
DATABASE
SCHEMA
SYMMETRIC KEY
CERTIFICATE
SEARCH PROPERTY LIST
FULLTEXT STOPLIST
ROLE
FULLTEXT CATALOG
SERVICE

List All Effective Permissions I have on Various Securables in SQL Server

Now, we get the permissions in SQL Server that can be granted to a principal to access a securable that is table, view or procedure. Here I will show you another system function sys.fn_my_permissions to check permissions for a user or yourself in a SQL Server instance.

--List all my server level effective permission on SQL Server Instance
SELECT * FROM fn_my_permissions(NULL, 'SERVER');  
GO

You can see all server level permissions below for the current user.

Next, I will check my permissions in database AdventureWorks2019. We can use the same system function, but I will change the securable class from server to database.

--List all effective permissions on database "AdventureWorks2019"
USE AdventureWorks2019
GO
SELECT * FROM fn_my_permissions(NULL, 'Database');  
GO

I am part of the sysadmin fixed server role, so you can see I have all permissions on the server as well as the database.

List All Effective Permissions of Server or Database Level Principals on Various Securable Classes in SQL Server

First, I will create a login and assign some permissions to this login at the database and object level and then see what the function returns.

Run the below command to create a login, create a user in database AdventureWorks2019 and finally assign read permissions to this login in database AdventureWorks2019.

--Create a Login named test
--Create a User test in database AdventureWorks2019
--Assign reader permission to the user test in database AdventureWorks2019
CREATE LOGIN test WITH PASSWORD='TE$T@12'
GO
USE [AdventureWorks2019]
GO
CREATE USER [test] FOR LOGIN [test]
GO
USE [AdventureWorks2019]
GO
ALTER ROLE [db_datareader] ADD MEMBER [test]
GO

You can see all commands have been executed successfully.

Now, we will run the below T-SQL statement in database AdventureWorks2019 to display all assigned permissions for user test. You can get similar details for any login. To do this, you need to change the execution context to target login before running the command. You can also use EXECUTE AS USER to switch the execution context to the test user.

--List all effective permissions for user test in database AdventureWorks2019
EXECUTE AS USER = 'test'
GO
USE AdventureWorks2019
GO
SELECT * FROM fn_my_permissions(null, 'database'); 
GO

Now, you can see the output and all effective permissions of the newly created user in the below screen. You can also compare this output with the image above in which I listed all my permissions at the database level. You can see I have all permissions whereas the below output shows only 4 rows for user test which are CONNECT, SELECT and VIEW permissions as we have assigned above.

Here, I will again check permissions for principal (login) test on the securable class server level. You can change the securable class in system function sys.fn_my_permissions from database to server as shown below.

--List all effective permissions for securable class server
EXECUTE AS LOGIN = 'test'
Go
SELECT * FROM fn_my_permissions(NULL, 'SERVER'); 
Go

The output shows only 3 rows whereas if you compare this output with image above where I listed all my permissions for securable class server, you can see a clear difference between permissions. Have a look at the output to get all effective permissions for securable class server for principal test.

Now, I will demonstrate one more example to list all permissions for principal (login) test on securable class server by assigning a fixed server role to this login.

You can see I have assigned the dbcreator fixed server role to login test by running the below T-SQL command.

--Assign dbcreator fixed server role to login test
ALTER SERVER ROLE [dbcreator] ADD MEMBER [test]
GO

Above running the above command we will run the below for this login to see the changes.

--List all effective permissions for securable class server
EXECUTE AS LOGIN = 'test'
GO
SELECT * FROM fn_my_permissions(NULL, 'SERVER'); 
GO

You can see now there are now 4 rows in the output and the additional row for CREATE ANY DATABASE has been added.

Similarly, you can again do more testing to verify the system function output. Let’s add this login to the securityadmin fixed server role and then check permissions.

--Assign security admin permission for login test
ALTER SERVER ROLE [securityadmin] ADD MEMBER [test]
GO

The command executed successfully.

Below you can see the permissions have been increased and a new permission "ALTER ANY LOGIN" is now in the output.

We can also get all effective permissions for a server or database level principal (login or user) without switching the execution context using the EXECUTE AS command. Using the below commands.

--List all effective permission for other users
SELECT * FROM fn_my_permissions('test', 'login'); 
GO
SELECT * FROM fn_my_permissions('test', 'user'); 
GO

Although the above command will display high level permissions. For example, the test login has ALTER permission, but it’s not showing ALTER on which object or entity.

This user test doesn’t have specific access to database master so the second result set does not show anything.

Now, I have changed the database context to AdventureWorks2019 and then executed same command. You can see user test has displayed the output with their permission levels because this user has access to database AdventureWorks2019.

List All Effective Permissions of a Server or Database Level Principal on a Specific Securable

This section will explain checking permissions for a server or database principal on a specific securable like table, views or stored procedures. Let’s take an example like a user came to you and asked what permissions he has on a table called "Person" in database AdventureWorks2019. You can get this using this system function by running the below T-SQL code. You can see here we have changed the securable class to object in our SQL code.

--List all permissions on table person to principal test 
EXECUTE AS USER = 'test';
GO
USE AdventureWorks2019
GO
SELECT * FROM fn_my_permissions('Person.Person', 'OBJECT')
GO

All permissions for database principal test on securable table person is displayed in the below screen by executing the above T-SQL. You can also see the permission level shows column level information.

You can also check your own permissions on this table by switching the execution context to yourself. You just need to open new query window and run the below command to get the details.

--List all effective permissions for yourself on securable Person
USE AdventureWorks2019
GO
SELECT * FROM fn_my_permissions('Person.Person', 'OBJECT')
GO

Here is the output.

Similarly, we can check the permissions of database principal test on a system view.

--List permissions on a system view
EXECUTE AS USER = 'test';
GO
USE AdventureWorks2019GO
SELECT * FROM fn_my_permissions('INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE', 'OBJECT')
GO

The principal test has only SELECT permission on this system view.

If I run it for my account, you can see I have various level of permission on this system view.

Here, we will check permissions on one more securable for both principals’ test and my own user id.

Run the below T-SQL statements to display all permissions I have on the full text catalog "AW2016FullTextCatalog" in database AdventureWorks2019 and what the test user has.

--List all effective permissions to a database principal test and my own user for a securable "FULLTEXT Catalog" in database Adventureorks2019
SELECT * FROM fn_my_permissions('AW2016FullTextCatalog', 'FULLTEXT CATALOG')
GO
EXECUTE AS USER = 'test';
GO
USE AdventureWorks2019
GO
SELECT * FROM fn_my_permissions('AW2016FullTextCatalog', 'FULLTEXT CATALOG')
GO

Here is the output for both principals and you can compare their permissions as well.

Next Steps

This system function is very useful to get effective permissions of any user or login on any SQL Serve entity. This function makes a DBA’s life easier in SQL Server security management.

About the author

Manvendra Singh has over 5 years of experience with SQL Server and has focused on Database Mirroring, Replication, Log Shipping, etc.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

List SQL Server Login and User Permissions with fn_my_permissions