Looking for script to for login creations and assigning permissions.

Question

Hi Experts,

Does anybody has a script or stored procedure which takes care of login creation, user creation and adding roles and permissions.

Basically, looking for script which does the following :
We will pass loginame, login type, db name, db_rolename as 4 parameters to stored procedure/sql script.
It should check for existence of the login, if login doesn't exists, then create a windows login or a sql login based on the login type.
Also, based on the db name which is passed as parameter or input value, it should go ahead and create the user in that database and assign the db_role passed as parameter.

Login type valid values :
WINDOWS_LOGIN, SQL_LOGIN

Thanks in advance.
Sam

Answer 1

create or alter proc dbo.usp_login_creation
@login varchar(100),
@login_type varchar(20),
@dbname varchar(50),
@dbrole varchar(50)
as
begin
BEGIN TRY
    IF (SUSER_ID(@login) IS NOT NULL)
    BEGIN
        PRINT ' LOGIN ALREADY EXISTS'
        RETURN
    END
    ELSE
    BEGIN
        DECLARE @sqlcmd VARCHAR(MAX);
        SET @sqlcmd = '
            USE [master];
            CREATE LOGIN ' + QUOTENAME(@login) + 
            CASE WHEN @login_type = 'Windows' THEN ' FROM WINDOWS' 
                ELSE 'WITH PASSWORD=N''SunRise#123''' END + 
            ' WITH DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=ON
        ';

        EXEC(@sqlcmd);

        SET @sqlcmd = '
            USE ' + QUOTENAME(@dbname) + ';
            CREATE USER '+ QUOTENAME(@login) +' FOR LOGIN '+ QUOTENAME(@login) +';
            ALTER ROLE [db_owner] ADD MEMBER ' + QUOTENAME(@login) +';
            ';
        EXEC(@sqlcmd);
    END
END TRY
BEGIN CATCH
    THROW;
END CATCH
END

Answer 2

Here is what I am looking for.

alter proc usp_login_creation
@NinjaTech varchar(100),
@NinjaTech _type varchar(20),
@dbname varchar(50),
@dbrole varchar(50)
as
begin
BEGIN TRY

-- if he is a Windows login do below
IF (@NinjaTech _type = 'WINDOWS_LOGIN')
BEGIN
IF (SUSER_ID(@NinjaTech ) IS NULL)
begin
PRINT 'CREATE LOGIN '
end
ELSE
begin
PRINT 'LOGIN ALREADY EXISTS'
end
END

-- if he is a sql authentication user do below
IF (@NinjaTech _type = 'SQL_LOGIN')
BEGIN
IF (SUSER_ID(@NinjaTech ) IS NULL)
begin
PRINT 'CREATE LOGIN '
end
ELSE
begin
PRINT 'LOGIN ALREADY EXISTS'
end
END

END TRY
BEGIN CATCH
--EXEC dbo.spErrorHandling
print 'Error...'
END CATCH
end
go

CASE 1:
Now , if I want to create a Windows Authentication Login , I want to pass below parameters and want to execute below TSQL stmts dynamically, thats where I am stuck. Can anybody help?
--Run 1
declare @v_login varchar(100)
declare @v_loginType varchar(20)
declare @v_dbname varchar(50);
declare @v_dbrole varchar(50);
set @v_login = '[ABCD-CORP\Adam]'
set @v_loginType = 'WINDOWS_LOGIN'
set @v_dbname = 'SQLDBA_utils'
set @v_dbrole = 'db_owner'
EXEC usp_login_creation @v_login,@v_loginType,@v_dbname,@v_dbrole
go

--- Below code I want to execute dynamically in a variable or so and embed the same inside above stored proc inside the create WINDOWS_LOGIN block. How do it???
/*
---- create windows
USE [master]
GO
CREATE LOGIN [ABCD-CORP\Adam] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
GO
USE [SQLDBA_utils]
GO
CREATE USER [ABCD-CORP\Adam] FOR LOGIN [ABCD-CORP\Adam]
GO
USE [SQLDBA_utils]
GO
ALTER ROLE [db_owner] ADD MEMBER [ABCD-CORP\Adam]
GO
*/

CASE 2: -- if it is a sql authentication login , i want to execute below code
--Run2 :

-- SQL LOGIN CREATION

declare @v_login varchar(100)
declare @v_loginType varchar(20)
set @v_login = 'test'
set @v_loginType = 'SQL_LOGIN'
set @v_dbname = 'SQLDBA_utils'
set @v_dbrole = 'db_owner'
EXEC usp_login_creation @v_login,@v_loginType,@v_dbname,@v_dbrole
go

-- For sql authentication login , I want to execute below as dynamic sql. How to do it?
USE [master]
GO
CREATE LOGIN [Smith] WITH PASSWORD=N'SunRise#123', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=ON
GO
USE [SQLDBA_utils]
GO
CREATE USER [Smith] FOR LOGIN [Smith]
GO
USE [SQLDBA_utils]
GO
ALTER ROLE [db_owner] ADD MEMBER [Smith]
GO

Thanks.
Sam

Answer 3

What is the purpose of this proc? Are you constantly adding users?

You want users to be in an AD group and you manage rights to the AD group in SQL Server, NOT individual users. Then you manage who is in the group in AD.

Answer 4

Any help on how below script can be dynamically executed within the above stored proc?
I am stuck on how to dynamically to changing the database context, creating a login , db user and assigning a role.

USE [master]
GO
CREATE LOGIN [Smith] WITH PASSWORD=N'SunRise#123', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=ON
GO
USE [SQLDBA_utils]
GO
CREATE USER [Smith] FOR LOGIN [Smith]
GO
USE [SQLDBA_utils]
GO
ALTER ROLE [db_owner] ADD MEMBER [Smith]
GO