Unable to delete all database users

[TheBugSlayer]

Hi all. I have a script that's supposed to delete a number of users from all databases. But for some reason, it complains about

Msg 15151, Level 16, State 1, Line 1
Cannot drop the user 'UserXYZ', because it does not exist or you do not have permission.


User UserXYZ does exist, AND I am a sysadmin. The script is the modified version of one that I found online last week and it follows:

[b]
set nocount on
declare @dbname as varchar(80)
declare @server_name as varchar(20)
select @server_name = @@servername
declare rs_cursor CURSOR for select name from master.dbo.sysdatabases 
where name not in ('model','master','msdb','tempdb','alert_db','mssecurity')
order by name

open rs_cursor 
Fetch next from rs_cursor into @dbname
IF @@FETCH_STATUS <> 0 
   PRINT '...'
WHILE @@FETCH_STATUS = 0
BEGIN
	PRINT 'USE ' + @dbname
        EXEC('USE ' + @dbname)
	DROP USER UserABC
	...
        DROP USER UserXYZ
	FETCH NEXT FROM rs_cursor INTO @dbname
END
CLOSE rs_cursor
deallocate rs_cursor
[/b]

As for the risks of deleting all users, this is a clone of a development server that's going out of phase. I had proceed in the wrong order so now I just need to remove all users, create them at the server level with their default databases and let SQL take care of the SIDs, etc. 88 user databases, too tedius to do it manually.

Thanks for your help. This is a bit time-sensitive.

MCP SQL Server 2000, MCTS SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA)

 

[SQLBill]

It could be an orphaned login. The login might be in the database (database>Security>Users) but it might not have access via the server (Security>Logins). Quick check, open the GUI, go to Security>Logins. Find the login, open it and check the user mapping. Has it been granted access (is the box checked for the database)? If not, then it really doesn't have access to the database and can't be dropped.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[gmmastros]

There is a test for Orphaned Database Users in SQLCop. I would encourage you to download it

http://sqlcop.lessthandot.com

Log in to your database, expand "Configuration" and then click on "Orphaned Users". SQLCop will list the orphaned users and display a blog that shows how to fix the problem.



-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[TheBugSlayer]

Bill, users were configured properly. Both at the server and db levels.

George, I have had SQLCop for a while now...

I figured out what was happening. If the user was a db_owner you could not drop him. Obviously ownership had to be transferred to another principal prior.

Thank you both!

MCP SQL Server 2000, MCTS SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA)