This script will generate list of users and their corresponding database, application roles and type of login for a given database.
WITH Roles_CTE(Role_Name, Username)
User_Name(sm.[groupuid]) as [Role_Name],
user_name(sm.[memberuid]) as [Username]
FROM [sys].[sysmembers] sm
[DatabaseUserName] = princ.[name],
[UserType] = CASE princ.[type]
WHEN 'S' THEN 'SQL User'
WHEN 'U' THEN 'Windows User'
WHEN 'G' THEN 'Windows Group'
WHEN 'A' THEN 'Application Role'
WHEN 'R' THEN 'Database Role'
WHEN 'C' THEN 'User mapped to a certificate'
WHEN 'K' THEN 'User mapped to an asymmetric key'
JOIN Roles_CTE on Username = princ.name
where princ.type in ('S', 'U', 'G', 'A', 'R', 'C', 'K')
ORDER BY princ.name
We ran into a case recently where we had the logins and users scripted out on my SQL Server instances, but we didn't have the fixed database roles for a critical database. As a result, our recovery efforts were only partially successful. We ended up trying to figure out what the database role memberships were for that database we recovered but we'd like not to be in that situation again. Is there an easy way to do this?
I have audited for permissions on my databases because users seem to be accessing the tables, but I don't see permissions which give them such rights. I've gone through every Windows group that has access to my SQL Server and into the database, but with no success. How are the users accessing these tables?