Wednesday, May 10, 2006

Permission generation script

Here is a hany security script that will generate the grant execute / select /delete permissions on all objects in your database if you have the need to do so:



Declare @RevokeSQL varchar(1000)
Declare @GrantSQL varchar(1000)
declare @EveryoneRoleName varchar(30)

set @EveryoneRoleName='Public'

set nocount on

select P.ID, U.Name as UserName, o.name as ObjectName,
case P.ProtectType
when 204 then 'GRANT_W_GRANT'
when 205 then 'GRANT'
when 206 then 'REVOKE'
end as ProtectType,
case p.action
when 26 then 'REFERENCES'
when 178 then 'CREATE FUNCTION'
when 193 then 'SELECT'
when 195 then 'INSERT'
when 196 then 'DELETE'
when 197 then 'UPDATE'
when 198 then 'CREATE TABLE'
when 203 then 'CREATE DATABASE'
when 207 then 'CREATE VIEW'
when 222 then 'CREATE PROCEDURE'
when 224 then 'EXECUTE'
when 228 then 'BACKUP DATABASE'
when 233 then 'CREATE DEFAULT'
when 235 then 'BACKUP LOG'
when 236 then 'CREATE RULE'
end as PermissionGranted
into #Temp
from sysprotects P
inner join sysusers U on P.UID = U.UID
inner join sysobjects O on P.ID=O.ID where
P.uid=0 and o.Type<>'S' and
(O.Name not like 'sync%'
and O.Name not like 'sys%'
and O.Name not like 'dt_%')
-- just added the funky syntax for o.name filters
order by UserName, ObjectName

DECLARE cur CURSOR
READ_ONLY
FOR Select UserName, PermissionGranted, ObjectName from #Temp

DECLARE @name varchar(40)
DECLARE @ProtectType varchar(100)
DECLARE @ObjectName varchar(100)
OPEN cur

FETCH NEXT FROM cur INTO @name, @ProtectType, @ObjectName
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
--create has a slightly different syntax, so we have to branch
--here
if @ProtectType like 'Create%'
begin
set @GrantSQL ='Grant ' + @ProtectType + ' to ' + @EveryoneRoleName
print @GrantSQL
-- exec(@GrantSQL)
-- set @RevokeSQL ='Revoke ' + @ProtectType + ' on [' + @ObjectName + '] from ' + @Name
-- print @RevokeSQL
--exec(@RevokeSQL)
end
else
begin
set @GrantSQL ='Grant ' + @ProtectType + ' on [' + @ObjectName + '] to ' + @EveryoneRoleName
print @GrantSQL
-- exec(@GrantSQL)

end
END
FETCH NEXT FROM cur INTO @name, @ProtectType, @ObjectName
END

CLOSE cur
DEALLOCATE cur

--clean up the working table
drop table #Temp

set nocount off

0 Comments:

Post a Comment

<< Home