See which roles users are in

[LonnieJohnson]

How do I query the system tables to see all the roles a particular user name is associated with. I looked at the sysusers table and did not see what I needed.

Thanks in advance.

ProDev, MS Access Applications
Visit me at ==>

http://www.prodev.us

Contact me at ==>lonniejohnson@prodev.us

May God bless you beyond your imagination!!!

 

[ralphwiggum]

Have you tried the master.dbo.syslogins table? It has a column for each SQL Server fixed role. If the value in the column is 1 then the login is a member of the corresponding role.

 

[LonnieJohnson]

Maybe I don't understand too well.

I have created Roles in a database to put users in. So would I be able to query the dB for a user name to determine which of the roles that I created that user was in?

Thanks again.

ProDev, MS Access Applications
Visit me at ==>

http://www.prodev.us

Contact me at ==>lonniejohnson@prodev.us

May God bless you beyond your imagination!!!

 

[JamesLean]

Try this query:

SELECT u.name AS username, r.name AS role
FROM sysusers u JOIN sysmembers m ON u.uid = m.memberuid
  JOIN sysusers r ON m.groupuid = r.uid
WHERE u.name = '<user>'

--James