sql-server - una - el usuario no tiene permiso para modificar la base de datos

T-SQL para enumerar todas las asignaciones de usuarios con roles/permisos de base de datos para un inicio de sesión

Question

Estoy buscando un script t-sql que pueda enumerar las bases de datos y los respectivos roles / privilegios asignados para un usuario en particular. Utilizando SQL Server 2008 R2.

Answer 1

¿Es este el tipo de cosa que quieres? Es posible que desee extenderlo para obtener más información de las tablas de 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

Answer 2

¿Has ordenado esto? Acabo de encontrar este código aquí:

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

Creo que tendré que hacer un poco de ajustes, ¡pero esencialmente esto lo ha solucionado para mí!

Espero que lo haga por ti también!

J

Answer 3

Escribí una pequeña consulta para encontrar el permiso de un usuario en una base de datos específica.

 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

Los permisos de los roles de base de datos fijos no aparecen en sys.database_permissions. Por lo tanto, los principales de la base de datos pueden tener permisos adicionales que no figuran aquí

No prefiero

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

¡Porque solo está recuperando los permisos que el usuario no tiene de dónde vienen!

Tal vez averigüe cómo unirme a los permisos de roles de base de datos fijos concedidos para el usuario un día ...

Por favor disfruta la vida y odia a los usuarios: D

Answer 4

Robé esto desde here . ¡Lo encontré muy útil!

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

Answer 5

usando fn_my_permissions

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

Answer 6

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 (''+@col+'')) pvt''
EXEC(@sql)
-- cleanup
DROP TABLE #tempww

Answer 7

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