How to list the access the roles the different users have?

— print usernames and the roles assigned to the user, it can be run against any database and it will print the users and the roles assigned to different users
FROM sysusers u, sysusers g, sysmembers m, master.dbo.syslogins sl
where IN (SELECT name FROM dbo.sysusers WHERE ([issqlrole] = 1 OR [isapprole] = 1))
and g.uid = m.groupuid
and sl.sid = u.sid
and g.issqlrole = 1
and u.uid = m.memberuid
and sl.denylogin = 0
order by

Sample output:
localuser db_owner
localuser db_datareader
domain\ADuser db_datareader
domain\ADuser db_denydatawriter

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.