SQL Server: View Database User Permissions

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