Object permissions by users

,

Allow to view object permissions by users

DECLARE	@sNombre_usuario varchar(40)  /* Nombre del usuario */
select @sNombre_usuario='<VALOR>'

if @sNombre_usuario is not null  
begin
	Select 'dbo' as propietario, sysobjects.name as Objeto, 
	CASE WHEN sysprotects.action = 193
			THEN 'SELECT'  
			     WHEN sysprotects.action = 195
				THEN 'INSERT'
		             WHEN sysprotects.action = 196
				THEN 'DELETE'
			     WHEN sysprotects.action = 197
				THEN 'UPDATE'
			     WHEN sysprotects.action = 224
				THEN 'EXECUTE'
			     WHEN sysprotects.action = 26
				THEN 'REFERENCES'
			ELSE 'UNKNOWN TYPE: ' + CAST(sysprotects.action as nvarchar(128))
			END as 'Tipo de permiso' from sysprotects, 
			sysusers,
			sysobjects
	where sysusers.uid= sysprotects.uid
	and 
		sysusers.name =@sNombre_usuario 
	and 
	sysprotects.id = sysobjects.id
		and sysusers.issqlrole <> 1
	union
	select @sNombre_usuario as propietarios, sysobjects.name as Objeto, 
		'EXECUTE' from syscomments, sysobjects
	where syscomments.id= sysobjects.id and sysobjects.uid=user_id(@sNombre_usuario)
	union
	select @sNombre_usuario as propietario, sysobjects.name as objeto, case WHEN xtype='P'
			THEN 'EXECUTE'
		WHEN xtype= 'U'	or xtype= 'V'
			THEN 'SELECT,INSERT,UPDATE,DELETE,REFERENCES'
		ELSE xtype
		end FROM sysobjects
	where uid=user_id(@sNombre_usuario) and xtype not in ('S','PK','D') 
	and sysobjects.name not like 'dt_%'
	ORDER BY Propietario,Objeto
END
else
begin
	Select user_name(sysprotects.uid) as propietario, sysobjects.name as Objeto, 
	CASE WHEN sysprotects.action = 193
			THEN 'SELECT'  
			     WHEN sysprotects.action = 195
				THEN 'INSERT'
		             WHEN sysprotects.action = 196
				THEN 'DELETE'
			     WHEN sysprotects.action = 197
				THEN 'UPDATE'
			     WHEN sysprotects.action = 224
				THEN 'EXECUTE'
			     WHEN sysprotects.action = 26
				THEN 'REFERENCES'
			ELSE 'UNKNOWN TYPE: ' + CAST(sysprotects.action as nvarchar(128))
			END as 'Tipo de permiso' from sysprotects, 
			sysusers,
			sysobjects
	where sysusers.uid= sysprotects.uid
	and 
	sysprotects.id = sysobjects.id
	and sysusers.issqlrole <> 1
	and user_name(sysprotects.uid) <> 'dbo' and user_name(sysprotects.uid) <> 'public'
	union 
	select user_name(sysobjects.uid) as propietarios, sysobjects.name as Objeto, 
		'EXECUTE' from syscomments, sysobjects
	where syscomments.id= sysobjects.id 
	and user_name(sysobjects.uid) <> 'dbo' and user_name(sysobjects.uid) <> 'public'
	union 
	select user_name(sysobjects.uid) as propietario, sysobjects.name as objeto, case WHEN xtype='P'
			THEN 'EXECUTE'
		WHEN xtype= 'U'	or xtype= 'V'
			THEN 'SELECT,INSERT,UPDATE,DELETE,REFERENCES'
		ELSE xtype
		end FROM sysobjects
	where xtype not in ('S','PK','D') 
	and sysobjects.name not like 'dt_%'
	and user_name(sysobjects.uid) <> 'dbo' and user_name(sysobjects.uid) <> 'public'
	ORDER BY Propietario,Objeto
end

Rate

Share

Share

Rate