Query bases de datos con sus repectivos usuarios y permisos

Question

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

Answer 1

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

Answer 2

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

Answer 3

Muchas gracias Javi, como siempre, intercambiando excelente código. Saludos

---

IIslas Master Consultant SQL Server