How to: Remove SQL Server Database User(s)

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.

EXEC sp_dropuser '<USER_NAME>'

IF EXISTS (SELECT * FROM sys.server_principals WHERE name = N'<USER_NAME>')
DROP LOGIN '<USER_NAME>'

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