Thursday, October 22, 2009

T-SQL to generate all rights for the current database as valid T-SQL statements

Tested on SQL Server 2005 & SQL Server 2000
I wrote up a post on sp_helprotect2 previously, but I like this method better, even though they both use stored procedure sp_helprotect to populate the table.

      

--T-SQL to generate all rights for the current database in valid T-SQL statements

create table dbo.##protect (
[owner] sysname
,
[object] sysname
,
[grantee] sysname
,
[grantor] sysname
,
[protectype] sysname
,
[action] sysname
,
[column]
sysname
)
go


insert
into dbo.##Protect

exec sp_helprotect
go

select protectype + action + ' ON ' + object + ' TO [' + grantee + ']'

from dbo.##protect
where object <>
'.'
order by action
go

drop table dbo.##protect
go

No comments: