Monday, November 10, 2008

Viendo los permisos de los roles.

Muchas veces, nos topamos con que necesitamos obtener los permisos que tiene una base de datos en partícular, pero no podemos obtener todos los permisos rápidamente o los necesitamos exportar a excel, o queremos hacer una auditoría de permisos...

Para estas ocasiones aquí está un script que obtiene los permisos de una base de datos a todos los roles...

SELECT a.name as OBJETO,c.name as Rol, 'INSERT' as Permiso
FROM SYS.SYSOBJECTS A INNER JOIN sys.database_permissions B ON A.ID=B.MAJOR_ID INNER JOIN sys.database_principals c on b.grantee_principal_id=c.principal_id
where b.permission_name='INSERT' and a.name<>'dtproperties'
union
SELECT a.name as OBJETO,c.name as Rol, 'UPDATE' as Permiso
FROM SYS.SYSOBJECTS A INNER JOIN sys.database_permissions B ON A.ID=B.MAJOR_ID INNER JOIN sys.database_principals c on b.grantee_principal_id=c.principal_id
where b.permission_name='UPDATE' and a.name<>'dtproperties'
union
SELECT a.name as OBJETO,c.name as Rol, 'DELETE' as Permiso
FROM SYS.SYSOBJECTS A INNER JOIN sys.database_permissions B ON A.ID=B.MAJOR_ID INNER JOIN sys.database_principals c on b.grantee_principal_id=c.principal_id
where b.permission_name='DELETE' and a.name<>'dtproperties'
union
SELECT a.name as OBJETO,c.name as Rol, 'EXECUTE' as Permiso
FROM SYS.SYSOBJECTS A INNER JOIN sys.database_permissions B ON A.ID=B.MAJOR_ID INNER JOIN sys.database_principals c on b.grantee_principal_id=c.principal_id
where b.permission_name='EXECUTE' and a.name<>'dtproperties'
union
SELECT a.name as OBJETO,c.name as Rol, 'SELECT' as Permiso
FROM SYS.SYSOBJECTS A INNER JOIN sys.database_permissions B ON A.ID=B.MAJOR_ID INNER JOIN sys.database_principals c on b.grantee_principal_id=c.principal_id
where b.permission_name='SELECT' and a.name<>'dtproperties'
union
SELECT a.name as OBJETO,c.name as Rol, 'VIEW DEFINITION' as Permiso
FROM SYS.SYSOBJECTS A INNER JOIN sys.database_permissions B ON A.ID=B.MAJOR_ID INNER JOIN sys.database_principals c on b.grantee_principal_id=c.principal_id
where b.permission_name='VIEW DEFINITION' and a.name<>'dtproperties'

Lo pueden correr tranquilamente en sus servidores sin problemas de performance, este script lo utilizo regularmente para hacer auditorías de los permisos...

Obtiene un resultado parecido a este:
Objeto Rol Permiso
Tabla1 rol_users_rh select

Solamente se incluyen los permisos de select, insert, update, delete, execute y view definition, pero se le pueden agregar todos los permisos....

Espero les sirva.

1 comment:

Anonymous said...

Funciona en sql >= 2005, esta buenaso, script para permisos que en lo personal me agrada. gracias por la aportacion, no lo he probado en sql 2008 pero en un momento lo hare, gracias por tu aportacion. :d