Few step(s) to remove SQL Server database user(s)

Database security is one of the significant concerns for most of the DBA. DBAs are frequently restore or backup the database, this is a very common scenario, But the thing is after successfully restore a new version of your database and you want to remove the current users, probably you thought just expand the user node and delete 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.

What will you do?

Microsoft SQL Server provides quite a lot of way to maintain the security of database. This article is not about the security of Microsoft SQL server.

In this article I will try to explain how to resolve the following issues:

  1. The database principal owns a database role and cannot be dropped.
  2. The database principal owns a schema and cannot be dropped.

Solution

I try to categorize into two sections, section-A; we will discuss to find out the list of roles in which the user exists and the section-B; we will discuss on how to resolve it.
Section-A

In this section, our primary goal is to find out the list of existing roles of our target database. For this purpose we use a simple transact-sql with the help of SQL Server SYS.DATABASE_PRINCIPALS table. A sample sql script and the required step(s) are listed below:

  1. Open SQL Server Management Studio and login as an admin user.
  2. Select the database, set the user name & execute the following transact-sql for getting the database role and user detail.    

Read more: Codeproject

Posted via email from jasper22's posterous