Cannot delete users in SQL 2005?

Hi,

Running multiple SQL 2000 servers and on each can easily go under Enterprise Manager>>ServerXYZ>>Security>>Logins>>Delete and delete user regardless users DB membership etc...now installed SQL 2005 (MS SQL Server Management Studio) and trying to do the same (attached to server running SQL 2000) and I cannot do it. Each time I try it, I am getting:

TITLE: Microsoft SQL Server Management Studio

Drop failed for Login 'testdelete'. (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.3033.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Drop+Login&LinkId=20476

ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

Login 'testdelete' is aliased or mapped to a user in one or more database(s). Drop the user or alias before dropping the login. (Microsoft SQL Server, Error: 15175)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=08.00.0760&EvtSrc=MSSQLServer&EvtID=15175&LinkId=20476

BUTTONS:

OK

If I go and manually remove user from all databases and then try to delete it only then I can delete such user...this is not the solution for me as we have some servers where users can be associated up to different 300+ databases.

I would like to move from 2000 and start using 2005 tools exclusively and this is only reason I cannot.

Any fix, suggestion or solution is appreciated. Thanks.

I believe the error is by default as you can see that user is mapped/aliased to other databases, if you are using linked servers then thsi is expected. As a matter of access can you try revoking the permissions for that user and then drop it.|||

Thanks for your reply.

Yes I know what the message is meaning but what I do not understand is how I can just go to any user setup with access to multiple databases on same server (not linked) and select "Delete" using SQL 2000 Enterprise Manager and it drops it w/o any issues but when I go to the same server and user with same setup under SQL 2005 Server Managment Studio it tells me that user is associated to other databases and it cannot delete it w/o me first revoking all the access just then to drop it (that works)...this is lot more complex and requires extra steps?

|||

The issue is not so much that the login has mapped users, but that the mapped users own something in a database. In SQL Server 2005, we made security enhancements that require every object to be owned by some security principal. A user can no longer simply relinquish ownership. Instead, ownership must be positively taken by some other principal. Once all the users mapped to the login are no longer owners of anything, you should be able to drop the login without error.

This sounds like a pain point for you. If you could file a suggestion to make this easier at http://connect.microsoft.com/sqlserver, that would help. The development team gives additional weight to defect reports and feature requests submitted by customers when we are prioritizing future work.

Hope this helps,
Steve