- Start Microsoft SQL Server Management Studio (MSSMS).
- In the File menu, click Connect Object Explorer. Then, in the Connect to Server dialog box:
- In the Server type list box, select Database Engine.
- In the Server name text box, type the name of the SQL cluster server.
- In the Authentication list box, choose your SQL Server Authentication method and specify the user credentials. If you do not want to re-type the password every time you connect to the server, tick Remember password.
- Click Connect.
- Upon connection, click “New Query” and paste the following query into the query field (type the full username, such as ENTERPRISE\J.Carter):
--- Creating temporary table for permissions list ---
NAME AS entity_name,
--- Collecting object-level permissions ---
SELECT 'OBJECT' AS entity_class,
CROSS apply Fn_my_permissions(Quotename(NAME), 'OBJECT') a
--- Collecting database-level permissions ---
SELECT 'DATABASE' AS entity_class,
CROSS apply Fn_my_permissions(Quotename(NAME), 'DATABASE') a
--- Collecting server-level permissions ---
SELECT 'SERVER' AS entity_class,
@@SERVERNAME AS NAME,
FROM Fn_my_permissions(NULL, 'SERVER')) p
--- Grouping all effective permissions for single object ---
SELECT DISTINCT entity_class,
FROM (SELECT *
FROM #permsummary) p1
CROSS APPLY (SELECT permission_name + ', '
FROM (SELECT *
FROM #permsummary) p2
WHERE p2.entity_class = p1.entity_class
AND p2.entity_name = p1.entity_name
AND p2.subentity_name = p1.subentity_name
ORDER BY entity_class
FOR xml path('')) D ( permissions )
--- Delete temporary table ---
DROP TABLE #permsummary
- Click Execute (or hit the F5 key).
- Review the list of server-level roles and principals (member names) in the query execution results:
- Open Netwrix Auditor and navigate to Reports -> Predefined -> SQL Server - State-in-Time -> Account Permissions in SQL Server.
- Specify the following filters:
- In the User account filter, type the full user name (such as ENTERPRISE\J.Carter).
- In the Object type filter, choose Select all to see all permissions for a user.
- Click View to see a clear report on the effective permissions for the user:
The “Means Granted” field reflects server roles and database roles assigned to the user in question. Clicking values will open the SQL Server Means Granted report, which shows each account with explicit and inherited permissions on the selected SQL Server object and how those permissions were granted.
Why you need an alternative to T-SQL Queries to Get User Permissions in SQL Server.
DBAs are often required to retrieve and analyze security principals’ permissions in order to conduct security audits and incident investigations. While SQL Server supports role-based access controls for security principals, there’s no guarantee that all permissions are assigned this way, and the complex layout of its permissions structure makes investigating user permissions difficult. Often, simply getting user permissions in SQL Server can be complicated, as listing database users or server users and parsing role members will take several lines in the T-SQL select statement query. Building a query to check user privileges in SQL Server is also troublesome due to the need to go through all securable scopes, starting from the server level down to schema collections and individual objects.
Although checking the available data and user permissions with T-SQL may seem beneficial, it takes advanced scripting skills to collate your report into a readable format. Likewise, while the built-in sys.fn_my_permissions function looks like a helpful tool, it returns results only for a single securable and only for the current connection. Even using the EXECUTE AS function to view the effective permissions of another security principal (as shown in the above example) is inefficient, as you will have to compile complex queries and sift through innumerable rows of data.
Instead of forcing you to carry out tedious scripting and manual analysis of your reports, Netwrix Auditor for SQL Server allows you to know all effective user permissions with just a couple of clicks, then enables you to refine your report even further by filtering specific objects and permissions.