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

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 223
  • Last Modified:

Painful Login Creation/Deletion Issues

We had a domain user have the audacity to go and get married.  Thus, she changed her last name.  Unfortunately, we didn't create a new account for her with her new name....rather, we changed her login name (thus using the same SID).  Well, now I need to add her to SQL security and I can't because her SID is listed and it shows her old name.  When I try to delete her SQL login, it says I can't because she is the owner of database objects.

1.  How do I display a list of objects and who owns them? (so I can change them to dbo)

or 2.  Can I just update her username in the sysxlogins table to reflect the new Active Directory account information?

1 Solution

use myDB
exec sp_help

The above will list objects and owners.

use myDB
exec sp_changeobjectowner 'authors', 'John'

The above will change the ownership on the object.  

I suggest you change all object owners to 'dbo'.   If sp_help reveals not too many objects need their ownership changing, you can make the changes one by one.  However, if there too many to do manually, you can script it out if you insert the output of sp_help into a temporary table and then query the temp table.

prairieitsAuthor Commented:

That is great info.  Thanks for that.

One follow-up question is that the objects I am changing ownership on are all views.  When I run exec sp_changeobjectowner 'myViewName', 'dbo', I get an error saying: "Object 'myViewName' does not exist or is not a valid object for this operation."

Do I have to use a different sp to change ownership of a view or how do I change the ownership of the view?

Anthony PerkinsCommented:
You need to qualify it with the existing object owner, as in:
exec sp_changeobjectowner 'existingowner.myViewName', 'dbo'

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

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