cannot revoke dbaccess because user own object

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

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

cannot revoke dbaccess because user own object

Author

Topic

RensV
Starting Member

2 Posts

Posted - 2007-03-20 : 11:35:04

Hi,

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??):

projects_PrimaryKey

K
SEQUENCES

U
sequences_PrimaryKey etc

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,

Regards

Rens

mcrowley
Aged Yak Warrior

771 Posts

Posted - 2007-03-20 : 11:39:26

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.

RensV
Starting Member

2 Posts

Posted - 2007-03-21 : 05:09:12

hello,
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!

Rens

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources