sqlschool.gr logo

articles

Articles of SQLschool.gr Team

Get Security Permission Report for all users and objects in a database - Version 2

Antonios Chatzipavlis
Thursday 09 June 2016

Καθημερινά σαν DBA μου έρχονται πολλά αιτήματα που αφορούν την ασφάλεια των SQL Server instances και των database που αυτά έχουν.
Ένα σύνηθες ερώτημα είναι να βγάλω ένα report για τους χρήστες με τις προσβάσεις που έχουν σε συγκεκριμένη database.

Κάτι τέτοιο το έχω μοιραστεί μαζί σας εδώ

Επειδή όμως δουλεύω συνήθως (για να μην πω αποκλειστικά) με windows groups στο παραπάνω report βλέπω μόνο τα groups. Αρκετές φορές όμως θέλω να ξέρω και τα windows accounts που ανήκουν σε αυτό το windows group.

Μέχρι τώρα αν ήθελα κάτι τέτοιο απλά εκτελούσα την xp_logininfo για κάθε group του οποίου ήθελα να δω τα accounts. Σήμερα έπεσα σε μια βάση που είχε πολλά όμως windows groups και ήθελα να τα δω όλα. Σαν τεμπέλης που είμαι είπα να φτιάξω κάτι quick & dirty για να πάρω συγκεντρωτικά την πληροφορία. Έτσι έφτιαξα το παρακάτω

declare @cmd nvarchar(max)='';

create table #t 
(        
   [account name] nvarchar(100)
,  [type] nvarchar(20)
,  [privilege]  nvarchar(20)
,  [mapped login name] nvarchar(100)
,  [permission path] nvarchar(100)
);

select @cmd += 'insert into #t exec xp_logininfo ''' + name + ''',''members'';'  from sys.database_principals where type='G';
exec (@cmd);

select * from #t;

drop table #t;

Παρόλο που αυτό έκανε μια χαρά την δουλειά του αποφάσισα να το ενσωματώσω στην υπάρχουσα stored procedure.

Αρχικά έβαλα τον παραπάνω κώδικα στο τέλος της stored procedure και το αποτέλεσμα ήταν να πάρω δύο result sets.


Ικανοποιητικό αποτέλεσμα αλλά τελικά αποφάσισα να έχω ένα αποτέλεσμα το οποίο να έχει και τα δύο μαζί το οποίο είναι το παρακάτω

USE master;
GO 
ALTER PROC dbo.sp_GetAllObjectsUsersPerms 
AS
    declare @cmd nvarchar(max)='';

    create table #t 
    (        
            [account name] nvarchar(100)
        ,    [type] nvarchar(20)
        ,    [privilege]  nvarchar(20)
        ,    [mapped login name] nvarchar(100)
        ,    [permission path] nvarchar(100)
    );

    select @cmd += 'insert into #t exec xp_logininfo ''' + name + ''',''members'';'  from sys.database_principals where type='G';
    exec (@cmd);
    with r as (
    SELECT
        [UserType]          =        CASE princ.[type]
                                    WHEN 'S' THEN 'SQL User'
                                    WHEN 'U' THEN 'Windows User'
                                    WHEN 'G' THEN 'Windows Group'
                                    END,
        [DatabaseUserName]    =        princ.[name],
        [LoginName]         =        ulogin.[name],
        [Role]              =        NULL,
        [PermissionType]    =        perm.[permission_name],
        [PermissionState]   =        perm.[state_desc],
        [ObjectType]        =        CASE perm.[class]
                                    WHEN 1 THEN obj.[type_desc]       
                                    ELSE perm.[class_desc]            
                                    END,
        [Schema]            =        objschem.[name],
        [ObjectName]        =        CASE perm.[class]
                                    WHEN 3 THEN permschem.[name]       
                                    WHEN 4 THEN imp.[name]             
                                    ELSE OBJECT_NAME(perm.[major_id])  
                                    END,
        [ColumnName]        =        col.[name]
    FROM
        sys.database_principals                AS princ
        LEFT JOIN sys.server_principals        AS ulogin    ON ulogin.[sid] = princ.[sid]
        LEFT JOIN sys.database_permissions    AS perm      ON perm.[grantee_principal_id] = princ.[principal_id]
        LEFT JOIN sys.schemas                AS permschem ON permschem.[schema_id] = perm.[major_id]
        LEFT JOIN sys.objects                AS obj       ON obj.[object_id] = perm.[major_id]
        LEFT JOIN sys.schemas                AS objschem  ON objschem.[schema_id] = obj.[schema_id]
        LEFT JOIN sys.columns                AS col       ON col.[object_id] = perm.[major_id]
                                                            AND col.[column_id] = perm.[minor_id]
        LEFT JOIN sys.database_principals    AS imp       ON imp.[principal_id] = perm.[major_id]
    WHERE
        princ.[type] IN ('S','U','G')
        AND princ.[name] NOT IN ('sys', 'INFORMATION_SCHEMA')

    UNION

    SELECT
        [UserType]          =    CASE membprinc.[type]
                                WHEN 'S' THEN 'SQL User'
                                WHEN 'U' THEN 'Windows User'
                                WHEN 'G' THEN 'Windows Group'
                                END,
        [DatabaseUserName]  =    membprinc.[name],
        [LoginName]         =    ulogin.[name],
        [Role]              =    roleprinc.[name],
        [PermissionType]    =   perm.[permission_name],
        [PermissionState]   =   perm.[state_desc],
        [ObjectType]        =   CASE perm.[class]
                                WHEN 1 THEN obj.[type_desc]        
                                ELSE perm.[class_desc]             
                                END,
        [Schema]            =   objschem.[name],
        [ObjectName]        =   CASE perm.[class]
                                WHEN 3 THEN permschem.[name]       
                                WHEN 4 THEN imp.[name]             
                                ELSE OBJECT_NAME(perm.[major_id])  
                                END,
        [ColumnName]        =    col.[name]
    FROM
        sys.database_role_members          AS members
        JOIN      sys.database_principals  AS roleprinc ON roleprinc.[principal_id] = members.[role_principal_id]
        JOIN      sys.database_principals  AS membprinc ON membprinc.[principal_id] = members.[member_principal_id]
        LEFT JOIN sys.server_principals    AS ulogin    ON ulogin.[sid] = membprinc.[sid]
        LEFT JOIN sys.database_permissions AS perm      ON perm.[grantee_principal_id] = roleprinc.[principal_id]
        LEFT JOIN sys.schemas              AS permschem ON permschem.[schema_id] = perm.[major_id]
        LEFT JOIN sys.objects              AS obj       ON obj.[object_id] = perm.[major_id]
        LEFT JOIN sys.schemas              AS objschem  ON objschem.[schema_id] = obj.[schema_id]
        LEFT JOIN sys.columns              AS col       ON col.[object_id] = perm.[major_id]
                                                           AND col.[column_id] = perm.[minor_id]
        LEFT JOIN sys.database_principals  AS imp       ON imp.[principal_id] = perm.[major_id]
    WHERE
        membprinc.[type] IN ('S','U','G')
        AND membprinc.[name] NOT IN ('sys', 'INFORMATION_SCHEMA')

    UNION

    SELECT
        [UserType]                =   '{All Users}',
        [DatabaseUserName]        =   '{All Users}',
        [LoginName]                =   '{All Users}',
        [Role]                    =   roleprinc.[name],
        [PermissionType]        =   perm.[permission_name],
        [PermissionState]        =    perm.[state_desc],
        [ObjectType]            =   CASE perm.[class]
                                    WHEN 1 THEN obj.[type_desc]      
                                    ELSE perm.[class_desc]           
                                    END,
        [Schema]                =   objschem.[name],
        [ObjectName]            =   CASE perm.[class]
                                    WHEN 3 THEN permschem.[name]     
                                    WHEN 4 THEN imp.[name]           
                                    ELSE OBJECT_NAME(perm.[major_id]) 
                                    END,
        [ColumnName]            =   col.[name]
    FROM
        sys.database_principals            AS roleprinc
        LEFT JOIN sys.database_permissions AS perm      ON perm.[grantee_principal_id] = roleprinc.[principal_id]
        LEFT JOIN sys.schemas              AS permschem ON permschem.[schema_id] = perm.[major_id]
        JOIN      sys.objects              AS obj       ON obj.[object_id] = perm.[major_id]
        LEFT JOIN sys.schemas              AS objschem  ON objschem.[schema_id] = obj.[schema_id]
        LEFT JOIN sys.columns              AS col       ON col.[object_id] = perm.[major_id]
                                                           AND col.[column_id] = perm.[minor_id]
        LEFT JOIN sys.database_principals  AS imp       ON imp.[principal_id] = perm.[major_id]
    WHERE
        roleprinc.[type] = 'R'
        AND roleprinc.[name] = 'public'
        AND obj.[is_ms_shipped] = 0
   )
   select 
        [UserType],
        [DatabaseUserName],
        [LoginName],
        grp.[account name] as [Windows Account],
        [Role],
        [PermissionType],
        [PermissionState],
        [ObjectType],
        [Schema],
        [ObjectName],
        [ColumnName] 
   from r
   LEFT JOIN #t as grp on grp.[permission path] = [LoginName]
   ORDER BY LoginName,[Windows Account];
   
   drop table #t;
GO

-- make it system object
EXEC sys.sp_MS_marksystemobject sp_GetAllObjectsUsersPerms ;
GO

Antonios Chatzipavlis

Antonios Chatzipavlis

Antonios is a Data Solutions Consultant and Trainer. He has been working in IT since 1988. In his career, he has worked as senior developer, IT Manager, Solutions Architect and IT Consultant. Since 1995 he has been devoted on new technologies and software development tools, mainly by Microsoft, either by training company staff and colleagues or assisting them in design, development and implementation as a consultant or chief developer. He has focused in Databases and Data Science since 1995. He specialized in Microsoft SQL Server since version 6.0 in areas like SQL Server Internals, Database Design and Development, Business Intelligence and in 2010 he has started working with Azure Data Platform, NoSQL databases, Big Data Technologies and Machine Learning. He is an active member of many IT communities in Greece, answering colleagues' questions and writing articles in his web site. He is the owner of SQLschool.gr which is a community portal with a lot of information about Microsoft SQL Server. He has been a Microsoft Certified Trainer (MCT) since 2000. Microsoft honored him as MVP on Data Platform due to his activities in SQL Server since 2010. He holds a large number of Microsoft Certifications and Microsoft SQL Server Certifications since version 6.5.

Episode

Transparent Data Encryption (TDE)

image

More Episodes...

Tip

Refresh Intellisence in SSMS

Για να κάνουμε refresh το intellisence μέσα στο SSMS αρκεί να πατήσουμε Ctrl+Shift+R

More Tips...

Become a member

If you want to receive updates from us become a member to our community.

Connect

Explore

Learn


sqlschool.gr © 2010-2023 All rights reserved

This site uses cookies for operational and analytics purposes only. By continuing to browse this site, you agree to their use.