Programatically return Login name for DBO user?

Question

Post reply

Programatically return Login name for DBO user?

JaybeeSQL

Hall of Fame

Points: 3501
More actions
June 19, 2017 at 9:21 am

#337678

Hi all,
My Google skills seem to have failed me here!!
Any ideas?
Thanks,
JB

Viewing 7 posts - 1 through 6 (of 6 total)

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

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

Join sys.database_principals to sys.server_principals on sid=sid.

John

Thom A SSC Guru Points: 99130 More actions · Answer 2

Do you mean?
SELECT SYSTEM_USER AS ServerLogin, CURRENT_USER AS DatabaseUser;

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

JaybeeSQL Hall of Fame Points: 3501 More actions · Answer 3

John Mitchell-245523 - Monday, June 19, 2017 9:26 AM
Join sys.database_principals to sys.server_principals on sid=sid.
John

Pretty close, this did it for me....

Use YourDatabaseNameHere Go Select S.name

From sys.database_principals As DB

Join sys.server_principals As S

on DB.sid = S.sid

Where default_schema_name = 'dbo'

Thom A SSC Guru Points: 99130 More actions · Answer 4

Just to point to put, that isn't exactly what you asked. That query returns a list of logins that have their default Schema set to dbo on the current database.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

JaybeeSQL Hall of Fame Points: 3501 More actions · Answer 5

Thom A - Monday, June 19, 2017 10:36 AM
Just to point to put, that isn't exactly what you asked. That query returns a list of logins that have their default Schema set to dbo on the current database.

Good point, that's what I should have asked for. I'm working to improve some unclear documentation created by an Oracle DBA, which states a login to a Test DB should be remapped (post refresh) to dbo, so I'm looking at correct result and then working backwards to see what the docu should be stating.

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 6

JaybeeSQL - Tuesday, June 20, 2017 2:46 AM
Thom A - Monday, June 19, 2017 10:36 AM
Just to point to put, that isn't exactly what you asked. That query returns a list of logins that have their default Schema set to dbo on the current database.
Good point, that's what I should have asked for. I'm working to improve some unclear documentation created by an Oracle DBA, which states a login to a Test DB should be remapped (post refresh) to dbo, so I'm looking at correct result and then working backwards to see what the docu should be stating.

It's still not the default_schema_name column you need. It's name. But please, this sounds like an awfully bad idea. If you map a login to dbo in a database, that login becomes the owner of that database, and can make any changes to the database, including dropping it. That probably isn't what you want. Have some harmless login(s) that nobody knows the password to own your databases, and to the logins that are actually used for stuff, assign them only the permissions they need.

John