title: How to write an SQL query to find out which logins have been granted which rights in Sql Server 2005?
body: <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>