Я ищу t-sql script, который может отображать базы данных и соответствующие роли/привилегии, отображаемые для определенного пользователя. Использование SQL Server 2008 R2.
T-SQL для отображения всех пользовательских сопоставлений с ролями/разрешениями базы данных для входа в систему
Ответ 1
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
Ответ 2
Я написал небольшой запрос, чтобы найти разрешение пользователя на конкретную базу данных.
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
Разрешения для фиксированных ролей базы данных не отображаются в sys.database_permissions. Таким образом, участники баз данных могут иметь дополнительные разрешения, не перечисленные здесь.
Я не предпочитаю
EXECUTE AS USER = 'userName';
SELECT * FROM fn_my_permissions(NULL, 'DATABASE')
Потому что он просто извлекает, какие разрешения у пользователя нет, откуда они пришли!
Возможно, я узнаю, как присоединиться к фиксированным ролям базы данных, предоставленным для пользователя в один прекрасный день...
Pls нравится Жизнь и ненависть к пользователям: D
Ответ 3
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
Ответ 4
Украсть это от здесь. Я нашел это очень полезным!
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
Ответ 5
используя fn_my_permissions
EXECUTE AS USER = 'userName';
SELECT * FROM fn_my_permissions(NULL, 'DATABASE')
Ответ 6
Это то, что вы хотите? Вы можете расширить его, чтобы получить больше информации из таблиц sys.
use master DECLARE @name VARCHAR(50) -- database name DECLARE db_cursor CURSOR FOR select name from sys.databases OPEN db_cursor FETCH NEXT FROM db_cursor INTO @name WHILE @@FETCH_STATUS = 0 BEGIN print @name exec('USE ' + @name + '; select rp.name, mp.name from sys.database_role_members drm join sys.database_principals rp on (drm.role_principal_id = rp.principal_id) join sys.database_principals mp on (drm.member_principal_id = mp.principal_id)') FETCH NEXT FROM db_cursor INTO @name END CLOSE db_cursor DEALLOCATE db_cursor
Ответ 7
Вы отсортировали это? Я просто нашел этот код здесь:
Думаю, мне нужно немного подкорректировать, но по существу это отсортировало его для меня!
Надеюсь, это и для вас тоже!
J