T-SQL to Add or Remove Security from a Stored Procedure & Query the System Tables to View All Object Permissions

A fairly common task is to add or remove user or group permissions to a SQL Server Stored Procedure, and also to maintain a list of database objects and the permissions associated with them.

The syntax for these actions has remained fairly similar throughout the various versions of SQL Server, although SQL Server 200 and before do have different methods for querying the system tables. I have tested the add/remove/report syntax shown below on SQL Server 2008.

Remember that when you are using these commands it is identical to grant permissions to a database user as it is to a database group.

Use the following syntax to grant execute database owner (DBO) permissions to a stored procedure for a database user:

GRANT EXECUTE ON [dbo].[MyDatabase] TO [DatabaseUserAccount] AS [dbo]
GO

Use the following syntax to remove permissions to a stored procedure for a database user:

REVOKE EXECUTE ON [dbo].[MyDatabase] TO [DatabaseUserAccount]
GO

Remember that if you are posting your stored procedure to production, and production is tightly restricted, you should make sure that your developer account has View Definition permissions to your stored procedure

There is a good explanation of the View Definition permission level at the MSDN site.

Bottom line is that if you will be tasked with maintaining or troubleshooting your stored procedure, then you should be able to see the code. This is where the View Definition permission comes into play. You can grant your account this access level as follows:

GRANT VIEW DEFINITION ON [dbo].[MyDatabase] TO [DatabaseUserAccount]
GO

Naturally knowing how to remove this level of access is also useful to know. You can remove view definition access as follows:

REVOKE VIEW DEFINITION ON [dbo].[MyDatabase] TO [DatabaseUserAccount]
GO

The View/Revoke Access Syntax is Identical for User Defined Functions

You don’t have to use any different syntax for removing or adding database permissions when dealing with user defined functions. For example removing control access for an account on a scalar function and then adding execute control can be done as follows:

USE [MyDatabase]
GO
REVOKE CONTROL ON [dbo].[fnMyScalarFunction] TO [DatabaseUserAccount]
GO
GRANT EXECUTE ON [dbo].[fnMyScalarFunction] TO [DatabaseUserAccount]
GO

Query All Permissions in your Database

The final step in managing permissions throughout your database is to review all permission settings. Depending on the size of your database this can quickly turn into a tedious process. The simplest way to maintain permissions is to query the SQL Server System tables as follows:

Permissions Report by Account/Object

SELECT 
[sysPrincipals].NAME [Account/Role],
[sysPrincipals].type_desc [Account Type],
[sysObjs].NAME [Object],
[perm].permission_name [Permission],
[perm].state_desc [Permission State]
FROM 
sys.database_permissions [perm]
LEFT OUTER JOIN sys.all_objects [sysObjs]ON [perm].major_id = [sysObjs].OBJECT_ID
INNER JOIN sys.database_principals [sysPrincipals] ON [perm].grantee_principal_id = [sysPrincipals].principal_id
ORDER BY 
[Account/Role],
[Object]

This query will generate a simple and easy to maintain list of the accounts in your database, and what procedures/objects they are associated with. If you prefer to see the report by object rather than by account, you can simply re-arrange the SQL query as follows:

Permissions Report by Object/Account

SELECT 
[sysObjs].NAME [Object],
[sysPrincipals].NAME [Account/Role],
[sysPrincipals].type_desc [Account Type],
[perm].permission_name [Permission],
[perm].state_desc [Permission State]
FROM 
sys.database_permissions [perm]
LEFT OUTER JOIN sys.all_objects [sysObjs]ON [perm].major_id = [sysObjs].OBJECT_ID
INNER JOIN sys.database_principals [sysPrincipals] ON [perm].grantee_principal_id = [sysPrincipals].principal_id
ORDER BY 
[Object],
[Account/Role]

 

 

One thought on “T-SQL to Add or Remove Security from a Stored Procedure & Query the System Tables to View All Object Permissions

  1. Pingback: T-SQL to Add or Remove Security from a Stored Procedure & Query the System Tables to View All Object Permissions | Web App Dev | Scoop.it

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s