April 15, 2008 at 1:42 pm
I think I've got it. I dissected the code in sysprotects and voila:
USE dbName;
IF EXISTS (SELECT * FROM sysprotects WHERE user_name(sysp.uid) = 'dbRole')
EXEC sp_helprotect NULL, 'dbRole';
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
March 30, 2009 at 5:38 pm
This is the sp i use to get the permissions for a particular obj..
I guess you modify it as per your needs..
CREATE PROCEDURE sp_get_permissions (@str_obj_name varchar(300) = null)
as
BEGIN
select ' GRANT ' +
Substring(
CASE WHEN sysprotects_1.action is null THEN CASE WHEN sysobjects.xtype IN ('P','FN','IF') THEN '' ELSE '' END ELSE 'SELECT,' END +
CASE WHEN sysprotects_2.action is null THEN CASE WHEN sysobjects.xtype IN ('P','FN','IF') THEN '' ELSE '' END ELSE 'INSERT,' END +
CASE WHEN sysprotects_3.action is null THEN CASE WHEN sysobjects.xtype IN ('P','FN','IF') THEN '' ELSE '' END ELSE 'UPDATE,' END +
CASE WHEN sysprotects_4.action is null THEN CASE WHEN sysobjects.xtype IN ('P','FN','IF') THEN '' ELSE '' END ELSE 'DELETE,' END +
CASE WHEN sysprotects_5.action is null THEN CASE WHEN sysobjects.xtype IN ('U','V') THEN '' ELSE 'No' END ELSE 'EXEC,' END,
1,len( CASE WHEN sysprotects_1.action is null THEN CASE WHEN sysobjects.xtype IN ('P','FN','IF') THEN '' ELSE '' END ELSE 'SELECT,' END +
CASE WHEN sysprotects_2.action is null THEN CASE WHEN sysobjects.xtype IN ('P','FN','IF') THEN '' ELSE '' END ELSE 'INSERT,' END +
CASE WHEN sysprotects_3.action is null THEN CASE WHEN sysobjects.xtype IN ('P','FN','IF') THEN '' ELSE '' END ELSE 'UPDATE,' END +
CASE WHEN sysprotects_4.action is null THEN CASE WHEN sysobjects.xtype IN ('P','FN','IF') THEN '' ELSE '' END ELSE 'DELETE,' END +
CASE WHEN sysprotects_5.action is null THEN CASE WHEN sysobjects.xtype IN ('U','V') THEN '' ELSE 'No' END ELSE 'EXEC,' END) - 1 )
+ ' ON ' + sysobjects.name + ' to ' + sysusers.name + char(10) + ' GO '
from
sysusers
full join sysobjects on ( sysobjects.xtype in ( 'U','P','V','FN','IF' ) and sysobjects.Name NOT LIKE 'dt%' )
left join sysprotects as sysprotects_1
on sysprotects_1.uid = sysusers.uid and sysprotects_1.id = sysobjects.id and sysprotects_1.action = 193 and sysprotects_1.protecttype in ( 204, 205 )
left join sysprotects as sysprotects_2
on sysprotects_2.uid = sysusers.uid and sysprotects_2.id = sysobjects.id and sysprotects_2.action = 195 and sysprotects_2.protecttype in ( 204, 205 )
left join sysprotects as sysprotects_3
on sysprotects_3.uid = sysusers.uid and sysprotects_3.id = sysobjects.id and sysprotects_3.action = 197 and sysprotects_3.protecttype in ( 204, 205 )
left join sysprotects as sysprotects_4
on sysprotects_4.uid = sysusers.uid and sysprotects_4.id = sysobjects.id and sysprotects_4.action = 196 and sysprotects_4.protecttype in ( 204, 205 )
left join sysprotects as sysprotects_5
on sysprotects_5.uid = sysusers.uid and sysprotects_5.id = sysobjects.id and sysprotects_5.action = 224 and sysprotects_5.protecttype in ( 204, 205 )
where
(sysprotects_1.action is not null or sysprotects_2.action is not null or
sysprotects_3.action is not null or sysprotects_4.action is not null or
sysprotects_5.action is not null)
and (sysobjects.name like @str_obj_name or isnull(@str_obj_name,'***') = '***')
order by
sysusers.name, sysobjects.name
END
Regards,
Raj
Viewing 2 posts - 16 through 16 (of 16 total)
You must be logged in to reply to this topic. Login to reply