locked
Listing all users with access to Database RRS feed

  • Question

  • Hello,

    i was just wondering how to list all user who have access to a certain database in SQL Server 2005.

    There is one possibility

    SELECT * FROM sys.database_principals

    that lists all users from this database BUT it does not show me the login which is DB OWNER.

    The question is, how can i found out which user is db owner select it?

    Thank you for your answer in advance
    Wednesday, June 25, 2008 8:32 AM

Answers

  • Try these two system procs:

     

    Code Snippet

    sp_helpuser

    sp_helpdb

     

     

     

    Let us know if works.

     

    Wednesday, June 25, 2008 12:14 PM

All replies

  • Can you check out this query:

     

    Code Snippet

    select [Login]=sp.[name],

    [DB user]= dp.name,

    [Schema] = default_schema_name

    from sys.database_principals dp

    join sys.server_principals sp

    on sp.sid = dp.sid

     

     

     

     

    Let us know if it meets your need.

    Wednesday, June 25, 2008 9:46 AM
  • hi,

    thank you for your answer. The query you posted works - almost.

    It retrieves all users with access to database, BUT the user who installed the database is not retrieved, i think it is because this user is Sysadmin or Database owner.

    Is there a possibility to retrieve the login of the sysadmin or the database owner?
    Wednesday, June 25, 2008 11:50 AM
  • Try these two system procs:

     

    Code Snippet

    sp_helpuser

    sp_helpdb

     

     

     

    Let us know if works.

     

    Wednesday, June 25, 2008 12:14 PM
  • hi,

    sp_helpdb did the trick!

    thank you very much!
    Thursday, June 26, 2008 6:58 AM