Retrieving users for specific SQL Server databases

Question

Hi there:

I have a few SQL Server databases (all in one server), containing their own set of users. Now I'm trying to design a small application that would query those users and then display them in a report (TBD). I've looked over online how to do this, however I didn't find any. Is it possible in SQL Server to retrieve all the users of a database? If so, how?

Answer 1

On SQL Server 2005 and up:

• connect to that specific database you're interested in

  USE Databasename
  

• execute this query

  SELECT * FROM sys.database_principals
  

That gives you a slew of information on all users defined in the database

See the MSDN documentation for a detailed explanation of all rows returned from that view.