what accounts are aliased to dbo?

  • I need to get what accounts are aliased to dbo in various databases in my MSSQL 2005 server. I know sp_helpuser returns it but that procedure returns two result sets. From what I've read, you can't insert that kind of output into a table for further manipulation (because the procedure returns two result sets).

    How can I get what accounts are aliased to dbo? I need to put these results in a table for security auditing.

  • Actually sp_helpuser returns only one recordset. In any case you can use this statement also:

    select USER_NAME(role_principal_id) as RoleName, USER_NAME(member_principal_id)

    from sys.database_role_members

    Adi

    --------------------------------------------------------------
    To know how to ask questions and increase the chances of getting asnwers:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • Hi Adi.

    Thanks for the reply.

    In my database, I have a user account (winfas) aliased to dbo. When I run sp_helpuser in that database, I get two result sets. The first set shows all users that have some kind of access, including dbo and its login name (sa). The second result set has loginname "winfas" and usernamealiasedto "dbo". It is this part that I need.

    When I ran your query, the only db_owner entry I had was rolename of "db_owner" and username of "dbo".

    I just need to get all users aliased to dbo in any given database.

    -------------------

    I just found sys.database_principal_aliases and got this:

    select 'LoginName' = suser_sname(al.sid)

    ,'UserNameAliasedTo' = pr.name

    from sys.database_principals pr, sys.database_principal_aliases al

    where pr.principal_id = al.alias_principal_id

    order by 1

    To me it looks like this gives me what I need. Or at least it does for the database and the one alias that I have.

  • Looks to me that your winfas user is infact the database owner, which is why it is aliased to the dbo user.

    Gethyn Elliswww.gethynellis.com

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply