SQL Server – Instance – Permissions

 

Background

Let us review SQL Instance Server Roles and Server Permissions.

Outline

  1. server roles
    • To get a read of available server roles, please issue sp_helpsrvrole
    • To read assigned server roles, please access sys.server_role_members
  2. server permissions
    • To get a read of available server permissions, please issue sys.fn_builtin_permissions
    • To identify server permissions for self, please use sys.fn_my_permissions
    • To gather assigned server permissions, please access sys.server_permissions

Server Roles

List available Server Roles

sp_helpsrvrole

Syntax


 exec sp_helpsrvrole

Output

serverRoles_20180622_0902AM

List Principals and Assigned Server Roles

sys.server_role_members

Syntax


select
          [principal]
            = tblSSP.[name]

/*

        , [principalID]
            = tblSSP.principal_id

        , [principalType]
            = tblSSP.[type_desc]

        , [principalCreateTS]
            = tblSSP.create_date

*/
        , [role]
            = tblSSR.[name]

/*

        , [roleID]
            = tblSSRM.[role_principal_id]
*/
from   sys.server_principals tblSSP

inner join sys.server_role_members tblSSRM

        on tblSSP.[principal_id] = tblSSRM.[member_principal_id]

inner join sys.server_principals tblSSR

        on tblSSRM.role_principal_id = tblSSR.principal_id

order by
      tblSSP.[name] asc
    , tblSSR.[name] asc

Output

sysDOTserver_role_Members_20180622_0932AM

Server Permissions

List Built-In Permissions

sys.fn_builtin_permissions

Syntax


select
		  [class]
			= tblSFBP.[class_desc]

		, [permision]
			= tblSFBP.[permission_name]

		, [type]
			= tblSFBP.[type]

from   sys.fn_builtin_permissions
		(
			'server'
		) tblSFBP

order by

	  tblSFBP.[class_desc]
	, tblSFBP.[permission_name]

Output

sysDOTfn_builtin_permissions__20180622_0951AM

List Server Permissions Assigned to Self

sys.fn_my_permissions

Syntax

select 

	  [principal]
		= SYSTEM_USER

	, [entity]
		= tblSFMP.[entity_name]

	, [permission]
		= tblSFMP.[permission_name]

from   sys.fn_my_permissions
			(
				  null
				, 'SERVER'
			) tblSFMP

order by
	  tblSFMP.[entity_name]
	, tblSFMP.[permission_name]

Output

sysDOTfn_my_permissions__20180622_1058AM.jpg

List Assigned Server Permissions

List principals and assigned permissions.

sys.server_permissions

Code


select
      [principal]
        = tblSSP2.[name]

    , [permission]
        = tblSSP.[permission_name]

    , [state]
        = tblSSP.[state_desc]

    --, tblSSP2.is_fixed_role

    --, tblSSP2.type
    -- , tblSSP.*

    , [principalType]
        = tblSSP2.[type_desc]

/*
    , [createTS]
        = tblSSP2.create_date
*/
from   sys.server_permissions tblSSP

INNER JOIN sys.server_principals tblSSP2

    ON tblSSP.grantee_principal_id
         = tblSSP2.principal_id

where
    (

        -- (tblSSP2.type != 'C')
        (
           tblSSP2.[type_desc]
             != 'CERTIFICATE_MAPPED_LOGIN'
        )

    )

and (
        (
            tblSSP.permission_name not in
                (
                    'CONNECT SQL'
                )
        )
    )

and (
        (
            -- class = 105
            tblSSP.class_desc not in
                (
                    'ENDPOINT'
                )
        )	

    )

order by
      tblSSP2.[name] asc
    , tblSSP.[permission_name] asc

Output

sysDOTserver_permissions__20180622_1037AM

 

References

  1. Microsoft
    • Docs / SQL / Relational databases / System catalog views
      • sys.server_permissions (Transact-SQL)
      • sys.server_role_members (Transact-SQL)
    • Docs / SQL / Relational databases / System stored procedures
      • sp_helpsrvrole (Transact-SQL)
    • Docs / SQL / Relational databases / System functions
      • sys.fn_builtin_permissions (Transact-SQL)
    • Docs / SQL / Relational databases / System stored procedures
      • Security Stored Procedures (Transact-SQL)

 

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