Database Administrators often have a requirement to analyze security permissions to conduct security audits and incident investigations.
SQL Server does support role-based access controls for security principals but there is no guarantee that all permissions are assigned this way and the complex layout of the SQL Server permissions structure makes investigating user permissions difficult.
Getting user permissions in SQL Server can be a complex process, as being able to list database users or server users and parsing role members takes several lines in the T-SQL select statement query. Building a query to check user privileges in SQL Server is also difficult because of the need to go through all securable scopes, starting from the server level down to schema collections and individual objects.
In this article we will look at first how to get user permissions using a T-SQL select statement query and then look at a more straightforward method using the Lepide Auditor.
Using the Native Method
Please follow below steps:
- Start Microsoft SQL Server Management Studio (MSSMS)
- From the File menu, select Connect Object Explorer
- From the Connect to Server dialog box:
- From the Server type list, select Database Engine
- From the Server name text box, type the name of the SQL cluster server
- From the Authentication list box, choose your SQL Server Authentication method and specify the user credentials. Check Remember password if required
- Click Connect
- Upon connection, click New Query and paste the following query into the query field (type the full username, such as ENTERPRISE\J.Smith):
--- 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') aUNION 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') aUNION 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 = perms.value('.', 'NVARCHAR(MAX)')
FROM #permsummary p1
CROSS APPLY (
SELECT permission_name + ', '
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 permission_name
FOR XML PATH(''), TYPE
) AS perms(perms);--- Delete temporary table ---
DROP TABLE #permsummary;
Review the list of server-level roles and principals (member names) in the query execution results:
How Lepide Auditor Helps
Instead of using the native method described above of writing complex scripts and having to manually analyze your reports, the Lepide Auditor for SQL Server allows you to have visibility over all effective user permissions by running one of the many pre-defined SQL server reports. This then allows you to refine your report even further by filtering specific objects and permissions.
The following is an example of the Server permissions report which includes information about Who has modified permissions, When and from which server.
