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 (7)
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.
¿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
¿Has ordenado esto? Acabo de encontrar este código aquí:
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
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
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
usando fn_my_permissions
EXECUTE AS USER = ''userName'';
SELECT * FROM fn_my_permissions(NULL, ''DATABASE'')
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
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