Friday, June 17, 2016

How to create a new user in SQL Server

Use below syntax to create user login  to sql server database

-- Which database to use.
USE master
GO

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

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

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


-- Add new login.
CREATE LOGIN [samanth] WITH PASSWORD=N'S0meComplexPassword', DEFAULT_DATABASE=master
GO

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

-- Add new user.
CREATE USER [samanth] FOR LOGIN [samanth] WITH DEFAULT_SCHEMA=[dbo]
GO

-- Add to database read / write roles
EXEC sp_addrolemember 'db_datareader', 'samanth'
EXEC sp_addrolemember 'db_datawriter', 'samanth'
GO


-- Add to database owner role? 
-- Only give out if application needs a high level of privileges.
-- EXEC sp_addrolemember 'db_owner', 'samanth'