dropping a user

I am trying to drop a user and i get the error:

i ran the command

sp_dropuser 'webuser'
go

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.

Server: Msg 15284, Level 16, State 1, Procedure sp_MScheck_uid_owns_anything, Line 34
The user has granted or revoked privileges to the following in the database and cannot be dropped.
gupshupAsked:
Who is Participating?
 
arbertConnect With a Mentor Commented:
webuser owns objects in the database--you can't drop the user until you change the owner on the object.....
0
 
Lori99Commented:
You should have received a list of objects that the user 'webuser' owns as part of the error message.  As arbert said, you need to change the owner on these objects to someone else before you can drop him.  Use the stored procedure sp_changeobjectowner 'webuser', 'newuser' to change the owner.
0
 
gupshupAuthor Commented:
basically webuser already exists in the database because i did a restore however i want to drop this and create it again because it is not allowing me to access the old webser id and login.......is changing the owner of the object the best way, and if i change the owner what should i change it to?
0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
Lori99Commented:
You can create new temporary user, change the object owner to the new user, drop and recreate webuser and then use change object owner to change it back to webuser.
0
 
gupshupAuthor Commented:
when i run
sp_changeobjectowner 'webuser', 'NEWwebuser' i get the error

Server: Msg 15001, Level 16, State 1, Procedure sp_changeobjectowner, Line 38
Object 'webuser' does not exist or is not a valid object for this operation.

when i run exec sp_grantdbaccess 'webuser'
i get: Server: Msg 15007, Level 16, State 1, Procedure sp_grantdbaccess, Line 98
The login 'webuser' does not exist.

when i run exec sp_revokedbaccess 'webuser' i get
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.

Server: Msg 15284, Level 16, State 1, Procedure sp_MScheck_uid_owns_anything, Line 34
The user has granted or revoked privileges to the following in the database and cannot be dropped.


HELP I NEED TO GET THIS WORKING  THANKS!!!!!
0
 
Lori99Commented:
Does this user's login exist at the server level?  If not, try adding webuser as a SQL Server login.  If it does exist, you may need to relink the user.

Try,

sp_change_users_login 'Update_One', 'UserName', 'LoginName'

UserName = the name of the user in the current database
LoginName = the name of a SQL Server login
0
 
gupshupAuthor Commented:
lori99 i tried your request and it says
Server: Msg 15001, Level 16, State 1, Procedure sp_changeobjectowner, Line 38
Object 'webuser' does not exist or is not a valid object for this operation.  

however when i run
exec sp_revokedbaccess 'webuser' its says

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.

Server: Msg 15284, Level 16, State 1, Procedure sp_MScheck_uid_owns_anything, Line 34
The user has granted or revoked privileges to the following in the database and cannot be dropped.

to me this is contradicting.....i dont understand
0
 
Lori99Connect With a Mentor Commented:
Comment from gupshup
Date: 04/14/2004 12:51PM PDT

when i run
sp_changeobjectowner 'webuser', 'NEWwebuser' i get the error

Server: Msg 15001, Level 16, State 1, Procedure sp_changeobjectowner, Line 38
Object 'webuser' does not exist or is not a valid object for this operation.  

I just noticed that you did not include the object name above command.  That is why you received an error.  You need to give it an object name such as a table name or whatever object webuser owns.  It should read

sp_changeobjectowner 'objectname', 'NEWwebuser'

0
 
gupshupAuthor Commented:
waht would i put for the object name.....my database name is hhdir
0
 
gupshupAuthor Commented:
my server has crashed....it has the user webuser on it......i NEED TO USE THIS ON THE NEW SERVER.....i have restored all of the databases but all of the messages i am getting above for webuser..... i do not have access to the old server.....
0
 
gupshupAuthor Commented:
when i do a select * from sysusers i do not see 'webuser'
however when i try to create a new user, it says it already exists
0
 
gupshupAuthor Commented:
the id from the the master of webuser doesnt match the new one.....thats the probelm....should i take master and restore it on the new one so it matches?
0
 
Lori99Connect With a Mentor Commented:
NO, do not restore master.

Then you will need to link this user to the user in your hhdir database.  Use the command I mentioned before.

sp_change_users_login 'Update_One', 'webuser', 'webuser'

UserName = the name of the user in the current database
LoginName = the name of a SQL Server login

0
 
gupshupAuthor Commented:
why am i making this the name of a sql server login, i dont understand why you want me to do that....
so i would do

sp_change_users_login 'update_one', 'webuser', 'newwebuser'

newwebuser will be the name of the sql login? why do this?
0
 
Lori99Commented:
This command does not actually change the users login.  It maps the SQL Server login to the database login.  You have the ids out of sync because you did a restore to a different server.  This will fix that.

See this link for more information.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_ca-cz_8qzy.asp

sp_change_users_login 'Update_One', 'UserName', 'LoginName'

UserName should be the name of the user in the current database
LoginName shouldbe  the name of the SQL Server login

I will be leaving for the day soon, so I hope this works for you.

0
 
arbertCommented:
Hmmm, this question seems to be going towards the exact same subject as this one:

http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_20954214.html#10826948

Now I remember why I was apprehensive in the past about answering your questions.
0
 
gupshupAuthor Commented:
the current solution  i did was to right click on the server name go to properties and check the option where it says allow modifications to system .... i think its on the general tab but not sure, i dont have sql server on this pc to check...however by checking this option it allowed to drop an user  id.....i know this is probably not the correct way however i am going to continue to try some other solutions becasue i have this problem other places also...
0
 
gupshupAuthor Commented:
Now that I am at a server machine I exactly did:

i checked the box under the server settings tab that reads 'allow modifications to be made directly to the system catelogs'  
I was then able to delete the webuser id and recreate it however this is probably not the correct method.  This problem is going to arise with other id's also.  I will try the advice given again.

0
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.

All Courses

From novice to tech pro — start learning today.