http://www.wisdom-soup.com/blog/security-audit/how-to-remove-sql-server-database-users/
Database security is one of the significant concerns for most DBAs. DBAs frequently restore or backup the database, this is a very common scenario, But the thing is after successfully restoring a new version of your database, you want to remove the current users. Probably you thought of just expanding the user node and deleting the desire user; in that sense you are somewhat correct. But if you face an error like:
Msg 15421, Level 16, State 1, Line 1
The database principal owns a database role and cannot be dropped.
1 |
EXEC sp_dropuser '<USER_NAME>' |
3 |
IF EXISTS (SELECT * FROM sys.server_principals WHERE name = N'<USER_NAME>') |
4 |
DROP LOGIN '<USER_NAME>' |
6 |
EXEC sp_droplogin '<USER_NAME>' |
References:
- http://www.codeproject.com/Articles/66827/Few-Step-s-to-Remove-SQL-Server-Database-User-s
- http://msdn.microsoft.com/en-us/library/aa933285%28v=sql.80%29.aspx
