<p>I'm responsible for some test database servers.  Historically, too many other poeple have access to them.  They run on <code>SQL Server 2005</code>.  </p>

<p>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 <code>sysadmin</code> role on their login - it was a single line query for the latter.</p>

<p>But how to find out which logins have a User Mapping to a particular (or any) database?  </p>

<p>I can find the <code>sys.database_principals</code> and <code>sys.server_principals</code> tables.  I have located the <code>sys.databases table</code>.  I haven't worked out how to find out which users have rights on a database, and if so, what.  </p>

<p>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?</p>

[{'answer_id': 66349, 'author': 'Jason Punyon', 'author_id': 6212, 'author_profile': 'https://Stackoverflow.com/users/6212', 'pm_score': 1, 'selected': False, 'text': '<pre><code>\nselect * from Master.dbo.syslogins l inner join sys.sysusers u on l.sid = u.sid\n</code></pre>\n\n<p>This will get you what users are mapped to which logins within a single database.</p>\n'}, {'answer_id': 66511, 'author': 'Amy B', 'author_id': 8155, 'author_profile': 'https://Stackoverflow.com/users/8155', 'pm_score': 2, 'selected': False, 'text': '<p>Check out this msdn reference article on <a href="http://msdn.microsoft.com/en-us/library/ms189802.aspx" rel="nofollow noreferrer">Has_Perms_By_Name</a>.  I think you\'re really interested in examples D, F and G</p>\n\n<hr>\n\n<p>Another idea... I fired up SQL profiler and clicked on the ObjectExplorer->Security->Users.  This resulted in (approx) the following query being issued.</p>\n\n<pre><code>SELECT *\nFROM\n  sys.database_principals AS u\n  LEFT OUTER JOIN sys.database_permissions AS dp\n  ON dp.grantee_principal_id = u.principal_id and dp.type = N\'CO\'\nWHERE (u.type in (\'U\', \'S\', \'G\', \'C\', \'K\'))\nORDER BY [Name] ASC\n</code></pre>\n'}, {'answer_id': 73846, 'author': 'Julian Simpson', 'author_id': 9727, 'author_profile': 'https://Stackoverflow.com/users/9727', 'pm_score': 2, 'selected': True, 'text': "<p>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.</p>\n\n<pre><code>select DbRole = g.name, MemberName = u.name\n  from @NAME.sys.database_principals u, @NAME.sys.database_principals g, @NAME.sys.database_role_members m\n  where   g.principal_id = m.role_principal_id\n    and u.principal_id = m.member_principal_id\n    and g.name in (''db_ddladmin'', ''db_owner'', ''db_securityadmin'') \n    and u.name not in (''dbo'')\n  order by 1, 2\n</code></pre>\n\n<p>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!</p>\n"}]
['https://Stackoverflow.com/questions/66164', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/9727/']