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?

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.


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.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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'
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.