Solved

dropping a user

Posted on 2004-04-14
18
2,077 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
 
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
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 

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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
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.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

762 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now