March 30, 2005 at 7:55 am
I have some old logins on two of my SQL servers that are causing me some problems. For example:
Server Login “User1” is showing up in database1 as database user “dbo” instead of “User1”. Since I’ve been here, I have always kept server logins and database logins the same if for nothing more than readability.
Now I have a situation where I need to remove these users so my question is two fold.
1) What is the most likely reason this happened? I looked at ownership of the databases (who created them) and it seems that if “User1” created the database, his/her actual database login name has become “dbo”. I can’t seem to change the login because dbo owns all of the db objects.
2) What is the best way to alleviate this problem? There seem to be several stored procedures that deal with changing SQL roles, users, access, etc. However, these are production boxes and I can’t learn this the hard way – not even a little bit.
Suggestions on what SP’s to use and in what order would be very helpful. Syntax assistance isn’t necessary – just the process.
TIA
March 30, 2005 at 9:40 am
Try changing database ownership to SA or whatever user you like to have as the owner.
You can test this on a not development box, or at least a non production database. For example, if the production database happens to be a small one, you can use the last database backup to create a new database under a different name (after giving the new name, switch to the Options tap to get it to generate different MDX, LDF file names). You can then "play" with that copy. Alternatively, just create a new empty database while logged in as someone other than SA. That user should then be assigned as DBO. I think the solution is to change database ownership to SA (or whoever you like to own the databases) using the following while connected to that database:
EXEC sp_changedbowner 'sa'
If the User1 is still aliased to dbo (I don't think it would be, but I don't know) then you should at least be able to remove and re-add or otherwise correct the mapping once it is no longer the dbo.
Since you are apparently removing the users, you should just be able to drop the user at this point and be done with it. If you were to retain them, the drop/add mentioned above should correct them, although I'm not sure if there would be issues if there were current connections for that user during db ownership changing or especially the drop/add.
I've never had occasion to actually do what I'm talking about, so I might lean towards doing this during off hours with a backup handy as a fail safe. And I'd give the process a try on a non-live database first. Perhaps someone else here can address whether or not this can really be done as an "online" operation.
March 30, 2005 at 9:55 am
Aaron is correct, just use the sp_changedbowner proc. Then you should be able to drop User1.
User1 is probably being shown as the database owner because that login was used to either create the database or to do the last restore.
And yes, this is a completely online operation and should not affect other users of the database at all unless they happen to be using the User1 login...although the admonition to make sure you have a backup is always applicable, even in very low risk situations like this.
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
March 31, 2005 at 4:02 am
Also check to make sure who has created objects. You may need to change object owners as well.
March 31, 2005 at 5:11 am
I've had a few problems in the past changing the owner back to SA, it always said that SA was already a member of the database.
To get around it I created a new login without any DB access permissions and then ran the SP to change the database owner to this new login. After that I let me repeat the first step and change it's ownership to SA.
It's probably a known bug but not one I've come across so I thought you might want to know about it.
March 31, 2005 at 8:04 am
Not a bug, BOL states that the new owner account cannot "become the owner of the current database if it already has access to the database through an existing alias or user security account within the database. To avoid this, drop the alias or user within the current database first."
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
March 31, 2005 at 8:15 am
Just to add on what Peterson and Aaron said.
Be careful when you change the ownership of the database. The user who is the current owner would loose acces to the database. I remember on one of our production database, the application id was set to the database owner. We implemented a policy to change sa as the owner of the database and one of our junior dba's with the best of the interest made the change without further analysis. the end result.the application was down. Hope this helps. Be careful to document the privileges.
March 31, 2005 at 1:45 pm
This may be result of moving databases from one server to another where User1's sid was the same as the dbo's.
You may want to try:
exec sp_change_users_login 'report'
this will show all logins with conflicting sid's
If you find any, follow with:
exec sp_change_users_login 'update_one','sql login','sql login'
sql login is the login that is in conflict from the report query.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply