June 6, 2007 at 4:08 am
Hi everyone.
Having an issue dropping a user.
History
Restored database onto new server following DR event. However need to drop and recreate user with same permissions again , and get this error.
Server: Msg 15183, Level 16, State 1, Procedure sp_MScheck_uid_owns_anything, Line 17
The user owns objects in the database and cannot be dropped.
Any clues on what I can do to drop this user , or even get this user to work again ?
Cheers all
June 6, 2007 at 4:21 am
SELECT [name] FROM sysobjects
WHERE uid = USER_ID('User_to_be_dropped')
This will give you a list of objects owned by that user. You will then have to decide whether to drop those objects or change their owner before you can drop the user.
John
June 6, 2007 at 4:37 am
John
Many thanks for quick repsonse.
Ok I have that list , can you explain how I go about transferring the owner to another owner ?
Will this effect the app , I mean will I have to transfer them from my user A to user B , drop that user A, then recreate User A and transfer ownership back from user B to user A ?
thanks for your help
June 6, 2007 at 4:57 am
Hi
check out the following command in BOL which will change the ownership of an object
EXEC sp_changeobjectowner 'objectname', 'newownername'
This may well affect your app, but it depends on how your application functions. On my system, which is a third party app with front-end enforced security and mixed-mode authentication, everything is owned by dbo (SQLServer 2K will automatically look for the object in the dbo schema if it cannot find the object in the current schema).
I am wondering if changing ownership of the database objects is going to be sufficient. I am not sure what the effect of your problem user having that database as the default database, and being in the db_owner role might be?
David
If it ain't broke, don't fix it...
June 6, 2007 at 10:15 am
Why are you dropping the user if you are going to re-add them with the same permissions?
Are they orphaned and you are trying to resolve that? If so, just run these two commands:
sp_change_users_login 'report'
that will give you a list of orphaned users. Then run:
sp_change_users_login 'auto_fix', ''
Change to the actual login name.
-SQLBill
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply