SQLServer: View Database User Permissions

1. view the database role useyourdbgoselectDbRoleg. name, MemberNameu. name, MemberSIDu. sidfromsys. database_principalsu, sys. database_principalsg, sys. Role

1. view SQL 2005 database role use yourdb go select DbRole = g. name, MemberName = u. name, MemberSID = u. sid from sys. database_principals u, sys. database_principals g, sys. database_role_members m where g. principal_id = m. role_principal

1. view the database role of the SQL 2005 user
Use yourdb
Go

Select DbRole = g. name, MemberName = u. name, MemberSID = u. sid
From sys. database_principals u, sys. database_principals g, sys. database_role_members m
Where g. principal_id = m. role_principal_id
And u. principal_id = m. member_principal_id
Order by 1, 2
Go

2. view the server role of the SQL 2005 Logon account
Use master
Go

Select SrvRole = g. name, MemberName = u. name, MemberSID = u. sid
From sys. server_principals u, sys. server_principals g, sys. server_role_members m
Where g. principal_id = m. role_principal_id
And u. principal_id = m. member_principal_id
Order by 1, 2
Go

3. view the permissions granted to SQL 2005 users
Use yourdb
Go

Exec sp_helprotect @ username = 'user name'
Go