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'



How to disable foreign key constraint in sql server

Some times we need to delete unwanted data from the tables at that time foreign key will not allow deleting data from the table. Then Use below syntax for disabling/enabling foreign key temporarily
Method 1:-
-- Disable all table constraints

ALTER TABLE TableName NOCHECK CONSTRAINT ALL

-- Enable all table constraints

ALTER TABLE TableName WITH CHECK CHECK CONSTRAINT ALL

-- Disable single constraint

ALTER TABLE TableName NOCHECK CONSTRAINT Constraint

-- Enable single constraint

ALTER TABLE TableName WITH CHECK CHECK CONSTRAINT Constraint

Method 2:-

If you want to disable all constraints in the database just run this code:
-- disable all constraints
EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"

-- enable all constraints

exec sp_msforeachtable @command1="print '?'", @command2="ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"