Drop a user who owns the DB - SQL Server Forums

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)

Drop a user who owns the DB

Author

Topic

CanadaDBA

583 Posts

Posted - 2007-09-10 : 12:53:26

I restored a DB on another machine. The DB has some users which are owner as well. I want to drop the users but can't because they own the database. I tried to change the db owner but

EXEC sp_changedbowner 'dbo'

didn't work and says "The login 'dbo' does not exist".

1. Can I prevent the users to be restored with restoring a DB?

2. How can I drop these users?

Thanks,

Canada DBA

X002548
Not Just a Number

15586 Posts

Posted - 2007-09-10 : 14:07:56

If you restored the db from a different server, you will probably need to fix the logins


SET QUOTED_IDENTIFIER OFF 
GO 

DECLARE @SQL varchar(100) 

DECLARE curSQL CURSOR FOR 
select "exec sp_change_users_login 'AUTO_FIX','" + name + "'" 
from sysusers 
where issqluser = 1 and name NOT IN ('dbo', 'guest') 

OPEN curSQL 

FETCH curSQL into @SQL 

WHILE @@FETCH_STATUS = 0 
BEGIN 
EXEC (@SQL) 
SELECT @SQL
FETCH curSQL into @SQL 
END 

CLOSE curSQL 
DEALLOCATE curSQL 
go

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

CanadaDBA

583 Posts

Posted - 2007-09-10 : 14:47:48

Hi Brett!

I get the following error for each user in the database:
Terminating this procedure. The Action 'AUTO_FIX' is incompatible with the other parameter values ('USER1', '(null)').

Note that I have only SA and Builtin\Administrators logins in my new server.

Canada DBA

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2007-09-10 : 16:23:38

sp_changedbowner 'sa'

dbo is mapped to sa by default

--------------------
keeping it simple...

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-09-10 : 22:48:56

Use sp_dropuser to remove unneeded db users.

CanadaDBA

583 Posts

Posted - 2007-09-11 : 08:49:49

The user owns objects in the database and cannot be dropped.

quote:
Originally posted by jen

sp_changedbowner 'sa'

dbo is mapped to sa by default

--------------------
keeping it simple...

Canada DBA

CanadaDBA

583 Posts

Posted - 2007-09-11 : 08:54:38

sp_changedbowner 'sa' worked. But still I get error that the user owns objects and cannot be dropped. How can I find which objects a user owns?

Canada DBA

CanadaDBA

583 Posts

Posted - 2007-09-11 : 09:29:01

I found my answer to previous post. For record, it is:

exec sp_msforeachdb 'select name from sysobjects where uid = user_id( ''user_name'') '

So, that solves my problem. Now, I feel I am getting far from my need.. What was my issue? I am creating a new Test server. I have all old Test backups and restored in the new server. The problem is that when I was going to create the same users on this machine, I couldn't because the users already existed in the databases that I had restored. How can I create same situation as my original server.

Note: Please give other solution than copy physical SQL files from one compute to the other.

Canada DBA

CanadaDBA

583 Posts

Posted - 2007-09-11 : 10:05:33

At the time I ran the script, I had only SA and UILTIN\Administrator logins. Now I have created the users and when ran the script, it worked and generated the right SQL scripts and by running that it seems the problem is fixed.

To be sure, I am going to drop all the DBs and user logins and then:

1. Create User logins same as what I had in old server.
2. Restore the databases.

Any idea? I'll post the results I get.

quote:
Originally posted by CanadaDBA

Hi Brett!

I get the following error for each user in the database:
Terminating this procedure. The Action 'AUTO_FIX' is incompatible with the other parameter values ('USER1', '(null)').

Note that I have only SA and Builtin\Administrators logins in my new server.

Canada DBA

Canada DBA

CanadaDBA

583 Posts

Posted - 2007-09-11 : 12:41:55

For record:
I was going to create a new test environment. Only some of the databases and users from old server were supposed to be on the new server. Here are the steps I took:

0. Installed SQL Server 2000 and SP4

1. Using DTS package transferred logins from old server to the new one

2. Restored a database and ran the following script


SET QUOTED_IDENTIFIER OFF 
GO 

DECLARE @SQL varchar(100) 

DECLARE curSQL CURSOR FOR 
	select "exec sp_change_users_login 'AUTO_FIX','" + name + "'" 
		from sysusers 
		where issqluser = 1 and name NOT IN ('dbo', 'guest') 

OPEN curSQL 

FETCH curSQL into @SQL 

WHILE @@FETCH_STATUS = 0 
	BEGIN 
		EXEC (@SQL) 
		SELECT @SQL
		FETCH curSQL into @SQL 
	END 

CLOSE curSQL 
DEALLOCATE curSQL 
go

3. Repeated step 2 for each database

4. I was going to delete the logins I didn’t want on new server but they owned some objects. So, left them to be there.

Canada DBA

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2007-09-11 : 13:18:24

issue
sp_changeobjectowner 'old_owner.objectname','new_owner'

then you can drop those unwanted logins

--------------------
keeping it simple...

CanadaDBA

583 Posts

Posted - 2007-09-11 : 14:10:39

Yes, but in my case I noticed there are two objects (table/view) with same name but different owners. So, I couldn't rename the object to dbo because it already exist.

Canada DBA

Van
Constraint Violating Yak Guru

462 Posts

Posted - 2007-09-11 : 15:16:51

Skimming through theses posts it sounds like you are having issues with orphaned users...they are users in the database but have no login or the SID for the login doesn't match the user. The AutoFix will help with this. You might consider creating an SP that you can run whenever you have to do a restore from a different server that will fix the logins. That way you can do the restore then run the SP and be done with it.

I had to deal with this a few years ago. I had to do PROD restores down to mutiple DEV, TEST, and QA environments. After the first few (fixing the users, permissions, db access, all that manually), I wrote an SP that I could run that did it all for me. I wrote the SP as a way to make it easier and quicker for me as a DBA. The SP would get errors from time to time but it was ok because it was errors about the code not needing to be ran for a user at that particular time...so it still fixed everything. It's kinda funny because sometime later the developers and contract DBAs (Graz included) decided to use my SP in their build process and it would crash their build process. Graz had to code around my SP getting errors. I guess I could have went back and put error handling in but that would be too much of a favor for Graz.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources