Solved

dropping a user

Posted on 2004-04-14
18
2,109 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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL LINE CONTINUATION ISSUE 12 31
SSRS 2013 - Overlapping reports 2 19
always on switch back after failover 2 31
Find results from sql within a time span 11 29
Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

813 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

18 Experts available now in Live!

Get 1:1 Help Now