SQL Server check user permissions on table

In this SQL Server tutorial, we will cover SQL Server check user permissions on table. In this tutorial, we will understand how we can check user permissions that are granted on a table. And, we will also discuss the following topics.

• SQL Server check user permissions on table
• SQL Server check user permissions on all tables
• SQL Server check permissions on table
• SQL Server check insert permission on table
• SQL Server check select permission on table

SQL Server check permissions on table

In this section, we will understand how we can check permissions associated with a given table.

For this implementation, we will use the sp_table_privileges stored procedure. It is a system stored procedure and it returns a list of permissions associated with the specified table. Here is the general syntax that we can use to list table permissions.

USE database_name
GO  
EXEC sp_table_privileges
 @table_name = 'TableName';  

In the above syntax, first, we need to specify the database name which contains the required table. After this, we need to pass the table name as an input parameter in the execution statement.

Let’s have a demo by implementing an example of this execution. And the complete script for the example is given below.

USE [sqlserverguides]
GO  
EXEC sp_table_privileges
   @table_name = 'ProductTable'; 

In the above example, we are listing the table permissions for the “ProductTable” table which is there in the “sqlserverguides” database. The output of the above example is shown below.

Read: How to get list of users in SQL Server

SQL Server check user permissions on table

In this section, we will understand how we can check the permissions of a database user on the given table.

Now, for the implementation, we will use the sys.fn_my_permissions system function. And this function contains all the permissions that are granted to a securable. Here is the general syntax that we can use to list user permissions on a table.

EXECUTE AS USER = 'user_name';  
SELECT * FROM fn_my_permissions('table_name', 'OBJECT')   
ORDER BY subentity_name, permission_name ;     
GO  

• In the above syntax, first, we are using an EXECUTE statement to define the user name for which we want to list the permissions.
• Next, we are using a SELECT statement to fetch all the columns from the system function.
• And we also have to specify the table name as a first argument in the function.
• Now, as the table is an object in SQL Server, it belongs to the OBJECT securable class which is the second argument for the function.

Let’s execute a simple example of this implementation, and the script for this is given below.

EXECUTE AS USER = 'sqlUser';  
SELECT * FROM fn_my_permissions('dbo.ProductTable', 'OBJECT')   
ORDER BY subentity_name, permission_name ;     
GO  

In the above example, we are checking the permissions for the “sqlUser” database user on the “ProductTable” table which belongs to the “dbo” schema. And it returns the following result.

Read: How to check if SQL Server is running

SQL Server check user permissions on all tables

Till now, we discussed how we can check user permissions on a single table in a database. Now, let’s understand how we can check or list the user permissions on all the tables available within a database.

In SQL Server, we cannot directly list user permissions on all the tables in the database. So, for this implementation, we have to use different system views like sys.schemas to get the name of the schema, sys.objects to get the table name, sys.database_principals to get the user name, and sys.database_permissions to get the permission names, etc.

And here is the complete script that we can use to get the required output.

USE databse_name
GO

SELECT sys.schemas.name AS [Schema], 
       sys.objects.name AS [Object], 
       sys.database_principals.name AS [User Name],
       sys.database_permissions.permission_name, 
       sys.database_permissions.state_desc

FROM sys.database_permissions join sys.objects 
     on sys.database_permissions.major_id = sys.objects.object_id 
     join sys.schemas on sys.objects.schema_id = sys.schemas.schema_id     
     join sys.database_principals 
     on sys.database_permissions.grantee_principal_id =
     sys.database_principals.principal_id

WHERE sys.database_principals.name = 'username'

In this script, we simply need to replace the name of the user with a username and execute the script. For demonstration, we are executing the script to check the user permissions for the “sqlUser” user on all the tables in the sqlserverguides database.

USE sqlserverguides
GO

SELECT sys.schemas.name AS [Schema], 
       sys.objects.name AS [Object], 
       sys.database_principals.name AS [User Name],
       sys.database_permissions.permission_name, 
       sys.database_permissions.state_desc

FROM sys.database_permissions join sys.objects 
     on sys.database_permissions.major_id = sys.objects.object_id 
     join sys.schemas on sys.objects.schema_id = sys.schemas.schema_id     
     join sys.database_principals 
     on sys.database_permissions.grantee_principal_id =
     sys.database_principals.principal_id

WHERE sys.database_principals.name = 'sqlUser'

And after successfully executing the above script, we will get the following output.

Read: What is a stored procedure in sql server

SQL Server check insert permission on table

In SQL Server, inert permission on a table is related to the ability of a user to perform an INSERT statement on a table.

In this section, we will understand how to check if a user is having the insert permission on a table or not. Now, in SQL Server, we can execute this task in 2 ways. The first is by using SQL Server Management Studio, or the second is by executing a Transact-SQL query.

Using SQL Server Management Studio

• First, connect to the Database Engine using the credentials and move to “Object Explorer“
• Under Object Explorer, expand the Databases directory and then, expand the required database that contains the table.
• Next, expand the Tables directory and right-click the required table for which you want to check permissions, and click on the “Properties” option. It will open a new table properties window.
• Now, from the left pane, select the Permissions section. It will display the user and roles permission section in the right pane.

• Next, under the User or roles section, select the user that you want. After this, the list of the permission associated with the user will be listed in the below permissions section.

• Now, from the list, we can easily view and check the insert permission and if it is not granted then we can also grant it to the user.

Using Transact-SQL

Now, let’s understand how we can check whether a user has assigned insert permission on a table using Transact-SQL. Again, for the implementation, we will use the sys.fn_my_permissions system function. And the query for this task is as follows.

EXECUTE AS USER = 'username';  
SELECT * FROM fn_my_permissions('table_name', 'OBJECT')
WHERE permission_name = 'INSERT'
GO

Let’s understand the query by executing the following example.

EXECUTE AS USER = 'sqlUser';  
SELECT * FROM fn_my_permissions('dbo.ProductTable', 'OBJECT')
WHERE permission_name = 'INSERT'
GO  

In the above example, we are checking the insert permission on the “ProductTable” for user “sqlUser“. And if the user has granted the insert permission then, the query will return the insert permission else it will not return anything. The output of the above example is shown in the screenshot below.

Read: How to select latest record in SQL Server

SQL Server check select permission on table

In SQL Server, select permission on a table is related to the ability of a user to perform a SELECT statement on a table.

In this section, we will understand how to check if a user is having the select permission on a table or not. Now, we can execute this task in 2 ways. The first is by using SQL Server Management Studio, or the second is by executing a Transact-SQL query.

Now, using SQL Server Management Studio for checking user permissions on the table is already explained in the previous topic.

So, in this section, we will focus on executing the query for checking the select permission on a specified table. And the query for this execution is given below.

EXECUTE AS USER = 'username';  
SELECT * FROM fn_my_permissions('table_name', 'OBJECT')
WHERE permission_name = 'SELECT'
GO 

Let’s understand the query by executing the following example.

EXECUTE AS USER = 'sqlUser';  
SELECT * FROM fn_my_permissions('dbo.ProductTable', 'OBJECT')
WHERE permission_name = 'SELECT'
GO  

In the above example, we are checking the select permission on the “ProductTable” for user “sqlUser“. And if the user has granted the select permission then, the query will return the select permission else it will not return anything. Here is the execution and output of the above example.

You may also like reading the following articles.

• SQL Server trigger after insert
• SQL Server stored procedure if else
• SQL Server stored procedure case statement
• Rename stored procedure in SQL Server
• PostgreSQL vs SQL Server
• SQL Server find text in stored procedure
• Error: 40 – could not open a connection to sql server
• SQL Server move database files
• Full-text search in SQL Server
• How to view table in SQL Server
• Create a table from view in SQL Server

So, in this SQL Server tutorial, we have covered SQL Server check user permissions on table. And, we understood how we can check user permissions that are granted on a table. And, we have also discussed the following topics.

• SQL Server check user permissions on table
• SQL Server check user permissions on all tables
• SQL Server check permissions on table
• SQL Server check insert permission on table
• SQL Server check select permission on table