December 1, 2005 at 9:42 am
Hi!
I've checked weirdman's Script to list all user and roles, and I think it's quite useful. However, it doesn't retrieve the users that don't have a role assigned.
I've been assigned to fix a DB in which there are no roles, and there are 100+ users and I've to design the roles, so I need a quick look of who has permissions to what.
How could I get the users (and/or roles) and their permissions in the tables?
Thanks!
December 1, 2005 at 9:55 am
Syspermissions table. From BOL: "Contains information about permissions granted and denied to users, groups, and roles in the database. This table is stored in each database"
Sysprotects table. From BOL: "Contains information about permissions that have been applied to security accounts with the GRANT and DENY statements. This table is stored in each database."
Regards,Yelena Varsha
December 8, 2005 at 12:48 pm
Thanks for the quick reply, and I apologize for not answering as fast as you did.
I managed to write a little script that helped me a lot, and I wonto to share it with you. I'm also uploading it as a script, hoping it'll be helpfull to someone else.
-----------------------------------------------------------------
select
u.name as 'Role',
o.name as 'Nombre Objeto',
'Object Type'=
case
when o.xtype='FN' then 'Scalar function'
when o.xtype='IF' then 'Inlined table-function'
when o.xtype='P' then 'Stored procedure'
when o.xtype='TF' then 'Table function'
when o.xtype='TR' then 'Trigger'
when o.xtype='U' then 'User table'
when o.xtype='V' then 'View'
when o.xtype='X' then 'Extended Stored Proc'
when o.xtype='S' then 'System Table'
else o.xtype
end,
'Action' =
case
when pr.action=26 then 'REFERENCES'
when pr.action=178 then 'CREATE FUNCTION'
when pr.action=193 then 'SELECT'
when pr.action=195 then 'INSERT'
when pr.action=196 then 'DELETE'
when pr.action=197 then 'UPDATE'
when pr.action=198 then 'CREATE TABLE'
when pr.action=203 then 'CREATE DATABASE'
when pr.action=207 then 'CREATE VIEW'
when pr.action=222 then 'CREATE PROCEDURE'
when pr.action=224 then 'EXECUTE'
when pr.action=228 then 'BACKUP DATABASE'
when pr.action=233 then 'CREATE DEFAULT'
when pr.action=235 then 'BACKUP LOG'
when pr.action=236 then 'CREATE RULE'
end,
'Permission'=
case
when pr.protecttype = 204 then 'GRANT_W_GRANT'
when pr.protecttype = 205 then 'GRANT'
when pr.protecttype = 206 then 'REVOKE'
end
from sysprotects pr, sysobjects o, sysusers u
where pr.id = o.id
and pr.uid = u.uid
order by u.name, o.xtype
---------------------------------------------------------------
Thanx again!!!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply