Please start any new threads on our new site at We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Administration (2000)
 cannot revoke dbaccess because user own object
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Starting Member

2 Posts

Posted - 03/20/2007 :  11:35:04  Show Profile  Reply with Quote

first I should appologize for posting my question into this server admin forum while I'm actually a newbie in sql server administration.... but I some how keep on getting involved way to deep into the sql server for our current project. And that's because we have some problems with another application which uses the MS desktop sql server 2000.
Okay I will spare you the complete story of how the problems began, but we suspect it is one database user (called TD) blocking the database because it owns some objects in this database. We only want the sa to be the user and db_owner of that specific database.
Now I've tried removing this TD user by executing the following stored procedure (with OSQL):

EXEC sp_revokedbaccess @name_in_db='TD'

Then I get the following warning:

Msg 15183, Level 16, State 1, Server DECO, Procedure sp_MScheck_uid_owns_any
thing, Line 17
The user owns objects in the database and cannot be dropped.

with a list like this (I guess these are the actual objects owned by TD??):




My question: is there a method to make the sa the owner of those objects and remove the user TD??

I hope that someone can help with this!

Thanks in advance,



Aged Yak Warrior

771 Posts

Posted - 03/20/2007 :  11:39:26  Show Profile  Reply with Quote
I would first check to see if the offending object is even being used by renaming it, and seeing what breaks. If it is beingf used, look up sp_changeobjectowner is BOL.
Go to Top of Page

Starting Member

2 Posts

Posted - 03/21/2007 :  05:09:12  Show Profile  Reply with Quote
Thanks for your answer
I've been looking into the object-owner-thing of the database but I somehow wasn't able to discover anything useful. However by coincidence I discovered the actual problem was that this 'td' user was no longer a user of the 'model' database. After adding the user all problems were solved!

Why is it that it's made so impossibly difficult to manage sql servers and databases with all the hidden options and stored procedures (even undocumented!)
But hey everything works for now and I'm happy!

Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.04 seconds. Powered By: Snitz Forums 2000