List ALL system privileges granted to user

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

List ALL system privileges granted to user

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-03-31 : 22:37:28

Lissa writes "How do I list all system privileges granted to a particular user (whether directly or indirectly)? It includes privileges granted to a user directly, through the roles and to roles through another role to that user. I tried retrieving from "USER_SYS_PRIVS" but system privileges granted to a user via a role to another role (i.e: Grant role1 to role2; Grant role2 to myuser) are not displayed. I am using the username as the parameter of the SQL statement.

Any help is greatly appreciated.

--Lissa"

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2002-04-01 : 20:35:50

This doesn't solve your problem completely - but it may help

SELECT     o.name AS object, u.name AS user_name, select_allowed = CASE WHEN (1 & p.actadd) = 0 THEN 0 ELSE 1 END, 
                      update_allowed = CASE WHEN (2 & p.actadd) = 0 THEN 0 ELSE 1 END, insert_allowed = CASE WHEN (8 & p.actadd) = 0 THEN 0 ELSE 1 END, 
                      delete_allowed = CASE WHEN (16 & p.actadd) = 0 THEN 0 ELSE 1 END
FROM         syspermissions p INNER JOIN
                      sysobjects o ON p.id = o.id INNER JOIN
                      sysusers u ON p.grantee = u.uid

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources