question

Samanthar-3682 avatar image
0 Votes"
Samanthar-3682 asked Samanthar-3682 commented

Looking for script to for login creations and assigning permissions.

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-generalsql-server-transact-sql
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

TomPhillips-1744 avatar image
0 Votes"
TomPhillips-1744 answered Samanthar-3682 commented
 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
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thanks for the help Tom. It worked like a charm, Many Many thanks for your time and help.

0 Votes 0 ·
Samanthar-3682 avatar image
0 Votes"
Samanthar-3682 answered

Here is what I am looking for.

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

  • if he is a Windows login do below
    IF (@login_type = 'WINDOWS_LOGIN')
    BEGIN
    IF (SUSER_ID(@login) 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 (@login_type = 'SQL_LOGIN')
    BEGIN
    IF (SUSER_ID(@login) 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

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

TomPhillips-1744 avatar image
0 Votes"
TomPhillips-1744 answered Samanthar-3682 commented

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.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi Tom,


The purpose of this proc is to create login, db user and assign roles.
Yes, we need to provide provision of creating both windows authentication user as well as sql authentication user.

0 Votes 0 ·
Samanthar-3682 avatar image
0 Votes"
Samanthar-3682 answered

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

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.