Friday, June 19, 2009

Revisar permisos directos en SQL.

Es muy frecuente que se establezca como política (o como un Best Practice) que los permisos a los objetos de una base de datos se den solamente a roles y no a usuarios.

Esto es muy importante, es mucho mas recomendable asignar permisos a través de roles que a usuarios, de tal manera, que en caso de migrar una base de datos a un servidor diferente, solamente se tengan que recrear los usuarios, y no recrear los usuarios y tener que volver a asignar permisos.

Les paso un script que utilizo para detectar si se tienen permisos directos a los usuarios de una base de datos, en caso de ser así, se listan los objetos y los permisos, yo lo utiliza dentro de un script de PowerShell que me indica en caso de obtener algun registro que se asignaron mal los permisos.

El script recorre todas las bases de datos y revisa si asignaron permisos directos.

Espero les sirva:


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[BPSQL_Revisa_Permisos_Users]
AS

DECLARE @dbname2 varchar(40);
declare @id varchar(30);

DECLARE dbname CURSOR FOR select name,database_id from master.sys.databases;

CREATE TABLE #Permisos_Users(
[base_de_datos] [varchar](128) NULL,
[usuario] [varchar](128) NULL,
[tipo_permiso] [varchar](128) NULL,
[objeto] [varchar](128) NULL
)

OPEN dbname
FETCH NEXT FROM dbname
INTO @dbname2,@id

WHILE @@FETCH_STATUS = 0
BEGIN
exec('SET NOCOUNT ON;
use [' + @dbname2 + ']
INSERT #Permisos_Users
select d.name,b.name,a.permission_name,c.name from sys.database_permissions a inner join sys.database_principals b on a.grantee_principal_id=b.principal_id
inner join sys.objects c on a.major_id=c.object_id,sys.databases d where b.type_desc in (''SQL_USER'',''WINDOWS_USER'',''WINDOWS_GROUP'') and b.default_schema_name is not null
and b.name <> ''dbo'' and b.name <> ''guest'' and a.permission_name <> ''CONNECT'' and convert(varchar,d.database_id)='+@id+'')

FETCH NEXT FROM dbname INTO @dbname2,@id
END

CLOSE dbname
DEALLOCATE dbname

select * from #Permisos_Users

drop table #Permisos_Users

No comments: