SQL Server – Script – Login

Preface

Need a script that allows me to script SQL Server Logins.

Not at the database level just yet, just at the SQL Server Instance Level.

Metadata

The relevant views are under the following section :-

System Views (Transact-SQL) \ Catalog Views (Transact-SQL)  \ Security Catalog Views (Transact-SQL)

And, here they are:

System View Description Link
sys.server_principals Contains a row for every server-level principal. Link
sys.server_permissions Returns one row for each server-level permission. Link
sys.sql_logins Returns one row for every SQL Server authentication login. Link 
sys.server_role_members Returns one row for each member of each fixed and user-defined server role. Link 

 

 

Code

Reference

Our code will be based on Bill Graziano’s published here:

Scripting out SQL Server Logins
Link

 

Function – Scaler – [dbo].[fn_hexadecimal]

Documentation

INFO: Converting Binary Data to Hexadecimal String
Link

Code


USE [master]
GO

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO

if object_id('[dbo].[fn_hexadecimal]') is null
begin

	exec('create function [dbo].[fn_hexadecimal]() returns varchar(256) as begin return 1/0 end ')

end
go

ALTER FUNCTION [dbo].[fn_hexadecimal] 
(
    -- Add the parameters for the function here
     @binvalue varbinary(256)
)
RETURNS VARCHAR(256)
AS
BEGIN

    DECLARE @charvalue varchar(256)
    DECLARE @i int
    DECLARE @length int
    DECLARE @hexstring char(16)
    SELECT @charvalue = '0x'
    SELECT @i = 1
    SELECT @length = DATALENGTH (@binvalue)
    SELECT @hexstring = '0123456789ABCDEF' 
    WHILE (@i <= @length) 
    BEGIN
      DECLARE @tempint int
      DECLARE @firstint int
      DECLARE @secondint int
      SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1))
      SELECT @firstint = FLOOR(@tempint/16)
      SELECT @secondint = @tempint - (@firstint*16)
      SELECT @charvalue = @charvalue +
        SUBSTRING(@hexstring, @firstint+1, 1) +
        SUBSTRING(@hexstring, @secondint+1, 1)
      SELECT @i = @i + 1
    END
    return @charvalue

END
GO

grant execute on [dbo].[fn_hexadecimal]  to [public]
go

 

SP – dbo.sp_ScriptServerPrincipal



use master
go


if object_id('dbo.sp_ScriptServerPrincipal') is null
begin

	exec('create procedure [dbo].[sp_ScriptServerPrincipal] as ')

end
go

alter procedure [dbo].[sp_ScriptServerPrincipal] 
(
	   @serverPrincipal			sysname = null
     , @overwritePassword		bit = 1
)
as
begin

	/*

		2016-10-10 dadeniji

			To Avoid:
				Msg 33020, Level 16, State 1, Line 22
				A HASHED password cannot be set for a login that has CHECK_POLICY turned on.
			
			Set:
				CHECK_POLICY=OFF


		2016-10-10 dadeniji

			To Avoid:
	
				Msg 15122, Level 16, State 1, Line 1
				The CHECK_EXPIRATION option cannot be used when CHECK_POLICY is OFF.

			Set:
				CHECK_EXPIRATION = OFF
	*/
	set nocount on;

	PRINT '-----------------------------------------------------------------------------'
	PRINT '-- Script created on ' + CAST(GETDATE() AS VARCHAR(100))
	PRINT '-- SQL Instance :- ' + cast(serverproperty('servername') as sysname)
	PRINT '-----------------------------------------------------------------------------'

	declare @FORMAT_LOGIN_WINDOWS  nvarchar(4000)
	declare @FORMAT_LOGIN_WINDOWS_HEADER_CREATE varchar(600);
	declare @FORMAT_LOGIN_WINDOWS_HEADER_ALTER  varchar(600);
	declare @FORMAT_LOGIN_WINDOWS_BODY          varchar(600);

	declare @FORMAT_LOGIN_SQL			nvarchar(4000)
	declare @FORMAT_LOGIN_SQL_HEADER_CREATE varchar(600)
	declare @FORMAT_LOGIN_SQL_HEADER_ALTER  varchar(600)
	declare @FORMAT_LOGIN_SQL_BODY			nvarchar(4000)
	declare @FORMAT_LOGIN_SQL_PASSWORD		nvarchar(600)
	declare @FORMAT_LOGIN_SQL_SID			varchar(600)
	declare @FORMAT_LOGIN_DISABLED			nvarchar(4000)
	declare @FORMAT_LOGIN_CONNECT_SQL		nvarchar(4000)
	declare @FORMAT_ROLE_SQL				nvarchar(4000)

	declare @CHAR_TAB						char(1)

	declare @PERMISSION_TYPE_CONNECT_SQL    varchar(4)

	declare @tblLogin TABLE
	(
		  [id]		   int not null identity(1,1)
		, [principalID]  int not null
		, [name]	   sysname
		, [password]   varchar(256)
		, [type]	   char(1) not null
		, [database]   sysname
		, [language]   sysname	
		, [sid]		   varchar(256)

		, [isDisabled] bit
		, [isDisabledLiteral]
				as case [isDisabled]
						when 1 then ' DISABLE '
						else ' ENABLE '
				  end
		, [permissionStateCONNECTSQL] varchar(30)

		, [isExpirationChecked] bit

		, [isExpirationCheckedLiteral]
				as case [isExpirationChecked]
						when 0 then ' OFF '
						else ' ON '
				  end

		, [isPolicyChecked] bit
		, [isPolicyCheckedLiteral]
				as case [isPolicyChecked]
						when 0 then ' OFF '
						else ' ON '
				  end

	)

	declare @tblServerRole TABLE
	(
		  [id]    int not null identity(1,1)
		, [login] sysname not null
		, [role]  sysname not null
	)

	declare @sql							nvarchar(4000)
	declare @sqlLoginDisabled				nvarchar(4000)
	declare @sqlLoginPermissionToDBEngine	nvarchar(4000)

	declare @id int
	declare @idMax int

	declare @name		sysname
	declare @password   varchar(256)
	declare @type		char(1)
	declare @database	sysname
	declare @language	sysname
	declare @sid		sysname

	declare @isDisabled	bit
	declare @isDisabledLiteral varchar(60)

	declare @permissionStateCONNECTSQL varchar(60)

	declare @isExpirationChecked		bit
	declare @isExpirationCheckedLiteral varchar(30)

	declare @isPolicyChecked	    bit
	declare @isPolicyCheckedLiteral varchar(30)

	declare @role		sysname

	declare @clauseBegin     varchar(30)
	declare @clauseEnd	     varchar(30)
	declare @clauseElse      varchar(30) 
	declare @clauseWith	     varchar(30)
	declare @clauseComma	 varchar(30)

	set @CHAR_TAB = char(9)

	set @PERMISSION_TYPE_CONNECT_SQL = 'COSQ'

	set @clauseBegin = ' BEGIN ';
	set @clauseEnd = ' END ';
	set @clauseElse = ' ELSE ';
	set @clauseWith = ' WITH ';
	set @clauseComma = ' , ';

	set @FORMAT_LOGIN_WINDOWS_HEADER_CREATE 
		=  'CREATE LOGIN [@varLogin] from WINDOWS'
			 

	set @FORMAT_LOGIN_WINDOWS_HEADER_ALTER 
		=  'ALTER LOGIN [@varLogin]'
			 
	set @FORMAT_LOGIN_WINDOWS_BODY
		= 	 
		    + @CHAR_TAB + @clauseWith
			+ @CHAR_TAB + '      DEFAULT_DATABASE = [@varDatabase] '
			+ @CHAR_TAB + '    , DEFAULT_LANGUAGE = [@varLanguage] '
			;


	set @FORMAT_LOGIN_WINDOWS 
			= 'IF EXISTS (SELECT * FROM master.sys.server_principals where [name] = ''@varLogin'') '
				+ @clauseBegin
				+ @CHAR_TAB + @FORMAT_LOGIN_WINDOWS_HEADER_ALTER
				+ @CHAR_TAB + @FORMAT_LOGIN_WINDOWS_BODY
				+ @clauseEnd
				+ @clauseElse
				+ @clauseBegin
				+ @CHAR_TAB + @FORMAT_LOGIN_WINDOWS_HEADER_CREATE
				+ @CHAR_TAB + @FORMAT_LOGIN_WINDOWS_BODY
				+ @clauseEnd
				+ @CHAR_TAB + ' -- WINDOWS '


	set @FORMAT_LOGIN_SQL_HEADER_CREATE 
		=  'CREATE LOGIN [@varLogin]'
			 

	set @FORMAT_LOGIN_SQL_HEADER_ALTER 
		=  'ALTER LOGIN [@varLogin]'
			 
	set @FORMAT_LOGIN_SQL_BODY
		= 	 
		    + @CHAR_TAB + ' '
			+ @CHAR_TAB + '      DEFAULT_DATABASE = [@varDatabase] '
			+ @CHAR_TAB + '    , DEFAULT_LANGUAGE = [@varLanguage] '
			+ @CHAR_TAB + '    , CHECK_EXPIRATION = @varcheckExpiration '
			+ @CHAR_TAB + '    , CHECK_POLICY = @varcheckPolicy '
	


	set @FORMAT_LOGIN_SQL_PASSWORD
	             = @CHAR_TAB
	               + ' PASSWORD = @varPassword  HASHED '
				   ;


	set @FORMAT_LOGIN_SQL_SID
	             = @CHAR_TAB
	               + ' , SID = @varSID '
				   ;

	set @FORMAT_LOGIN_SQL 
			= 'IF EXISTS (SELECT * FROM master.sys.sql_logins where [name] = ''@varLogin'') '
				+ @clauseBegin
			    + @FORMAT_LOGIN_SQL_HEADER_ALTER
				+ @clauseWith 
				+ @FORMAT_LOGIN_SQL_BODY
				+ case @overwritePassword
						when 1 then ' , ' + @FORMAT_LOGIN_SQL_PASSWORD
						else ''
                  end
				+ @clauseEnd
				+ @clauseElse
				+ @clauseBegin
				+ @FORMAT_LOGIN_SQL_HEADER_CREATE
				+ @clauseWith 
				+ @FORMAT_LOGIN_SQL_PASSWORD
				+ @clauseComma
				+ @FORMAT_LOGIN_SQL_BODY
			    + @FORMAT_LOGIN_SQL_SID
				+ @clauseEnd
				+ @CHAR_TAB + ' -- SQL '


	set @FORMAT_LOGIN_DISABLED
			= 'ALTER LOGIN [@varLogin] [@varEnableOrDisable]; ';

	set @FORMAT_LOGIN_CONNECT_SQL
			= '[@varGrantOrDeny] CONNECT SQL TO [@varLogin]; '

	set @FORMAT_ROLE_SQL
			= 'EXEC master..sp_addsrvrolemember @loginame = [@varLOGIN], @rolename = [@varROLENAME]; '

			

	insert into @tblLogin
	(
		  [principalID]
		, [name]
		, [password]
		, [type]
		, [database]
		, [language]
		, [isDisabled]
		, [permissionStateCONNECTSQL]
		, [sid]
		, [isExpirationChecked]
		, [isPolicyChecked]
	)
	SELECT 
			  tblSQL.[principal_id]
			, tblSQL.[name]
			, [password]
				= case
					when (tblSSL.password_hash is null) then null
					--else [master].[dbo].[fn_hexadecimal](tblSSL.password_hash)
					else [master].[dbo].[fn_varbintohexstr]
					        (tblSSL.password_hash)
                  end
			, tblSQL.[type]
			, tblSQL.[default_database_name]
			, tblSQL.[default_language_name]
			, tblSQL.is_disabled
			, [permissionStateCONNECTSQL]
				= isNull(tblSSP.[state_desc], 'GRANT')
			, [sid]
				= case 
				     when (tblSQL.[sid] is null) then null
					 --else [master].dbo.[fn_hexadecimal](tblSQL.[sid])
					 --else [master].[sys].[fn_varbintohexstring]
					 else [master].[dbo].[fn_varbintohexstr]
					        (tblSQL.[sid])
                  end 
			, tblSSL.is_expiration_checked
			, tblSSL.is_policy_checked

	FROM   [master].[sys].[server_principals] tblSQL

	LEFT OUTER JOIN [master].[sys].[sql_logins] tblSSL

		on tblSQL.principal_id = tblSSL.principal_id

	LEFT OUTER JOIN [master].sys.server_permissions AS tblSSP
		on  tblSSP.grantee_principal_id = tblSQL.[principal_id]
		and tblSSP.[type] = @PERMISSION_TYPE_CONNECT_SQL

	where  tblSQL.[type_desc] in
				(
					  'SQL_LOGIN'
					, 'WINDOWS_LOGIN'
					, 'WINDOWS_GROUP'
				)
		
	and   tblSQL.[name] not in 
					(	
						  'sa'
						, 'guest'
					)

	and   tblSQL.[name] not like '##%'

	and   tblSQL.[type] in ('U', 'G', 'S', 'C', 'K') 
	AND   tblSQL.principal_id not between 101 and 255 
	AND   tblSQL.[name] <> N'##MS_AgentSigningCertificate##'

	and   tblSQL.[name] = isNull(@serverPrincipal, tblSQL.[name])


	insert into @tblServerRole
	(
		  [login]
		, [role] 
	)
	select 
			  [login] = l.[name]
			, [role] = r.[name]

	from master.sys.server_role_members rm

	join master.sys.server_principals r 
		on r.principal_id = rm.role_principal_id
	
	join master.sys.server_principals l 
		on l.principal_id = rm.member_principal_id

	where l.[name] not in ('sa')
	AND   l.[name] not like 'BUILTIN%'
	and   l.[NAME] not like 'NT AUTHORITY%'
	and   l.[name] not like '%\SQLServer%'

	and   l.[name] = isNull(@serverPrincipal, l.[name])

	/*
		Process Logins
	*/
	set @id = 1
	set @idMax = ( select max([id]) from @tblLogin)

	print '--Logins'
	print '------'
	while (@id <= @idMax)
	begin

		set @sql = null
		set @sqlLoginDisabled = null
		set @sqlLoginPermissionToDBEngine = null;

		select
				  @name       = [name]
				, @password   = isNull([password], '')
				, @type       = isNull([type], '')
				, @database   = isNull([database], '')
				, @language   = isNull([language], '')

				, @isDisabled = isNull(isDisabled, 0)
				, @isDisabledLiteral = isNull(isDisabledLiteral, 'ENABLE')

				, @permissionStateCONNECTSQL
					  = isNull([permissionStateCONNECTSQL], 'GRANT')


				, @sid		  = isNull([sid], '')

				, @isExpirationChecked 
							 = isNull(isExpirationChecked, '')

				, @isExpirationCheckedLiteral
							= isNull([isExpirationCheckedLiteral], 'OFF')

				, @isPolicyChecked 
							= isNull(isPolicyChecked, '')

				, @isPolicyCheckedLiteral
							= isNull([isPolicyCheckedLiteral], 'OFF')

		from    @tblLogin tblL

		where   tblL.[id] = @id

		if (@overwritePassword = 1)
		begin

			set @isPolicyCheckedLiteral = 'OFF'
			set @isExpirationCheckedLiteral = 'OFF'

		end	

		if (
				( @type in ('U', 'G')) 
		   )
		begin

			set @sql = @FORMAT_LOGIN_WINDOWS 


		end		   	
		else if 
			(
				( @type in ('S')) 
			)
		begin

			set @sql = @FORMAT_LOGIN_SQL

		end		   	

		set @sql = replace(@sql, '@varLogin', @name) 
		set @sql = replace(@sql, '@varPassword', @password) 
		set @sql = replace(@sql, '@varDatabase', @database) 
		set @sql = replace(@sql, '@varLanguage', @language) 
		set @sql = replace(@sql, '@varSID', @sid) 
		set @sql = replace(@sql, '@varcheckExpiration', @isExpirationCheckedLiteral) 

		set @sql = replace(@sql, '@varcheckPolicy', @isPolicyCheckedLiteral) 

		--Login Disabled
		set @sqlLoginDisabled = @FORMAT_LOGIN_DISABLED;
		set @sqlLoginDisabled
					= replace(@sqlLoginDisabled, '@varLogin', @name)
		set @sqlLoginDisabled
					= replace
						(
							  @sqlLoginDisabled
							, '[@varEnableOrDisable]'
							, @isDisabledLiteral
						)

		--Login Grant or Deny
		set @sqlLoginPermissionToDBEngine = @FORMAT_LOGIN_CONNECT_SQL

		set @sqlLoginPermissionToDBEngine
					= replace
						(
							  @sqlLoginPermissionToDBEngine
							, '[@varGrantOrDeny]'
							, @permissionStateCONNECTSQL
						)

		set @sqlLoginPermissionToDBEngine
					= replace(@sqlLoginPermissionToDBEngine, '@varLogin', @name)



		print isNull(@sql, '---')

		print @CHAR_TAB + isNull(@sqlLoginDisabled, '---Login Disabled')

		print @CHAR_TAB + isNull(@sqlLoginPermissionToDBEngine, '-- Login Deny')
							
		set @id = @id + 1

	end



	/*
		Process Server Roles
	*/
	print ''; print ''
	print '--Server Roles'
	print '------------'
	set @id = 1
	set @idMax = ( select max([id]) from @tblServerRole)

	while (@id <= @idMax)
	begin

		set @sql = null
		set @sqlLoginDisabled = null

		select
				  @name = [login]
				, @role = [role]
		from    @tblServerRole
		where   [id] = @id


		set @sql = @FORMAT_ROLE_SQL

		set @sql = replace(@sql, '@varLOGIN', @name) 
		set @sql = replace(@sql, '@varROLENAME', @role) 

		print isNull(@sql, '---')
			
		set @id = @id + 1

	end


end
go

EXEC sys.sp_MS_marksystemobject '[dbo].[sp_ScriptServerPrincipal]'
go


Invoke


declare @serverPrincipal sysname

set @serverPrincipal = 'compass'

exec [dbo].[sp_ScriptServerPrincipal] 
	@serverPrincipal = @serverPrincipal


Output

sqllogin

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s