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.
Add a comment
|
2 Answers
You either need to change the context to that database or dynamically go there:
EXEC j_test..sp_executesql N'DROP USER test_user;';
Comments
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
