July 13, 2007 at 9:10 am
Hi,
Is there an SP to find out the permissions for a user-defined database role.
I know there is sp_dbfixedrolepermission for getting the permissions for a fixed-role and sp_srvrolepermission for a fixed server role but I couldn't find any for the user-defined database role.
Yes I can look up the permissions in EM but if there is an SP it will be great help.
Regards
July 16, 2007 at 1:40 am
I don't think there's a stored procedurereadily available is SQL Server but you can try this script http://www.sqlservercentral.com/scripts/viewscript.asp?scriptid=520
Another option I use a lot is the SQLDumpSec utility. It's simple and it's free. http://www.sqlservercentral.com/columnists/cmiller/dumpsqlpermissions.asp
Markus
[font="Verdana"]Markus Bohse[/font]
July 16, 2007 at 3:28 am
We do have a script for us here. Let me know your id so that i shall share it to you.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
July 16, 2007 at 7:25 am
Thanks Markus for the script and the tool.
When I ran the script and the tool for a role in my DB it just showed me the GRANT EXECUTE & GRANT SELECT permissions for SPs and some tables respectively in my DB. But I can CREATE TABLES, VIEWS using that Role. Why doesn't both the tool and the script show CREATE TABLE permission?
Regards,
RSingh
July 16, 2007 at 7:29 am
There is a system stored procedure for this: sp_helprotect. use it with the @username parameter. For instance, to see everything the public role can do:
EXEC sp_helprotect @username = 'public'
This will give you all the permissions, including object level statements like CREATE TABLE.
K. Brian Kelley
@kbriankelley
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply