How to write an SQL query to find out which logins have been granted which rights in Sql Server 2005?

Question

I'm responsible for some test database servers. Historically, too many other poeple have access to them. They run on SQL Server 2005. I've been writing queries and wrapping them in scripts so I can run a regular audit of rights. Finding out which users had Administrator rights on the server itself was fine, as was finding out who had the 'sysadmin' role on their login - it was a single line query for the latter.

But how to find out which logins have a User Mapping to a particular (or any) database? I can find the sys.database_principals and sys.server_principals tables. I have located the sys.databases table. I haven't worked out how to find out which users have rights on a database, and if so, what. Every Google search brings up people manually using the User Mapping pane of the Login dialog, rather than using a query to do so. Any ideas?

Answer 1

select * from Master.dbo.syslogins l inner join sys.sysusers u on l.sid = u.sid

This will get you what users are mapped to which logins within a single database.

Answer 2

Check out this msdn reference article on Has_Perms_By_Name. I think you're really interested in examples D, F and G

---

Another idea... I fired up SQL profiler and clicked on the ObjectExplorer->Security->Users. This resulted in (approx) the following query being issued.

SELECT *
FROM
  sys.database_principals AS u
  LEFT OUTER JOIN sys.database_permissions AS dp
  ON dp.grantee_principal_id = u.principal_id and dp.type = N'CO'
WHERE (u.type in ('U', 'S', 'G', 'C', 'K'))
ORDER BY [Name] ASC

Answer 3

Here's how to do this. I ended up finding reference to a sproc in the MSDN docs. I pulled this from the sproc and wrapped it in a loop of all the databases known to the instance.

select DbRole = g.name, MemberName = u.name
  from @NAME.sys.database_principals u, @NAME.sys.database_principals g, @NAME.sys.database_role_members m
  where   g.principal_id = m.role_principal_id
    and u.principal_id = m.member_principal_id
    and g.name in (''db_ddladmin'', ''db_owner'', ''db_securityadmin'') 
    and u.name not in (''dbo'')
  order by 1, 2

This then reports the users that have DBO who perhaps shouldn't. I've already revoked some admin access from some users that they didn't need. Thanks everyone!