April 28, 2004 at 9:27 am
Does anyone have a script that creates the permissions for all users and objects within a database?
Thanks!
April 28, 2004 at 9:44 am
I should add that I don't want to use sp_helprotect.
April 28, 2004 at 10:40 am
Why not use sp_helprotect? I have this with a couple of DTS packages that back up all the permissions nightly and allow me to restore those permissions to any point in time.
April 29, 2004 at 2:44 am
The following may get you started:
Select 'Grant ' +
Case sp.action
when 26 then 'REFERENCES'
when 193 then 'SELECT'
when 195 then 'INSERT'
when 196 then 'DELETE'
when 197 then 'UPDATE'
when 224 then 'EXECUTE'
End +
' on ' + RTrim(so.name) + ' to ' +
(Select su.name from sysusers su where su.uid = sp.uid)
from sysobjects so
inner join sysprotects sp on
so.id = sp.id
where so.type in ('P','U','V', 'FN', 'TF', 'IF') and (so.category <> 2)
GO
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply