SQL Server Training | Tutorials | How to's

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

1. What are the permissions are being assigned to a that login ?
2. What all this user can do to at individual database object level ?
3. What server permissions are being assigned to this user?
4. What all tables can be modified by a particular user ?
5. What all stored procedures can be executed by this user ?
6. 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.