Solved

dropping a user

Posted on 2004-04-14
18
2,124 Views
Last Modified: 2007-12-19
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.
0
Comment
Question by:gupshup
  • 10
  • 6
  • 2
18 Comments
 
LVL 34

Accepted Solution

by:
arbert earned 60 total points
ID: 10826875
webuser owns objects in the database--you can't drop the user until you change the owner on the object.....
0
 
LVL 7

Expert Comment

by:Lori99
ID: 10826942
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
 

Author Comment

by:gupshup
ID: 10826999
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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 
LVL 7

Expert Comment

by:Lori99
ID: 10827085
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
 

Author Comment

by:gupshup
ID: 10827114
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
 
LVL 7

Expert Comment

by:Lori99
ID: 10827278
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
 

Author Comment

by:gupshup
ID: 10827308
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
 
LVL 7

Assisted Solution

by:Lori99
Lori99 earned 40 total points
ID: 10827392
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
 

Author Comment

by:gupshup
ID: 10827448
waht would i put for the object name.....my database name is hhdir
0
 

Author Comment

by:gupshup
ID: 10827495
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
 

Author Comment

by:gupshup
ID: 10827531
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
 

Author Comment

by:gupshup
ID: 10827547
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
 
LVL 7

Assisted Solution

by:Lori99
Lori99 earned 40 total points
ID: 10827561
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
 

Author Comment

by:gupshup
ID: 10827624
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
 
LVL 7

Expert Comment

by:Lori99
ID: 10827673
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
 
LVL 34

Expert Comment

by:arbert
ID: 10827980
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
 

Author Comment

by:gupshup
ID: 10829729
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
 

Author Comment

by:gupshup
ID: 10832686
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

Featured Post

Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

Question has a verified solution.

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

Suggested Solutions

I have a large data set and a SSIS package. How can I load this file in multi threading?
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

730 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