Checking SQL permissions on many SQL Server objects

By Steve Endow

I am working with a Dynamics GP client that is having an odd issue with an ISV solution related to SQL server permissions.  The ISV solution is designed to only allow one Dynamics GP login to access its tables and stored procedures--so that only one instance of the application is running at a time.

Normally this works fine and I've worked with dozens of clients without issue.  But one client is having an issue where the application is reporting that a second user has activated and de-activated the application.

We've checked the settings and checked the SQL Server database role used by the application and everything appears to be setup correctly.  But before I contacted the developer, I wanted to check one last thing--the permission on the individual SQL objects.  Even though the application uses a database role to manage its permissions, we have seen at least one third party GP product perform permission updates on every object in the GP databases, causing problems with this application.

So how do you check the permissions on a SQL object?  If you only need to check one or two objects, you can use SQL Server Management Studio.  Just right click on an object, such as a table, and select Properties.  Then click on the Permissions page.

Here is an example of a table used by Post Master for Dynamics GP.  It does not use the DYNGRP role, and you can see that only the rPostMaster database role is assigned to the table.

This approach of checking permissions works, but it is very tedious to do this for multiple objects.  I needed the GP client to check permissions on dozens of tables and stored procedures, and I didn't want to have to send instructions and a list of every object.

So after some Googling, I found some SQL queries that allow you to query the permissions on ranges of objects based on name.

So for tables, you can run this query:

sp_table_privileges 'ESS%'

This displays all of the permissions for tables that begin with ESS, which are the tables used by Post Master.

For stored procedures, it is a little bit more involved, but fortunately someone on Stack Overflow had posted the script:

SELECT OBJECT_NAME(major_id), USER_NAME(grantee_principal_id), permission_name
FROM sys.database_permissions p
WHERE OBJECT_NAME(major_id) LIKE 'zDP_ESS%'


You can modify the queries to look for objects starting with RM, PM, SOP, GL, etc., or all objects.

When you run these queries you get a nice list of all permissions that you can quickly scan to identify anomalies.

I was able to send both queries to the client, and a few minutes later, I had the results and was able to verify the permissions on all Post Master database objects.

Steve Endow is a Dynamics GP Certified Trainer and Dynamics GP Certified IT Professional in Los Angeles.  He is also the owner of Precipio Services, which provides Dynamics GP integrations, customizations, and automation solutions.

You can also find him on Google+ and Twitter

http://www.precipioservices.com