August 5, 2008 at 1:22 am
HI,
How do I script object permissions of a particular user or Group in a database for SQL 2000.
August 5, 2008 at 2:01 am
shubhangi.hirudkar (8/5/2008)
HI,How do I script object permissions of a particular user or Group in a database for SQL 2000.
You can get the permissions from the sysprotects system table. You can join it with sysusers, sysobjects, etc to get more information about what the permissions refer to. E.g.:
SELECT sysprotects.uid AS UserID -- or group ID
, sysusers.name AS UserName
, sysobjects.name AS ObjectName
, u2.name AS ObjectOwner
, action AS Action -- see Books Online sysprotects (actions tell you whether it is an INSERT, EXECUTE, .. permission
, protecttype AS ProtectType -- tells you whether it is a GRANT or DENY
, columns AS Comlumns -- tells you which columns are involved if it is column level permission
FROM sysprotects
LEFT JOIN sysusers ON sysusers.uid = sysprotects.uid
INNER JOIN sysobjects ON sysobjects.id = sysprotects.id
LEFT JOIN sysusers u2 ON sysobjects.uid = u2.uid
ORDER BY sysusers.name
, sysobjects.name
, action
, protecttype
The above gets you back enough information to generate the permission scripts.
Regards,
Andras
August 5, 2008 at 2:16 am
See the help of sysprotects table in books online for the meaning of numbers in 'action' and 'protecttype' columns.
August 5, 2008 at 2:17 am
Apologies.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply