Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1348
  • Last Modified:

Urgent:Changing Login name of a DB user

Hey experts,

  In the list of users of a DB, I have the following:
 
  dbo (with Login Name ecmp)
  ecmp (with no Login Name)
 
  I want to change it to become as follows:
 
  dbo (with Login Name sa)
  ecmp (with Login Name ecmp)
 
  any help on the fastest way to do that?
 
0
mte01
Asked:
mte01
  • 3
  • 2
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_ca-cz_8qzy.asp

exec sp_change_users_login 'Update_One' , 'dbo', 'sa'
exec sp_change_users_login 'Update_One' , 'ecmp', 'ecmp'
0
 
mte01Author Commented:
>>angelIII

I fixed it after using the AutoFix option; There was an error in using yours regarding the sa login:
Terminating this procedure. 'sa' is a forbidden value for the login name parameter in this procedure.

It's amazing how you know all these internal stored procedures...many thanks for your super help!!
0
 
mte01Author Commented:
Apparently I need to do the change on user dbo too.....it's a bit urgent..any help how to do that??
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>Terminating this procedure. 'sa' is a forbidden value for the login name parameter in this procedure.
interesting. I never tried it on dbo/sa, so good to know.

>Apparently I need to do the change on user dbo too.....it's a bit urgent..any help how to do that??
this looks like you made ecmp user the dbowner.
I know that with sp_changedbowner you can change the ownership to another user of the database, which makes that one dbo, but I don't know how to make the dbo as such.
possibly, this might work:
* use sp_changedbowner to make ecmp user the owner
* drop the dbo
* grant sa login permissions to the database
* use sp_changedbowner to make that new user the owner of the db
0
 
mte01Author Commented:
I solved it by detaching the DB, re-attaching it with sa the owner, then doing the same thing again with ecmp being the owner (which I want it to be), and it did the trick!
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now