How to Change Login Name

Question

How to Change Login Name

Andrew J

Mr or Mrs. 500

Points: 507
More actions
January 16, 2006 at 2:35 pm

#170167

Hi All,

I want to ask a little issue. I have reotre a database from .bak file. on that DB, I got: username: dbo, and the login name for that user dbo is 'sa'
I want to change the login name for username 'dbo' from 'sa' to 'myusername'
Please advise on how to do this
Thank you very much for your help, guys.

Cheerss!!

Best Regards

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

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

sqltung Hall of Fame Points: 3962 More actions · Answer 1

I don't believe that is possible. 'sa' is a special login for the system administrator, and 'dbo' is the username for that login.

You can re-create the database under a different login, but your database objects will not be owned by 'dbo'.

Andrew J Mr or Mrs. 500 Points: 507 More actions · Answer 2

Hi,

I think It is possible. when we try to restore database from .mdf .ldf, we can choose the default login for the database right? so, after the database created, it will say: username: dbo => loginname: 'myusername'

but, if I restore the database from .bak file. I did not see this option

Please help to solve this issue.

Thank you Guys

Best Regards

Andrew J Mr or Mrs. 500 Points: 507 More actions · Answer 3

Hi

I found the solution

After completed restoring the database from .bak.

just detach that DB, the attach it again from its .mdf .ldf file that have just created. you will get the option to set the default database login name for dbo there

Thanks Guys

Cheerss!!

Best Regards,

-Andrew J-

sqltung Hall of Fame Points: 3962 More actions · Answer 4

Now, if you go to the Users under the new database, what Login Name do you see for user 'dbo'?

Andrew J Mr or Mrs. 500 Points: 507 More actions · Answer 5

I got 'mydatabaseuser' as the login name

so, the username is: 'dbo' and the login name for dbo is 'mydatabaseuser'

Kelli Routsong Right there with Babe Points: 768 More actions · Answer 6

You can also use the stored procedure 'sp_changedbowner' to accomplish the same thing without the detach/reattach.

use yourdb

exec sp_changedbowner 'mydatabaseuser'

sqltung Hall of Fame Points: 3962 More actions · Answer 7

If the login that owns the db has system admin rights (which yours does), the owner of the database objects will show as 'dbo', but if the login that owns the db does NOT have sys admin rights, the database object owner will show as '[thelogin]'.