Category Archives: SQL Server Security

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>')

EXEC sp_droplogin '<USER_NAME>'