During the Audit, this is common question, which is being asked by Auditor.
During this season, Auditor was very tuff and he identified a suspected login and he wanted to know
- What are the permissions are being assigned to a that login ?
- What all this user can do to at individual database object level ?
- What server permissions are being assigned to this user?
- What all tables can be modified by a particular user ?
- What all stored procedures can be executed by this user ?
- any many more….
Graphically, I shared some screen shots, but he was not pleased with those and he is looking for detailed level report for that particular user.
To answer all these questions, I used a script, which was being written by Pete Carter. This script will show exactly what permissions a login/user has, at all levels of SQL Server, from permissions at the instance level, right the way through to object-level permissions in every database.
SCRIPT to FIND all Permissions assigned to a individual LOGIN ID
EXECUTE AS LOGIN = 'INSERT LOGIN NAME HERE' CREATE TABLE ##ObjectLevel ( DatabaseName NVARCHAR(128), Name NVARCHAR(128), SubEntityName NVARCHAR(128), PermissionName NVARCHAR(128) ) EXECUTE sp_msforeachdb 'USE [?] INSERT INTO ##ObjectLevel SELECT db_name(), t.name, c.subentity_name, c.permission_name FROM sys.objects t CROSS APPLY fn_my_permissions(QUOTENAME(t.name), ''OBJECT'') c' SELECT NULL AS 'Database Owning Object', @@SERVERNAME AS 'Securable Name', a.subentity_name COLLATE Latin1_General_100_CI_AI AS 'Subentity Name', a.permission_name COLLATE Latin1_General_100_CI_AI AS 'Permission Name' FROM fn_my_permissions(NULL, 'SERVER') a UNION ALL SELECT NULL, d.name COLLATE Latin1_General_100_CI_AI, b.subentity_name COLLATE Latin1_General_100_CI_AI, b.permission_name COLLATE Latin1_General_100_CI_AI FROM sys.databases d CROSS APPLY fn_my_permissions(QUOTENAME(d.name), 'DATABASE') b UNION ALL SELECT o.DatabaseName COLLATE Latin1_General_100_CI_AI, o.Name COLLATE Latin1_General_100_CI_AI, o.SubentityName COLLATE Latin1_General_100_CI_AI, o.PermissionName COLLATE Latin1_General_100_CI_AI FROM ##ObjectLevel o DROP TABLE ##ObjectLevel REVERT
The above script will, give you very detailed information at every object of every individual database and might take some (30 sec) time to execute too.
Note : In case, you want to get result for some specific database then simply add WHERE d.name = ‘MyDatabase’ to the end of the second select statement.