wake-up-neo.com

T-SQL, um alle Benutzerzuordnungen mit Datenbankrollen/Berechtigungen für eine Anmeldung aufzulisten

Ich suche nach einem T-SQL-Skript, das die Datenbanken und die jeweiligen Rollen/Berechtigungen auflisten kann, die einem bestimmten Benutzer zugeordnet sind. Verwenden von SQL Server 2008 R2.

sql-serverdatabasetsqluser-roles
16
muddu83
CREATE TABLE #tempww (
    LoginName nvarchar(max),
    DBname nvarchar(max),
    Username nvarchar(max), 
    AliasName nvarchar(max)
)

INSERT INTO #tempww 
EXEC master..sp_msloginmappings 

-- display results
SELECT * 
FROM   #tempww 
ORDER BY dbname, username

-- cleanup
DROP TABLE #tempww
35
Walter Wildoer
CREATE TABLE #tempww (

    LoginName nvarchar(max),
    DBname nvarchar(max),
    Username nvarchar(max), 
    AliasName nvarchar(max)
)

INSERT INTO #tempww 

EXEC master..sp_msloginmappings 

-- display results

declare @col varchar(1000)

declare @sql varchar(2000)

select @col = COALESCE(@col + ', ','') + QUOTENAME(DBname)

from #tempww Group by DBname

Set @sql='select * from (select LoginName,Username,AliasName,DBname,row_number() over(order by (select 0)) rn from #tempww) src

PIVOT (Max(rn) FOR DBname

IN ('[email protected]+')) pvt'

EXEC(@sql)



-- cleanup
DROP TABLE #tempww
3
Piotr Czekaj

Ich habe eine kleine Abfrage geschrieben, um die Erlaubnis eines Benutzers für eine bestimmte Datenbank zu finden.

    SELECT * FROM   
    (
    SELECT 
    perm.permission_name AS 'PERMISSION'
    ,perm.state_desc AS 'RIGHT'
    ,perm.class_desc AS 'RIGHT_ON'
    ,p.NAME AS 'GRANTEE'
    ,m.NAME AS 'USERNAME'
    ,s.name AS 'SCHEMA'
    ,o.name AS 'OBJECT'
    ,IIF(perm.class = 0, db_name(), NULL) AS 'DATABASE'
    FROM
    sys.database_permissions perm
    INNER JOIN sys.database_principals p ON p.principal_id = perm.grantee_principal_id
    LEFT JOIN sys.database_role_members rm ON rm.role_principal_id = p.principal_id
    LEFT JOIN sys.database_principals m ON rm.member_principal_id = m.principal_id
    LEFT JOIN sys.schemas s ON perm.class = 3 AND perm.major_id = s.schema_id
    LEFT JOIN sys.objects AS o ON perm.class = 1 AND perm.major_id = o.object_id
    UNION ALL
    SELECT 
    perm.permission_name AS 'PERMISSION'
    ,perm.state_desc AS 'RIGHT'
    ,perm.class_desc AS 'RIGHT_ON'
    ,'SELF-GRANTED' AS 'GRANTEE'
    ,p.NAME AS 'USERNAME'
    ,s.name AS 'SCHEMA'
    ,o.name AS 'OBJECT'
    ,IIF(perm.class = 0, db_name(), NULL) AS 'DATABASE'
    FROM
    sys.database_permissions perm
    INNER JOIN sys.database_principals p ON p.principal_id = perm.grantee_principal_id
    LEFT JOIN sys.schemas s ON perm.class = 3 AND perm.major_id = s.schema_id
    LEFT JOIN sys.objects AS o ON perm.class = 1 AND perm.major_id = o.object_id
    ) AS [union]
    WHERE [union].USERNAME = 'Username' -- Username you will search for
    ORDER BY [union].RIGHT_ON, [union].PERMISSION, [union].GRANTEE

Die Berechtigungen fester Datenbankrollen werden in sys.database_permissions nicht angezeigt. Datenbankprinzipalbenutzer verfügen daher möglicherweise über zusätzliche Berechtigungen, die hier nicht aufgeführt sind.

Ich mag es nicht 

    EXECUTE AS USER = 'userName';
    SELECT * FROM fn_my_permissions(NULL, 'DATABASE')

Weil nur abgerufen wird, welche Berechtigungen der Benutzer nicht hat, woher er kommt!

Vielleicht finde ich heraus, wie man die festgelegte Datenbankrollenberechtigung für den Benutzer eines Tages erhält ...

Pls genießen das Leben und hassen die Nutzer: D

2
Code.IT

Dies von hier gestohlen. Ich fand es sehr nützlich!

DECLARE @DB_USers TABLE
(DBName sysname, UserName sysname, LoginType sysname, AssociatedRole varchar(max),create_date datetime,modify_date datetime)

INSERT @DB_USers
EXEC sp_MSforeachdb

'
use [?]
SELECT ''?'' AS DB_Name,
case prin.name when ''dbo'' then prin.name + '' (''+ (select SUSER_SNAME(owner_sid) from master.sys.databases where name =''?'') + '')'' else prin.name end AS UserName,
prin.type_desc AS LoginType,
isnull(USER_NAME(mem.role_principal_id),'''') AS AssociatedRole ,create_date,modify_date
FROM sys.database_principals prin
LEFT OUTER JOIN sys.database_role_members mem ON prin.principal_id=mem.member_principal_id
WHERE prin.sid IS NOT NULL and prin.sid NOT IN (0x00) and
prin.is_fixed_role <> 1 AND prin.name NOT LIKE ''##%'''

SELECT

dbname,username ,logintype ,create_date ,modify_date ,

STUFF(

(

SELECT ',' + CONVERT(VARCHAR(500),associatedrole)

FROM @DB_USers user2

WHERE

user1.DBName=user2.DBName AND user1.UserName=user2.UserName

FOR XML PATH('')

)

,1,1,'') AS Permissions_user

FROM @DB_USers user1

GROUP BY

dbname,username ,logintype ,create_date ,modify_date

ORDER BY DBName,username
1
sam yi

mit fn_my_permissions 

EXECUTE AS USER = 'userName';
SELECT * FROM fn_my_permissions(NULL, 'DATABASE')
0
Arnon Rotem-Gal-Oz

Hast du das sortiert? Ich habe diesen Code gerade hier gefunden:

http://www.pythian.com/news/29665/httpconsultingblogs-emc-comjamiethomsonarchive20070209sql-server-2005_3a00_-view-all-permissions-_2800_2_2900_-aspx/

Ich denke, ich muss ein bisschen zwicken, aber im Wesentlichen hat es das für mich geregelt!

Ich hoffe es tut auch dir!

J

0
JYatesDBA

Ist es das, was du willst? Vielleicht möchten Sie es erweitern, um mehr Informationen aus den sys-Tabellen zu erhalten.

 use master 

 DECLARE @name VARCHAR (50) - Datenbankname 

 DECLARE db_cursor CURSOR FOR 
 Wählen Sie den Namen aus sys.databases 

 OPEN db_cursor
 FETCH NEXT FROM db_cursor IN @name 

 WHILE @@ FETCH_STATUS = 0 
 BEGIN 
 print @name 
 exec ('USE' + @name + '; wähle rp.name, mp.name von sys.database_role_members drm 
 Join sys.database_principals rp auf (drm.role_principal_id = rp.principal_id) auf (drm.member_principal_id = mp.principal_id) ') 
 FETCH NEXT FROM db_cursor IN @name 
 END 

 CLOSE db_cursor 
 DEALLOCATE db_cursor
0
JDC