Services
Technology Web App Development Mobile App Development Windows App Development Database Development UI/UX Development Cloud Development SEO Digital Marketing Guest Posting Content Writing Artificial Intelligence Data Science ERP Development CRM Development CMS Development LMS Development Business Listing Blockchain Technology Startup Promotion AI Tools Integration
Products
Data Converter Cleaner TUC - Unit Converter Import Export Survey Manager Tweet Controller
Developer
Article Blog Forum Interview Beginner News Startup Quiz
Users
Career
Apply Job Internship
Pricing
Login Sign Up Get a Quote

forum

Home Forums How to drop a user in SQL Server? – MindStick
Ask Question

1 Answers

Ashutosh Patel
Ashutosh Patel 16 Jul 2024
Report Abuse

SQL Server DROP USER

The SQL statement DROP USER allows you to delete a user from an existing database. Here is the syntax of the DROP USER statement

DROP USER [IF EXISTS] user_name;

In the syntax above, don't forget to replace your use name with the user_name after the keywork DROP USER. If the user doesn't exist in the current database then DROP USER statement will fail.

To avoid this, you can use the IF EXISTS option. If the user already exists, the IF EXISTS option conditionally deletes them.

1. Using the DROP USER statement to delete a user

Now, use the MyCollegeDb sample database for the following DROP USER statement example.

First, create a new login testLogin with a password,

CREATE LOGIN testLogin
WITH PASSWORD='UyxIv@12'

Second, create a new user and map it with the login testLogin 

USE MyCollegeDb
GO

CREATE USER MyUser
FOR LOGIN testLogin;

Third, drop the user MyUser from the current database

USE MyCollegeDb
GO
DROP USER IF EXISTS MyUser;

2. Drop a user that owns a securable

First, create a new login called jacob with a password,

USE master
GO
CREATE LOGIN jacob
WITH PASSWORD = 'Jac.B908$#'

Second, use your current database MyCollegeDb and create a new user for the login jacob

USE MyCollegeDb
GO
CREATE USER ami
FOR LOGIN jacob;

Third, create a schema called report and grant authorization to the user ami.

USE MyCollegeDb
GO
CREATE SCHEMA report 
AUTHORIZATION ami;

Fourth, connect to the SQL Server using the login jacob and create a table called daily_sales in the schema report

USE MyCollegeDb
GO

CREATE TABLE report.daily_sales (
Id INT IDENTITY PRIMARY KEY,
CreationDate DATETIME NOT NULL,
Amount DECIMAL(10,2) NOT NULL DEFAULT 0
)

Fifth, switch the connection to the system administrator (sa) account and drop the user ami

USE MyCollegeDb
GO

DROP USER ami;

The SQL server Returns the following error,

Ashutosh Patel

Because the user ami owns the schema report, the DROP USER statement cannot delete it.

To delete user ami, you must first transfer the authorization of the schema report to another user. For example, the following statement changes the authorization of the schema report to user dbo:

USE MyCollegeDb
GO
ALTER AUTHORIZATION 
ON SCHEMA::report 
TO dbo;

If you execute the DROP USER statement to delete the user ami, it will executes successfully,

USE MyCollegeDb
GO
DROP User ami;

Also, Read: Describe the different types roles in SQL Server.

advertising
Markdown for AI

A clean, structured version of this page for AI assistants and LLMs.

Open .md