Create Database User

/*********************************************************************************************************************************

    Created ON :: April 27, 2011
    Created By :: Vinay Kumar
    Decription :: This script is used for drop and create a new or existing user.
                 IF User is already exists in database then first it will dropped and then recreate.
    
    Parameter Information:- BEFORE EXECUTE SCRIPT, PLEASE CHANGE THE VALUE OF FOLLOWING ...
    
                     @NewUser_Name        :    'Put the new user name here'
                     @NewUser_Password    :    'Put the password for new user'
                     @DatabaseName        :    'Put the database name, where you want to create user'
                     @NewSchema_Name    :    'Put the new schema name'
     
     Note :- IF @NewSchema_Name not provided or @NewSchema_Name is null then default schema is dbo.
     
     
     
EXEC [dbo].[CreateDatabaseUser]
@NewUser_Name = N'newuser',
@NewUser_Password = N'123',
@DatabaseName = N'userdatabase',
@NewSchema_Name ='newschema'


*********************************************************************************************************************************/
IF EXISTS (SELECT 1 FROM sysobjects WHERE name=N'CreateDatabaseUser' AND xtype='P')
DROP PROCEDURE [dbo].[CreateDatabaseUser]
GO

CREATE PROCEDURE [dbo].[CreateDatabaseUser]
@NewUser_Name NVARCHAR(max) = NULL,
@NewUser_Password NVARCHAR(max)= NULL,
@DatabaseName NVARCHAR(max)= NULL,
@NewSchema_Name NVARCHAR(max)= NULL
AS

SET NOCOUNT ON

DECLARE @Error_Message NVARCHAR(max)
DECLARE @SqlString NVARCHAR(max)
DECLARE @UserID int
DECLARE @TotalRow int
DECLARE @Count int
DECLARE @LoginCount int
DECLARE @TB_SchemaName Nvarchar(max)
DECLARE @AuthorizationString NVARCHAR(max)
DECLARE @RE_AuthorizationString NVARCHAR(max)

SET @SqlString=''
SET @UserID=0
SET @TotalRow=0
SET @Count=1
SET @LoginCount=1
SET @TB_SchemaName=''
SET @Error_Message=''
SET @AuthorizationString=''
SET @RE_AuthorizationString=''


SELECT @LoginCount=COUNT(*) from master.sys.sysprocesses where loginame=@NewUser_Name

IF (@LoginCount>0)
BEGIN
    RAISERROR('User is currently login. Please first close all user session(s).',16,1)
    RETURN
END

Create Table ##UserOwnerInformation
(
    [Count] int,
    UserID int,
    UserName nvarchar(max),
    SchemaID int,
    schemaName nvarchar(max)
)

SET @SqlString=N'SELECT @UserID_OUT=principal_id FROM '+@DatabaseName+'.sys.database_principals where name='''+@NewUser_Name+''''

EXEC SP_EXECUTESQL @SqlString,N'@UserID_OUT INT OUTPUT',@UserID_OUT=@UserID OUTPUT


SET @SqlString=N'
insert into ##UserOwnerInformation
SELECT ROW_NUMBER () Over(Order by UserID) AS [Count], UserID, UserName, SchemaID, SchemaName FROM(
SELECT Principal_id as UserID,USER_NAME(principal_id)as UserName,[Schema_ID] as SchemaID,name as SchemaName from '+@DatabaseName+'.sys.schemas
where Principal_id = ('+convert(varchar,@UserID)+')) as Tab_vari

'
EXEC (@SqlString)

SELECT @TotalRow = COUNT(*) FROM ##UserOwnerInformation

WHILE (@Count <= @TotalRow)
BEGIN
    SELECT @TB_SchemaName=SchemaName FROM ##UserOwnerInformation WHERE [Count] = @Count
    SET @AuthorizationString=@AuthorizationString+'ALTER AUTHORIZATION ON SCHEMA::['+@TB_SchemaName+'] TO [dbo]
    '
    SET @RE_AuthorizationString=@RE_AuthorizationString+'ALTER AUTHORIZATION ON SCHEMA::['+@TB_SchemaName+'] TO ['+@NewUser_Name+']
    '
    SET @Count= @Count+1    
END


SET @SqlString=N'
-- Drop User in database
USE ['+@DatabaseName+']

IF EXISTS (select * from '+@DatabaseName+'.sys.database_principals where name=N'''+@NewUser_Name+''')
BEGIN
    '+@AuthorizationString+'Drop USER ['+@NewUser_Name+'] 
    Print ''User Dropped From '+@DatabaseName+'''
END

'

--------


--- ========================= Check the input parameter values ========================= ---
IF NOT EXISTS(SELECT 1 FROM master.sys.sysDatabases where master.sys.sysdatabases.name=@DatabaseName)
BEGIN
    SET @Error_Message='Database doesn''t exists. Please check the database name.'    
    RAISERROR('Database doesn''t exists. Please check the database name.',16,1)
    RETURN
END

IF ((LEN(@NewUser_Name)<1 OR @NewUser_Name IS NULL) AND @Error_Message='')
BEGIN
    SET @Error_Message='Please check the @NewUser_Name parameter value.'    
END

IF ((LEN(@NewUser_Password)<1 OR @NewUser_Password IS NULL) AND @Error_Message='')
BEGIN
    SET @Error_Message='Please check the @NewUser_Password parameter value.'    
END

IF ((LEN(@DatabaseName)<1 OR @DatabaseName IS NULL) AND @Error_Message='')
BEGIN
    SET @Error_Message='Please check the @DatabaseName parameter value.'    
END

IF ((LEN(@NewSchema_Name)<1 OR @NewSchema_Name IS NULL) AND @Error_Message='')
BEGIN
    SET @NewSchema_Name='dbo'    
END

IF (@Error_Message!='')
BEGIN
    RAISERROR(@Error_Message,16,1)
    RETURN
END


-- Drop User in database
SET @SqlString=N'
USE ['+@DatabaseName+']

IF EXISTS (select * from '+@DatabaseName+'.sys.database_principals where name=N'''+@NewUser_Name+''')
BEGIN
    '+@AuthorizationString+'Drop USER ['+@NewUser_Name+']
    Print ''User Dropped From '+@DatabaseName+'''
END

'

-- Check the Login's Existence
SET @SqlString=@SqlString+N'
USE MASTER

IF EXISTS (SELECT 1 from MASTER.sys.server_principals where name =N'''+@NewUser_Name+''')
Begin
    Drop login ['+@NewUser_Name+']
    Print ''Login Dropped From Server''
END

'
--Print @SqlString
EXEC (@SqlString)

--- Create the Login if not exits
SET @SqlString=N'
USE MASTER

IF NOT EXISTS (select * from MASTER.sys.server_principals where name=N'''+@NewUser_Name+''')
BEGIN
    Create Login ['+@NewUser_Name+'] with password ='''+@NewUser_Password+''',
    Default_Database=[Master],
    Default_Language=[Us_English],
    Check_Expiration=off,
    Check_Policy=OFF
    Print ''Login Created on Server''
END

'

-- Enable the newly created Login 
SET @SqlString=@SqlString+N'
USE MASTER

IF EXISTS (select 1 from MASTER.sys.server_principals where name =N'''+@NewUser_Name+''')
BEGIN
    Alter login ['+@NewUser_Name+'] ENABLE
    Print ''Login Enabled''
END

'

--- Use The Database Where we want the relationship.
--- Check the schema, which we want to bind with login user. if not exists then create schema.
SET @SqlString=@SqlString+N'
USE ['+@DatabaseName+']

IF NOT EXISTS (Select * from '+@DatabaseName+'.sys.schemas where name=N'''+@NewSchema_Name+''')
BEGIN
    EXEC SYS.sp_Executesql N''Create Schema ['+@NewSchema_Name+']''
    print ''New Schema Created''
END

'
-- Create User in database
SET @SqlString=@SqlString+N'
USE ['+@DatabaseName+']

IF NOT EXISTS (select * from '+@DatabaseName+'.sys.database_principals where name=N'''+@NewUser_Name+''')
BEGIN
    Create USER ['+@NewUser_Name+'] FOR Login ['+@NewUser_Name+']
    '+@RE_AuthorizationString+'
    
    IF EXISTS (Select * from '+@DatabaseName+'.sys.schemas where name=N'''+@NewSchema_Name+''')
    BEGIN
        ALTER USER ['+@NewUser_Name+'] WITH DEFAULT_SCHEMA=['+@NewSchema_Name+']
        ALTER AUTHORIZATION ON SCHEMA::['+@NewSchema_Name+'] TO ['+@NewUser_Name+']
    END
    Print ''User Created For '+@DatabaseName+'''
END

'
--- Give Permission to user on database
SET @SqlString=@SqlString+N'
USE ['+@DatabaseName+']

IF EXISTS (select * from '+@DatabaseName+'.sys.database_principals where name=N'''+@NewUser_Name+''')
BEGIN
    GRANT CREATE FUNCTION TO ['+@NewUser_Name+']
    GRANT CREATE PROCEDURE TO ['+@NewUser_Name+']
    GRANT CREATE TABLE TO ['+@NewUser_Name+']
    GRANT CREATE VIEW TO ['+@NewUser_Name+']
END

'

-- Privide Rights on Newly Created Schema on Database
SET @SqlString=@SqlString+N'
USE ['+@DatabaseName+']

IF EXISTS (select * from '+@DatabaseName+'.sys.schemas where name ='''+@NewSchema_Name+''')
BEGIN
    GRANT ALTER ON SCHEMA::['+@NewSchema_Name+'] TO ['+@NewUser_Name+']
    GRANT CONTROL ON SCHEMA::['+@NewSchema_Name+'] TO ['+@NewUser_Name+']
    GRANT DELETE ON SCHEMA::['+@NewSchema_Name+'] TO ['+@NewUser_Name+']
    GRANT EXECUTE ON SCHEMA::['+@NewSchema_Name+'] TO ['+@NewUser_Name+']
    GRANT INSERT ON SCHEMA::['+@NewSchema_Name+'] TO ['+@NewUser_Name+']
    GRANT REFERENCES ON SCHEMA::['+@NewSchema_Name+'] TO ['+@NewUser_Name+']
    GRANT SELECT ON SCHEMA::['+@NewSchema_Name+'] TO ['+@NewUser_Name+']
    GRANT TAKE OWNERSHIP ON SCHEMA::['+@NewSchema_Name+'] TO ['+@NewUser_Name+']
    GRANT UPDATE ON SCHEMA::['+@NewSchema_Name+'] TO ['+@NewUser_Name+']
    GRANT VIEW CHANGE TRACKING ON SCHEMA::['+@NewSchema_Name+'] TO ['+@NewUser_Name+']
    GRANT VIEW DEFINITION ON SCHEMA::['+@NewSchema_Name+'] TO ['+@NewUser_Name+']
END

'
--- Auto Fix the user 
SET @SqlString=@SqlString+N'
--- Auto Fix the user 
USE ['+@DatabaseName+']

EXEC sp_change_users_login ''Auto_Fix'', '''+@NewUser_Name+''', NULL, '''+@NewUser_Password+''''

--Print @SqlString
EXEC (@SqlString)

Print '
---- New User ['+@NewUser_Name+'] Created successfully. ---'

DROP TABLE ##UserOwnerInformation

SET NOCOUNT OFF
GO