Permissions - SQL Server Forums

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 2005 Forums

SQL Server Administration (2005)

Permissions

Author

Topic

sqlpal2009
Yak Posting Veteran

58 Posts

Posted - 2009-03-09 : 11:07:23

Hello All,

This command gives me the details on users who have SELECT/INSERT/UPDATE/DELETE privileges on a sql table. But this list includes theose users as well who have access through a role.

I wanted to see only the users having direct access. How would I do that?

exec sp_table_privileges 'tablename'

Thanks,
-S

guptam
Posting Yak Master

161 Posts

Posted - 2009-03-09 : 18:45:24

Try ..

SELECT object_name(major_id) As ObjectName,
DP.Name AS UserName,
Permission_Name
FROM sys.database_permissions AS DPer
INNER JOIN sys.database_principals AS DP ON DP.principal_id = Dper.grantee_principal_id
WHERE DP.name = 'guest'

--
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCITP: Database Administrator
MCTS: SQL Server 2005
http://sqllearnings.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources