Wednesday, 5 March 2008

Getting rid of annoying orphaned users on a SQL Server 2005 database

A nice one of those microsoft errors this one!!

Trying to delete an old user on a database which i have restored, but there is a problem, it throws an error, 'Drop failed for User ABC' (Microsoft.SqlServer.Smo)

The database principal owns a schema in the database, and cannot be dropped. (Microsoft SQL Server, Error: 15138)

Oh Joy! Ok after digging around I tried this which returned a list of orphaned users:

select from master..syslogins l right join
sysusers u on l.sid = u.sid
where l.sid is null and issqlrole <> 1 and isapprole <> 1
and ( <> 'INFORMATION_SCHEMA' and <> 'guest'
and <> 'system_function_schema')

With that I can use the exec sp_revokedbaccess 'ABC' - make sure they do not own any of the schemas in the database. You may need to move this onto another user.

No comments: