Painful Login Creation/Deletion Issues

Posted on 2005-03-01
Medium Priority
Last Modified: 2006-11-17
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?

Question by:prairieits
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
LVL 10

Accepted Solution

AustinSeven earned 2000 total points
ID: 13440212

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.


Author Comment

ID: 13441229

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?

LVL 75

Expert Comment

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

Featured Post

What Is Blockchain Technology?

Blockchain is a technology that underpins the success of Bitcoin and other digital currencies, but it has uses far beyond finance. Learn how blockchain works and why it is proving disruptive to other areas of IT.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question