¿Se han preguntado como en SQL Server yo puedo averiguar que privilegios existen en mi base de datos y que para que usuarios están siendo estos otorgados? Bueno, existe un método para averiguar esto, que también a su vez puede servir como método de Backup & Recovery. Lo he encontrado desde el el sitio web http://www.mssqltips.com.
Que un DBA posea a su alcance información de esta naturaleza puede ser de gran utilidad frente a determinadas circunstancias. En Oracle sabemos que existen vistas que entregan esta información como DBA_PROFILES, DBA_ROLES,etc. ¿Pero que hay para SQL Server? La Receta mágica es:
El procedimiento almacenado: sp_helprotect.
Este procedimiento almacenado entrega información de privilegios asignados sobre objetos de base de datos. He aquí un ejemplo de la información que entrega:
(Naturalmente ocupamos el usuario SA)
EXEC master.dbo.sp_helprotect;
Aún así para obtener un script más automatizado que nos entregue un procedimiento con todas las sentencias grant de nuestra base de datos, podemos utilizar las siguientes funciones para obtener un listado de comandos listos para usar:
-- Temporary table to hold results from sp_helprotect
drop table #TempPerms
go
CREATE TABLE #TempPerms (
[Owner] sysname NULL,
[Object] sysname NULL,
[Grantee] sysname NULL,
[Grantor] sysname NULL,
[ProtectType] NVARCHAR(9) NULL,
[Action] NVARCHAR(100) NULL,
[Column] NVARCHAR(300) NULL,
[WithGrant] VARCHAR(18) NULL DEFAULT ''
);
-- Let sp_helprotect do the work for us
INSERT INTO #TempPerms
([Owner], [Object], [Grantee], [Grantor], [ProtectType], [Action], [Column])
EXEC master.dbo.sp_helprotect;
-- actualizamos por si acaso la columna ProtectType
-- en el caso de existieran valores 'Grant_WGO'
-- lo que quiere decir que se otorgaron privilegios
-- con permisos de otorgamiento hacia otros usuarios
UPDATE #TempPerms
SET [ProtectType] = 'Grant ', [WithGrant] = ' WITH GRANT OPTION'
WHERE [ProtectType] = 'Grant_WGO';
-- Build and output the permissions
SELECT
CASE [Owner]
WHEN '.' THEN ProtectType + ' ' + [Action] + ' TO [' + [Grantee] + ']' + [WithGrant] + ';'
ELSE CASE [Column]
WHEN '(All+New)' THEN ProtectType + ' ' + [Action] + ' ON [' + [Owner] + '].[' + [Object] + '] TO [' + Grantee + ']' + [WithGrant] + ';'
WHEN '(All)' THEN ProtectType + ' ' + [Action] + ' ON [' + [Owner] + '].[' + [Object] + '] TO [' + Grantee + ']' + [WithGrant] + ';'
WHEN '.' THEN ProtectType + ' ' + [Action] + ' ON [' + [Owner] + '].[' + [Object] + '] TO [' + Grantee + ']' + [WithGrant] + ';'
ELSE ProtectType + ' ' + [Action] + ' ON [' + [Owner] + '].[' + [Object] + '] ([' + [Column] + ']) TO [' + Grantee + ']' + [WithGrant] + ';'
END
END AS 'Permissions'
FROM #TempPerms;
Al ejecutar la consulta verán que el resultado será el siguiente. (les sugiero presionar [CTRL+T] para obtener los resultados en modo texto más rápido.
Grant Execute ON [dbo].[fn_MSgensqescstr] TO [public];
Grant Execute ON [dbo].[fn_MSsharedversion] TO [public];
Grant Execute ON [dbo].[fn_sqlvarbasetostr] TO [public];
Grant Execute ON [dbo].[fn_varbintohexstr] TO [public];
Grant Execute ON [dbo].[fn_varbintohexsubstring] TO [public];
Grant Execute ON [dbo].[MS_sqlctrs_users] TO [public];
Esto funciona a la perfección en SQLServer2000 hacia atrás. Ahora para SQL Server 2005 y superior se tiene que realizar una consulta sobre las tablas sys.database_permissions, sys.database_principals y sys.objects. La query quedaría mas o menos así:
SELECT CASE dperms.state_desc
WHEN 'GRANT_WITH_GRANT_OPTION' THEN 'GRANT'
ELSE state_desc
END
+ ' ' + permission_name + ' ON ' +
CASE dperms.class
WHEN 0 THEN 'DATABASE::[' + DB_NAME() + ']'
WHEN 1 THEN
CASE dperms.minor_id
WHEN 0 THEN 'OBJECT::[' + sch.[name] + '].[' + obj.[name] + ']'
ELSE 'OBJECT::[' + sch.[name] + '].[' + obj.[name] + '] ([' + col.[name] + '])'
END
WHEN 3 THEN 'SCHEMA::[' + SCHEMA_NAME(major_id) + ']'
WHEN 4 THEN 'USER::[' + USER_NAME(major_id) + ']'
WHEN 24 THEN 'SYMMETRIC KEY::[' + symm.[name] + ']'
WHEN 25 THEN 'CERTIFICATE::[' + certs.[name] + ']'
WHEN 26 THEN 'ASYMMETRIC KEY::[' + asymm.[name] +']'
END
+ ' TO [' + dprins.[name] + ']' +
CASE dperms.state_desc
WHEN 'GRANT_WITH_GRANT_OPTION' THEN ' WITH GRANT OPTION;'
ELSE ';'
END COLLATE database_default AS 'Permissions'
FROM sys.database_permissions dperms
INNER JOIN sys.database_principals dprins
ON dperms.grantee_principal_id = dprins.principal_id
LEFT JOIN sys.columns col
ON dperms.major_id = col.object_id AND dperms.minor_id = col.column_id
LEFT JOIN sys.objects obj
ON dperms.major_id = obj.object_id
LEFT JOIN sys.schemas sch
ON obj.schema_id = sch.schema_id
LEFT JOIN sys.asymmetric_keys asymm
ON dperms.major_id = asymm.asymmetric_key_id
LEFT JOIN sys.symmetric_keys symm
ON dperms.major_id = symm.symmetric_key_id
LEFT JOIN sys.certificates certs
ON dperms.major_id = certs.certificate_id
WHERE dperms.type <> 'CO'
AND dperms.major_id > 0;
Eso es todo.
Aquí la fuente origina la Fuente Original:
