SQL Server – The dbo user

Background

There is a database that we restore nightly from Production.

The user has been doing a bit of acceptance testing and they called us this morning to let us know that they have too much access.

What a thing, user complaining of too much access?

 

TroubleShooting

Session

Run As User, Check User

CURRENT_USER

SQL

set nocount on
go

set XACT_ABORT on
go

use [pin]
go

DECLARE @login NVARCHAR(256)
DECLARE @user  NVARCHAR(256);

select 
			[STAGE]
				 = 'Before Context Switch'
		  , [SYTEM_USER_BEFORE_SWITCH] 
				= SYSTEM_USER
		  , [CURRENT_USER_IN_DB] 
				= CURRENT_USER
		  , [isSrvRoleMember] 
				= IS_SRVROLEMEMBER('sysadmin', SYSTEM_USER)	
;		

	
EXECUTE AS LOGIN = 'LAB\AnnMargaret'

select 
		    [STAGE] 
				= 'Within Context Switch'
		  , [SYTEM_USER] 
				= SYSTEM_USER
		  , [CURRENT_USER_IN_DB]
				= CURRENT_USER
		  , [isSrvRoleMember]
				= IS_SRVROLEMEMBER('sysadmin', SYSTEM_USER)	


/*

	select top 10 *
	from   dbo.pintbl

*/

--setuser
REVERT

select 
		    [STAGE] = 'Post Context Switch'
		  , [SYTEM_USER] = SYSTEM_USER
		  , [CURRENT_USER_IN_DB] = CURRENT_USER
		  , [isSrvRoleMember] = IS_SRVROLEMEMBER('sysadmin', SYSTEM_USER)	
;		


Image

Explanation
  1. We have 3 sections above
    • Section 1
      • Before Any User Context Change
        • I,the DBA, have all access
    • Section 2
      • Upon User Context Change
        • We use ‘SetUser’ or ‘Execute as Login’ to change the User
        • Queried to ensure user context change
          • SYSTEM_USER
            • The changed User Login account
          • CURRENT_USER
            • If user has db_owner privileges, then dbo returned
            • Else the user’s name in the contextual db
    • Section 3
      • Once change is reverted
        • We are back to ourselve

 

Database

DBO ROLE

sys.database_role_members

SQL

select 
		  [account]
			= tblSDP.[name]

		, [accountType] 
			= tblSDP.[type_desc]

		, [role] 
			= tblSDPR.[name]

from   sys.database_principals tblSDP

inner join sys.database_role_members tblSDRM

		on tblSDP.principal_id = tblSDRM.member_principal_id

inner join sys.database_principals tblSDPR

		on tblSDRM.role_principal_id = tblSDPR.principal_id

where tblSDPR.[name] = 'db_owner'

-- skip user dbo
and   tblSDP.[name] != 'dbo' 

;


Output

 

Explanation
  1. Only one account, dataVal, has db_owner privileges
    • dataVal
      • dataVal is a SQL Account
      • We are looking to track down a Windows Login

 

“DB OWNER”

 sys.databases

SQL


use [pin]
go

select 
		 [database]
			= tblSD.[name]
		, [ownerSID]
			= tblSD.[owner_sid]
		, [owner]
			= suser_sname(tblSD.[owner_sid])

from   sys.databases tblSD

where  tblSD.[name] = db_name()


Image

Explanation
  1. Database’s owner is sa

 

SQL Instance

Server Role Member

sp_helpsrvrolemember

SQL

declare @srvrolename  sysname

set @srvrolename = 'sysadmin'

EXEC [master].[dbo].sp_helpsrvrolemember
		  @srvrolename = @srvrolename

 

Image

Explanation
  1. Our Login is not listed as having sysadmin privileges

 

Account Membership

xp_logininfo

SQL

declare @acctname sysname
declare @option   sysname

set @acctname = 'BUILTIN\Administrators'
set @option = 'members';

EXEC [master].[dbo].xp_logininfo 
		  @acctname = @acctname
		, @option = @option 
		;

Output

Explanation

We found the account is part of the Local Administrator group.

 

Summary

Here is how the account we are tracking ended up with more information than we thought it would have:

  1. OS
    • Active Directory Account
      • Part of Machine’s Local Administrator group
  2. SQL Instance
    • SysAdmin
      • BUILTIN\Administrators has sysadmin privileges
  3. SQL Database
    • dbo
      • All logins that sysadmin have dbo access in each database

 

SQL Server – Install Date – Application & Last Service Pack

Background

It is that time of year again for our Microsoft “True Up” and we need to come clean with new Installs.

 

What is “True Up”?

Link

 

Install & Last SP Date

Let us ask each database its install date and while at it get the date the last SP was applied.

Outline

  1. Install Date
    • Install
      • When SQL Service is installed some OS Accounts are automatically provisioned, as well
        • List of OS Accounts:
          • NT SERVICE\ReportServer
          • NT SERVICE\SQLSERVERAGENT
          • NT AUTHORITY\SYSTEM
          • NT Service\MSSQLSERVER
          • NT SERVICE\Winmgmt
          • NT SERVICE\SQLWriter
  2. Last Service Pack Apply Date
    • Service Pack Applied
      • When a Service Pack is applied, new certificates are applied, as well
        • ##MS_AgentSigningCertificate##
        • ##MS_PolicySigningCertificate##
        • ##MS_SQLAuthenticatorCertificate##
        • ##MS_SQLReplicationSigningCertificate##
        • ##MS_SQLResourceSigningCertificate##
        • ##MS_SmoExtendedSigningCertificate##

 

Code


set nocount on;
set transaction isolation level read uncommitted;
go
  
; with cteServerPrincipalNT
(
      [name]
    , [create_date]
)
as
(
  
    select
  
              tblSSP.[name]
            , [create_date]
				= convert
					(
						  datetime
						, tblSSP.[create_date]
					)
  
    from   sys.server_principals tblSSP
  
    where   tblSSP.[name] in
                (
                      'NT SERVICE\ReportServer'
                    , 'NT SERVICE\SQLSERVERAGENT'
                    , 'NT AUTHORITY\SYSTEM'
                    , 'NT Service\MSSQLSERVER'
                    , 'NT SERVICE\Winmgmt'
                    , 'NT SERVICE\SQLWriter'
                )
  
)

, cteServerPrincipalCertificate
(
      [name]
    , [create_date]
)
as
(
  
    select
  
              tblSSP.[name]
            , tblSSP.[create_date]
  
    from   sys.server_principals tblSSP
  
    inner join master.sys.certificates tblSC
   
        on tblSSP.[name] = tblSC.[name]
   
)

, cteProductVersion
(
	  [productLevel]
	, [version]
)
as
(
  
    select
		  [productLevel]
				= convert
			      (
					  varchar(60)
					, serverProperty('ProductLevel')
			     )

		, [@@VERSION]
			 = @@version

)
  
select top 1
  
          [sqlInstance]
            = cast(serverproperty('servername') as sysname)

		, [productLevel]
			= ctePV.productLevel
  

		, [@@version]
			= ctePV.[version]

        , [installDate] 
            = convert(varchar(30), cteSP.[create_date], 100)
  
        , [servicePackDate] 
            = case
 
                when (

                           ( cteSPC.[create_date] > cteSP.[create_date] ) 

                        or (
								    ( ctePV.[version] not like '%RTM%') 
								and ( ctePV.productLevel != 'RTM' )
						   )
                    ) then
                        convert(varchar(30), cteSPC.[create_date], 100)
                         
                else null
 
              end

 
from   cteServerPrincipalNT cteSP
  
outer apply cteServerPrincipalCertificate cteSPC

cross apply cteProductVersion ctePV
  
order by
          cteSP.[create_date] asc

Output

SQL Server – Server Principals / Logins

 

Background

In this post we will cover some of the principals that are created upon SQL Server Installation.

In some cases full sysadmin permissions are granted.

And, in some cases, through separation of duties, minimal permissions are granted.

 

Server Principals

Matrix

Name SID Type Create Date Usage
 ##MS_PolicyEventProcessingLogin##  0x51D095ECDC6C164C9B6047F5838CAAA0 SQL_LOGIN  [Install Date] Policy Based Management
 ##MS_PolicyTsqlExecutionLogin##  0xA9EEB439F4762546BD90D163703F6DA1 SQL_LOGIN 2014-02-20 20:49:46.837  Policy Based Management
 NT AUTHORITY\SYSTEM  0x010100000000000512000000  WINDOWS_LOGIN  [Install Date]  Local System
NT SERVICE\MSSQLSERVER  0x010600000000000550000000E20F4FE7B15874E48E19026478C2DC9AC307B83E  WINDOWS_LOGIN  [Install Date] Database Engine / Default Instance
NT Service\MSSQL$[Instance-Name]  WINDOWS_LOGIN  [Install Date]  Database Engine / Named Instance
 NT SERVICE\ReportServer  0x010600000000000550000000214401ACF066EA342187301080455260EB684BA2  WINDOWS_LOGIN  [Install Date] Reporting Services / Default Instance
 NT SERVICE\ReportServer$[Instance-Name]  WINDOWS_LOGIN  [Install Date] Reporting Services / Named Instance
NT SERVICE\SQLSERVERAGENT 0x010600000000000550000000DCA88F14B79FD47A992A3D8943F829A726066357  WINDOWS_LOGIN [Install Date] SQL Server Agent
NT SERVICE\SQLAgent$[Instance-Name]  WINDOWS_LOGIN  [Install Date]  SQL Server Agent / Named Instance
 NT SERVICE\SQLWriter  0x010600000000000550000000732B9753646EF90356745CB675C3AA6CD6B4D28B  WINDOWS_LOGIN  [Install Date]  Database Backup
 NT SERVICE\Winmgmt  0x0106000000000005500000005A048DDFF9C7430AB450D4E7477A2172AB4170F4  WINDOWS_LOGIN  [Install Date]  Windows Management

 

 

 

Categories

  1. Policy Based Management
    • ##MS_PolicyEventProcessingLogin##
    • ##MS_PolicyTsqlExecutionLogin##
  2. Local System Account
    • NT AUTHORITY\SYSTEM
  3. NT SERVICE\MSSQLServer
    • Database Engine
  4. NT SERVICE\ReportServer
    • The account specified during setup is provisioned as a member of the RSExecRole database role.
      Link
  5. NT SERVICE\SQLServerAgent
    • SQL Server Agent
      • Link
        The per-service SID of the SQL Server Agent service is provisioned as a Database Engine login. The per-service SID login is a member of the sysadmin fixed server role.
  6. NT Service\SQLWriter
    • SQL Writer Service
      • Link
        The SQL Writer service must run under the Local System account. The SQL Writer service uses the NT Service\SQLWriter login to connect to SQL Server. Using the NT Service\SQLWriter login allows the SQL Writer process to run at a lower privilege level in an account designated as no login, which limits vulnerability. If the SQL Writer service is disabled, then any utility which in relies on VSS snapshots, such as System Center Data Protection Manager, as well as some other 3rd-party products, would be broken, or worse, at risk of taking backups of databases which were not consistent. If neither SQL Server, the system it runs on, nor the host system (in the event of a virtual machine), need to use anything besides Transact-SQL backup, then the SQL Writer service can be safely disabled and the login removed. Note that the SQL Writer service may be invoked by a system or volume level backup, whether the backup is directly snapshot-based or not. Some system backup products use VSS to avoid being blocked by open or locked files. The SQL Writer service needs elevated permissions in SQL Server because in the course of its activities it briefly freezes all I/O for the instance of SQL Server.
  7. NT Service\Winmgmt
    • WMI
      Link
      Windows Management Instrumentation (WMI) must be able to connect to the Database Engine. To support this, the per-service SID of the Windows WMI provider (NT SERVICE\winmgmt) is provisioned in the Database Engine.
      The SQL WMI provider requires the following permissions:

      • Membership in the db_ddladmin or db_owner fixed database roles in the msdb database.
      • CREATE DDL EVENT NOTIFICATION permission in the server.
      • CREATE TRACE EVENT NOTIFICATION permission in the Database Engine.
      • VIEW ANY DATABASE server-level permission.
      • SQL Server setup creates a SQL WMI namespace and grants read permission to the SQL Server Agent service-SID.

 

 

References

Microsoft Developer Network

  1. Database Engine > Database Engine Features and Tasks  > Security Center for SQL Server Database Engine and Azure SQL Database
    Link
  2. Database Engine Features and Tasks > Database Engine Instances (SQL Server)  > Manage the Database Engine Services
    Link

 

Blogs

  1. Database Dave
    • Dont delete ##MS_PolicyEventProcessingLogin## or ##MS_PolicyTsqlExecutionLogin##
      Link

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