none
Query bases de datos con sus repectivos usuarios y permisos RRS feed

  • Pregunta

  • Muy buenas compañeros he estado buscando la manera de como administrar las bases de datos de saber quien tiene permiso a las bases de datos actuales;

    Como hago para obtener una tabla donde me muestre los usuarios a que base de datos si tiene permisos a cada una de las bases de datos de mi servidor ejemplo:

    USUARIO| BASE DE DATOS| ROL

    Pablo| BDPrueba| dataReader

    Pablo| BDPrueba| dataWriter

    Pablo| BDPrueba85| db_owner

    Carlos| BDPruebas85| db_owner

    Carlos| BDCostos| dataReader

    espero me ayuden muchas gracias comunidad

    martes, 14 de julio de 2020 20:30

Todas las respuestas

  • SELECT  
        [UserName] = CASE princ.[type] 
                        WHEN 'S' THEN princ.[name]
                        WHEN 'U' THEN ulogin.[name] COLLATE Latin1_General_CI_AI
                     END,
        [UserType] = CASE princ.[type]
                        WHEN 'S' THEN 'SQL User'
                        WHEN 'U' THEN 'Windows User'
                     END,  
        [DatabaseUserName] = princ.[name],       
        [Role] = null,      
        [PermissionType] = perm.[permission_name],       
        [PermissionState] = perm.[state_desc],       
        [ObjectType] = obj.type_desc,--perm.[class_desc],       
        [ObjectName] = OBJECT_NAME(perm.major_id),
        [ColumnName] = col.[name]
    FROM    
        --database user
        sys.database_principals princ  
    LEFT JOIN
        --Login accounts
        sys.login_token ulogin on princ.[sid] = ulogin.[sid]
    LEFT JOIN        
        --Permissions
        sys.database_permissions perm ON perm.[grantee_principal_id] = princ.[principal_id]
    LEFT JOIN
        --Table columns
        sys.columns col ON col.[object_id] = perm.major_id 
                        AND col.[column_id] = perm.[minor_id]
    LEFT JOIN
        sys.objects obj ON perm.[major_id] = obj.[object_id]
    WHERE 
        princ.[type] in ('S','U')
    UNION
    --List all access provisioned to a sql user or windows user/group through a database or application role
    SELECT  
        [UserName] = CASE memberprinc.[type] 
                        WHEN 'S' THEN memberprinc.[name]
                        WHEN 'U' THEN ulogin.[name] COLLATE Latin1_General_CI_AI
                     END,
        [UserType] = CASE memberprinc.[type]
                        WHEN 'S' THEN 'SQL User'
                        WHEN 'U' THEN 'Windows User'
                     END, 
        [DatabaseUserName] = memberprinc.[name],   
        [Role] = roleprinc.[name],      
        [PermissionType] = perm.[permission_name],       
        [PermissionState] = perm.[state_desc],       
        [ObjectType] = obj.type_desc,--perm.[class_desc],   
        [ObjectName] = OBJECT_NAME(perm.major_id),
        [ColumnName] = col.[name]
    FROM    
        --Role/member associations
        sys.database_role_members members
    JOIN
        --Roles
        sys.database_principals roleprinc ON roleprinc.[principal_id] = members.[role_principal_id]
    JOIN
        --Role members (database users)
        sys.database_principals memberprinc ON memberprinc.[principal_id] = members.[member_principal_id]
    LEFT JOIN
        --Login accounts
        sys.login_token ulogin on memberprinc.[sid] = ulogin.[sid]
    LEFT JOIN        
        --Permissions
        sys.database_permissions perm ON perm.[grantee_principal_id] = roleprinc.[principal_id]
    LEFT JOIN
        --Table columns
        sys.columns col on col.[object_id] = perm.major_id 
                        AND col.[column_id] = perm.[minor_id]
    LEFT JOIN
        sys.objects obj ON perm.[major_id] = obj.[object_id]
    UNION
    --List all access provisioned to the public role, which everyone gets by default
    SELECT  
        [UserName] = '{All Users}',
        [UserType] = '{All Users}', 
        [DatabaseUserName] = '{All Users}',       
        [Role] = roleprinc.[name],      
        [PermissionType] = perm.[permission_name],       
        [PermissionState] = perm.[state_desc],       
        [ObjectType] = obj.type_desc,--perm.[class_desc],  
        [ObjectName] = OBJECT_NAME(perm.major_id),
        [ColumnName] = col.[name]
    FROM    
        --Roles
        sys.database_principals roleprinc
    LEFT JOIN        
        --Role permissions
        sys.database_permissions perm ON perm.[grantee_principal_id] = roleprinc.[principal_id]
    LEFT JOIN
        --Table columns
        sys.columns col on col.[object_id] = perm.major_id 
                        AND col.[column_id] = perm.[minor_id]                   
    JOIN 
        --All objects   
        sys.objects obj ON obj.[object_id] = perm.[major_id]
    WHERE
        --Only roles
        roleprinc.[type] = 'R' AND
        --Only public role
        roleprinc.[name] = 'public' AND
        --Only objects of ours, not the MS objects
        obj.is_ms_shipped = 0
    ORDER BY
        princ.[Name],
        OBJECT_NAME(perm.major_id),
        col.[name],
        perm.[permission_name],
        perm.[state_desc],
        obj.type_desc--perm.[class_desc] 


    IIslas Master Consultant SQL Server

    miércoles, 15 de julio de 2020 0:36
  • Hola Antonio Valladares:

    Puedes usar esta query:

    USE MASTER
    GO
    BEGIN
    DECLARE @SQLVerNo INT;
    SET @SQLVerNo = cast(substring(CAST(Serverproperty('ProductVersion') AS VARCHAR(50)) ,0,charindex('.',CAST(Serverproperty('ProductVersion') AS VARCHAR(50)) ,0)) as int);
    
    IF @SQLVerNo >= 9 
        IF EXISTS (SELECT TOP 1 *
                   FROM Tempdb.sys.objects (nolock)
                   WHERE name LIKE '#TUser%')
            DROP TABLE #TUser
    ELSE
    IF @SQLVerNo = 8
    BEGIN
        IF EXISTS (SELECT TOP 1 *
                   FROM Tempdb.dbo.sysobjects (nolock)
                   WHERE name LIKE '#TUser%')
            DROP TABLE #TUser
    END
    
    CREATE TABLE #TUser (
        
        DBName        SYSNAME,
        [Name]        SYSNAME,
        GroupName     SYSNAME NULL,
        )
    
    IF @SQLVerNo = 8
    BEGIN
    	INSERT INTO #TUser
    	EXEC sp_MSForEachdb
    	'
    	 SELECT 
    	 
    	   ''?'' as DBName,
    	   u.name As UserName,
    	   CASE WHEN (r.uid IS NULL) THEN ''public'' ELSE r.name END AS GroupName
    	   
    	 FROM [?].dbo.sysUsers u
    	   LEFT JOIN ([?].dbo.sysMembers m 
    	   JOIN [?].dbo.sysUsers r
    	   ON m.groupuid = r.uid)
    	   ON m.memberuid = u.uid
    	   LEFT JOIN dbo.sysLogins l
    	   ON u.sid = l.sid
    	 WHERE u.islogin = 1 OR u.isntname = 1 OR u.isntgroup = 1
    	   
    	 ORDER BY u.name
    	'
    END
    
    ELSE 
    IF @SQLVerNo >= 9
    BEGIN
    	INSERT INTO #TUser
    	EXEC sp_MSForEachdb
    	'
    	 SELECT 
    	   
    	   ''?'',
    	   u.name,
    	   CASE WHEN (r.principal_id IS NULL) THEN ''public'' ELSE r.name END GroupName
    	   
    	 FROM [?].sys.database_principals u
    	   LEFT JOIN ([?].sys.database_role_members m
    	   JOIN [?].sys.database_principals r 
    	   ON m.role_principal_id = r.principal_id)
    	   ON m.member_principal_id = u.principal_id
    	   LEFT JOIN [?].sys.server_principals l
    	   ON u.sid = l.sid
    	 WHERE u.TYPE <> ''R''
    	   /*and u.name like ''tester''*/
    	 order by u.name
    	 '
    END
    
    SELECT t.[Name], t.DBName, t.GroupName
    FROM #TUser t
    ORDER BY [name],
    DBName,
     GroupName
    
    DROP TABLE #TUser
    END
    
    
    

    Salida

    Author: Shiva Challa (http://challa.info)

    https://www.sqlservercentral.com/scripts/list-all-usernames-roles-for-all-the-databases

    miércoles, 15 de julio de 2020 2:49
  • Muchas gracias Javi, como siempre, intercambiando excelente código. Saludos

    IIslas Master Consultant SQL Server

    miércoles, 15 de julio de 2020 17:33