CODEWITHSUNDEEP
sql-servert-sql
need_the_buzz
need_the_buzz

Reputation: 373

Create User from a different DB in SQL Server

Is there any way I can create a user on a database from the master DB(or any other DB)

IF EXISTS (SELECT 1 FROM   j_test.sys.database_principals WHERE  name = N'test_user') 
  DROP USER j_test.[test_user]; --doesnt't work.

Upvotes: 3

Views: 3228

Answers (2)

CRAFTY DBA
CRAFTY DBA

Reputation: 14915

Logins are done at the server level, I usually create them from the [master] database. Just my convention.

Users are done at the database level. You need to have your context set to that database. The USE command allows you to switch between databases.

This snippet is from my blog that shows a hypothetical database [BSA] with a schema named [STAGE].

-- Which database to use.
USE [master]
GO


-- Delete existing login.
IF  EXISTS (SELECT * FROM sys.server_principals WHERE name = N'BSA_ADMIN')
DROP LOGIN [BSA_ADMIN]
GO

-- Add new login.
CREATE LOGIN [BSA_ADMIN] WITH PASSWORD=N'M0a2r0c9h11$', DEFAULT_DATABASE=[BSA]
GO


-- Which database to use.
USE [BSA]
GO

-- Delete existing user.
IF  EXISTS (SELECT * FROM sys.database_principals WHERE name = N'BSA_ADMIN')
DROP USER [BSA_ADMIN]
GO

-- Add new user.
CREATE USER [BSA_ADMIN] FOR LOGIN [BSA_ADMIN] WITH DEFAULT_SCHEMA=[STAGE]
GO

Upvotes: 0

Aaron Bertrand
Aaron Bertrand

Reputation: 280252

You either need to change the context to that database or dynamically go there:

EXEC j_test..sp_executesql N'DROP USER test_user;';

Upvotes: 8

Related Questions