How to Check User Privileges in SQL Server

Native Solution vs. Netwrix Auditor for SQL Server
{{ firstError }}
We care about security of your data. Privacy Policy
Native Solution Netwrix Auditor for SQL Server
Native Solution
Netwrix Auditor for SQL Server
Steps
  1. Start Microsoft SQL Server Management Studio (MSSMS).
  2. 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.
  1. Click Connect.
  2. 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 --- 
SELECT entity_class,  
      NAME AS entity_name,  
      subentity_name,  
      permission_name  
INTO   #permsummary  
FROM   ( 
--- Collecting object-level permissions --- 
SELECT 'OBJECT' AS entity_class,  
              NAME,  
              subentity_name,  
              permission_name  
       FROM   sys.objects  
              CROSS apply Fn_my_permissions(Quotename(NAME), 'OBJECT') a  
       UNION ALL 
--- Collecting database-level permissions ---  
       SELECT 'DATABASE' AS entity_class,  
              NAME,  
              subentity_name,  
              permission_name  
       FROM   sys.databases  
              CROSS apply Fn_my_permissions(Quotename(NAME), 'DATABASE') a  
       UNION ALL 
--- Collecting server-level permissions ---  
       SELECT 'SERVER'     AS entity_class,  
              @@SERVERNAME AS NAME,  
              subentity_name,  
              permission_name  
       FROM   Fn_my_permissions(NULL, 'SERVER')) p  
--- Grouping all effective permissions for single object --- 
SELECT DISTINCT entity_class,  
               entity_name,  
               subentity_name,  
               permissions  
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

  1. Click Execute (or hit the F5 key).
  2. Review the list of server-level roles and principals (member names) in the query execution results:
How to View SQL Server Database File Locations - Native Audiuting

 

  1. Open Netwrix Auditor and navigate to Reports -> Predefined -> SQL Server - State-in-Time -> Account Permissions in SQL Server.
  2. 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.
  1. Click View to see a clear report on the effective permissions for the user:
How to View SQL Server Database File Locations - Netwrix Auditor

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.

Learn more about Netwrix Auditor for SQL Server

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.

Related How-tos
How to Find Database Changes in SQL Server How to Create a SQL Server Logon Trigger How to Create a SQL Server Audit Trigger How to Track Login Password Changes in SQL Server How to Check SQL Server View Permissions Changes How to Audit SQL Server Logins