Looking for script to for login creations and assigning permissions.

Sam 1,476 Reputation points
2021-10-07T06:46:25.64+00:00

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

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,319 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,597 questions
0 comments No comments
{count} votes

Accepted answer
  1. Tom Phillips 17,721 Reputation points
    2021-10-07T17:17:37.177+00:00
    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
    

3 additional answers

Sort by: Most helpful
  1. Sam 1,476 Reputation points
    2021-10-07T10:42:59.383+00:00

    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

    0 comments No comments

  2. Tom Phillips 17,721 Reputation points
    2021-10-07T11:41:44.653+00:00

    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.


  3. Sam 1,476 Reputation points
    2021-10-07T17:01:32.37+00:00

    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

    0 comments No comments