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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy