December 22, 2009 at 7:54 am
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.
December 22, 2009 at 8:46 am
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/
December 22, 2009 at 9:49 am
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.
December 29, 2009 at 5:52 am
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