Feeds:
Posts
Comments

Posts Tagged ‘Bulk SQL users’

Sometimes, we have a request to create few hundred users in sql server specially at the time of new deployment.
The first solution is we can create users one by one, but it takes some time to create in the SQL Server.
The second solution is using Microsoft Excel. In this case, we need to enter all the users, its password and related information in excel sheet, and using this script we can create the sql script from this excel sheet.

To create SQL Authentication users in SQL, please enter your data in this excel sheet format (kindly do not change the header name) :
Download excel sheet for SQL.

To create Windows Authentication users in SQL, please enter your data in this excel sheet format (kindly do not change the header name) :
Download excel sheet for Windows.

Given below is the script with examples:

CREATE PROCEDURE CREATE_MULTIPLE_LOGIN_PROC
@Excel_File_Name_Path VARCHAR(MAX), 
@Excel_Sheet_Name VARCHAR(MAX),
@Authentication_Type VARCHAR(MAX)
AS

DECLARE @SQL VARCHAR(MAX)

SET @SQL=''
SET @Excel_Sheet_Name =QUOTENAME (@Excel_Sheet_Name + '$' )--Add "$" to the excel sheet name to make it accessable.
SET @Excel_File_Name_Path='''Excel 4.0;Database=' + @Excel_File_Name_Path + '' 

CREATE TABLE #temp_Data
(
    [SNO]	 INT,
    [SCRIPT] NVARCHAR(MAX)
)
IF @Authentication_Type ='SQL' -- If Authentication Type is SQL
Begin
		SET @SQL = 'INSERT INTO #temp_Data ' + 'SELECT  [SNO],'+ '''CREATE LOGIN '' 
        + QUOTENAME([LOGIN NAME]) 
		+ '' WITH PASSWORD=N''''''+ [PASSWORD] + '''''' 
		,CHECK_EXPIRATION=''+[CHECK_EXPIRATION]+ ''
		,CHECK_POLICY=''+[CHECK_POLICY]+ ''
		,DEFAULT_DATABASE=''+QUOTENAME([DEFAULT_DATABASE])+ ''
		,DEFAULT_LANGUAGE=''+QUOTENAME([DEFAULT_LANGUAGE])+ '''''
		+ ' AS [DATA] FROM OPENROWSET(''Microsoft.Jet.OLEDB.4.0'', ' + @Excel_File_Name_Path + ''', ' +
					  '''SELECT * FROM '+@Excel_Sheet_Name + ''')'
		Print @SQL
		EXEC(@SQL)

		SET @SQL = ''
		SET @SQL = 'INSERT INTO #temp_Data Select [SNO]
		,''EXEC sys.sp_addsrvrolemember @loginame =N''''''+ [LOGIN NAME] + ''''''
        , @rolename =N''''''+[FieldValue] + ''''''''
		FROM (
		Select [SNO],
		[LOGIN NAME], 
		CONVERT(NVARCHAR(Max),[SERVER ROLE 1]) as [SERVER ROLE 1]  ,
		CONVERT(NVARCHAR(Max),[SERVER ROLE 2]) as [SERVER ROLE 2]  ,
		CONVERT(NVARCHAR(Max),[SERVER ROLE 3]) as [SERVER ROLE 3]  ,
		CONVERT(NVARCHAR(Max),[SERVER ROLE 4]) as [SERVER ROLE 4]  ,
		CONVERT(NVARCHAR(Max),[SERVER ROLE 5]) as [SERVER ROLE 5]  ,
		CONVERT(NVARCHAR(Max),[SERVER ROLE 6]) as [SERVER ROLE 6]  ,
		CONVERT(NVARCHAR(Max),[SERVER ROLE 7]) as [SERVER ROLE 7]  ,
		CONVERT(NVARCHAR(Max),[SERVER ROLE 8]) as [SERVER ROLE 8]  ,
		CONVERT(NVARCHAR(Max),[SERVER ROLE 9]) as [SERVER ROLE 9]  ,
		CONVERT(NVARCHAR(Max),[SERVER ROLE 10]) as [SERVER ROLE 10]
		FROM OPENROWSET(''Microsoft.Jet.OLEDB.4.0'', ' + @Excel_File_Name_Path + ''', ' +
					  '''SELECT * FROM '+@Excel_Sheet_Name + '''))Main 
		UNPIVOT(FieldValue  FOR FieldName IN (
		[SERVER ROLE 1] ,
		[SERVER ROLE 2] ,
		[SERVER ROLE 3] ,
		[SERVER ROLE 4] , 
		[SERVER ROLE 5] , 
		[SERVER ROLE 6] , 
		[SERVER ROLE 7] ,
		[SERVER ROLE 8] , 
		[SERVER ROLE 9] , 
		[SERVER ROLE 10] 
		)
		)Sup'
		Print @SQL
		EXEC(@SQL)
END
ELSE IF @Authentication_Type ='Windows'
BEGIN
	---Create 
		SET @SQL = 'INSERT INTO #temp_Data ' + 'SELECT  [SNO],'+ '''CREATE LOGIN '' 
        + QUOTENAME([LOGIN NAME]) + ''
		FROM WINDOWS WITH DEFAULT_DATABASE=''+QUOTENAME([DEFAULT_DATABASE])+ ''
		, DEFAULT_LANGUAGE=''+QUOTENAME([DEFAULT_LANGUAGE])+ '''''
		+ ' AS [DATA] FROM OPENROWSET(''Microsoft.Jet.OLEDB.4.0'', 
       ' + @Excel_File_Name_Path + ''', ' +
	   '''SELECT * FROM '+@Excel_Sheet_Name + ''')'
		Print @SQL
		EXEC(@SQL)
		SET @SQL = ''
END

Select [SCRIPT] from #temp_Data Order By [SNO],[SCRIPT]
GO
--Syntax 
--CREATE_MULTIPLE_LOGIN_PROC 'Excel Sheet Path','Sheet Name (By Default it is sheet1)','Authentication Type'
--Example 1 :
CREATE_MULTIPLE_LOGIN_PROC 'E:\login_list-for-sql.xls','Sheet1','SQL' -- To Create SQL users
GO
--Result

--Example 2 :
CREATE_MULTIPLE_LOGIN_PROC 'E:\login_list-for-Windows.xls','Sheet1','Windows' -- To Create Windows users
--Result

I welcome your valuable feedback/input.

Read Full Post »