SQL Server – keeping a few while discarding most Objects

Background

Time to look for help again.

The specific database has too many objects, but I only need to keep about twenty tables or so.

And, remove the other ones.

Script

And, so I wrote a little script.

Outline

There are two parts:

  1. dbo.udtt_Object ( Table Type )
  2. dbo.sp_ScriptDropUnTaggedObjects ( Stored Procedure )

Objects

Table Type

dbo.udtt_Object


if not exists
        (
            select *
            from  sys.types tblSST
            where tblSST.[name] = 'udtt_Object'
        ) 
begin

    CREATE TYPE [dbo].[udtt_Object] AS TABLE
    (

          [id]		bigint	not null identity(1,1) 
        , [schema]  sysname not null 
        , [object]  sysname not null

        , primary key
            (
                  [schema]
                , [object]
            )

    )

end

 

Stored procedure

dbo.sp_ScriptDropUnTaggedObjects


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

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

end
go

alter procedure [dbo].[sp_ScriptDropUnTaggedObjects]
(
      @tblTable [dbo].[udtt_Object] READONLY
    , @errorIfObjectsToKeeepListIsEmpty bit = 1
)
as
begin

    set nocount on

    
    declare @tblTableCited TABLE
    (
          [id]	   int not null identity(1,1)
        , [schema] sysname not null
        , [object]  sysname not null
        , [type]    char(2) not null
        , [dropClause] 
                as case
                    when [type] = 'U' then ' Table '
                  end
    )

    declare @id smallint
    declare @table sysname 
    declare @iCount int
    declare @idMax smallint

    declare @idCited bigint
    declare @idCitedMax bigint

    declare @citedSchema	  sysname
    declare @citedObject	  sysname
    declare @citedObjectType  char(2)

    declare @citedObjectDropClause	varchar(30)

    declare @strLog nvarchar(600)
    declare @sql    nvarchar(255)

    declare @iNumberofTablesCited int


    declare @iNumberofAttempts int
    declare @iNumberofAttemptsMax int

    declare @CHAR_TAB char(1)

    set @CHAR_TAB = char(9);

    set @id = 1

    set @iCount = ( select count(*) from  @tblTable)
    set @idMax = ( select max( [id]) from  @tblTable)

    --print '@idMax ' + cast(@idMax as varchar(10))

    set @iNumberofTablesCited = -1
    set @idCited = 1

    set @iNumberofAttempts = 1
    set @iNUmberofAttemptsMax = 50


    set @strLog = 'Number of tables tagged ' 
                    + cast(isNull(@idMax, -1) as varchar(10))

    print @strLog


    if (
            (
                   ( @iCount = 0)
                or ( @idMax is null)
            )
            and 
            (
                (@errorIfObjectsToKeeepListIsEmpty = 1)
            )
       )
       begin

        set @strLog = 'Object to keep list is empty'

        raiserror(@strLog, 16,1)

        return -1

       end

    while (
                    ( @iNumberofTablesCited != 0)
                and ( @iNumberofAttempts <= @iNumberofAttemptsMax) ) begin set @strLog = @CHAR_TAB + 'Attempt ' + cast(@iNumberofAttempts as varchar(3)) print @strLog delete from @tblTableCited insert into @tblTableCited ( [schema] , [object] , [type] ) select tblSS.[name] , tblSO.[name] , tblSO.[type] from sys.objects tblSO inner join sys.schemas tblSS on tblSO.[schema_id] = tblSS.[schema_id] where tblSO.[type] = 'U' and not exists ( select tblT.[object] from @tblTable tblT where tblT.[object] = tblSO.[name] ) and not exists ( select * from sys.foreign_keys tblSFK where tblSO.[object_id] = tblSFK.[referenced_object_id] ) set @idCitedMax = ( select max( [id]) from @tblTableCited) set @iNumberofTablesCited = (select count(*) from @tblTableCited ) set @strLog = @CHAR_TAB + cast(@iNumberofTablesCited as varchar(10)) + ' objects cited' print @strLog if (@iNumberofTablesCited > 0)
        begin

            while (@idCited <= @idCitedMax)

            begin

                select
                          @citedSchema = tblTC.[schema]
                        , @citedObject = tblTC.[object]
                        , @citedObjectType = tblTC.[type]
                        , @citedObjectDropClause = tblTC.[dropClause]

                from    @tblTableCited tblTC
        
                where  tblTC.[id] = @idCited

                if (
                            ( @citedSchema is not null )
                        and ( @citedObject is not null )
                        and (@citedObjectDropClause is not null)
                    )
                begin
                
                    set @strLog =
                                    @CHAR_TAB + @CHAR_TAB + 
                                    + cast(@idCited as varchar(10))
                                    + @citedSchema
                                    + '.'
                                    + @citedObject


                    set @sql = 'DROP '
                                    + @citedObjectDropClause
                                    + quotename(@citedSchema)
                                    + '.'
                                    + QUOTENAME(@citedObject)

                    --print @sql

                    set @strLog = @CHAR_TAB + @CHAR_TAB + @sql
                
                    print @strLog

                    exec(@sql)

                end

                set @idCited = @idCited + 1

            end

        end -- @iNumberofTablesCited

        
        set @iNumberofAttempts = @iNumberofAttempts + 1

    end -- Number of retries

end
go


exec sp_MS_marksystemobject '[dbo].[sp_ScriptDropUnTaggedObjects]'
go

 

Invoke


set XACT_ABORT on
go

set nocount on
go

declare @tblTable as [dbo].[udtt_Object]

declare @commit bit

set @commit = 0

insert into @tblTable
select 'dbo', 'Users'
union
select 'dbo', 'Roles'

begin tran


    exec [dbo].[sp_ScriptDropUnTaggedObjects]
          @tblTable = @tblTable
        , @errorIfObjectsToKeeepListIsEmpty = 1

while (@@trancount > 0)
begin

    if (@commit=1)
    begin
    
        print 'commit tran';
        commit tran;
    
    end
    else
    begin
    
        print 'rollback tran';
        rollback tran;
    
    end
end

GitHub

DanielAdeniji/SQLServerScriptDropUntaggedObjects
Link

 

SQL Server – Database Permissions – List

Background

There are different levels of permissions in SQL Server.

There are concentric permissions.

Inclusive are Instance, database, schema, and object.

And, others that are more dependant on the object type.

Database Permissions

Code


SELECT
            [database]
              = db_name()

         ,  [username] 
              = tblGranteeDP.[name]

         ,  [login]     
             = suser_sname(tblGranteeDP.[sid])

         ,  [permissionClass]
             = tblDP.class_desc

         ,  [permissionName] 
            = tblDP.[permission_name]

         ,  [permissionState] 
            = tblDP.state_desc

         ,  [principalType] 
            = tblGranteeDP.[type_desc]

FROM sys.database_permissions AS tblDP

INNER JOIN sys.database_principals AS tblGrantorDP   
    ON tblGrantorDP.principal_id = tblDP.grantor_principal_id

INNER JOIN sys.database_principals AS tblGranteeDP
    ON tblGranteeDP.principal_id = tblDP.grantee_principal_id

LEFT OUTER JOIN sys.server_principals AS tblGranteeSP
    ON tblGranteeDP.principal_id = tblGranteeSP.principal_id

/*
    Identifies class on which permission exists.
*/
where (tblDP.class = 0)

/*
    Exclude those with mere CONNECT permission
*/
and  tblDP.permission_name not in 
        (   
            'CONNECT'
        )

order by
      db_name() asc
    , tblGranteeDP.[name] asc
    , tblDP.[permission_name] asc

Output

SQL Server – Security – Privileging & Tracking the Guest Account

Background

Each month we get databases from our Vendors.  And, we have to review data in those tables.

There are a couple of pathways to quickly granting accessing to our users.

In this post, we will grant access to the guest user.

 

SQL

Grant Access



grant connect to guest;

Review Access

Review Permissions grant/denied to Guest on Contextual Database

Code


declare @principal sysname

set @principal = 'guest'

; with cteObject
(
      objectID
    , [object]
    , schemaID
    , [schema]
)
as
(
    select
              tblSO.object_id
            , tblSO.[name]
            , tblSO.schema_id
            , tblSS.[name]

    from   sys.objects tblSO

    inner join sys.schemas tblSS

            on tblSO.[schema_id] = tblSS.[schema_id]
)
select 

          tblSP.[name]
        
        --, tblSDP.*

        , tblSDP.[state_desc]

        , tblSDP.[permission_name]

        , [objectClass]
            = tblSDP.class_desc

        , [object]
            = coalesce
                (
                     quoteName
                        (
                            cteO.[schema]
                        )
                    + '.'
                    + quoteName
                        (
                            cteO.[object]
                        )

                    , null
                )

            , [grantee]
                = SUSER_NAME(tblSDP.grantor_principal_id)

from   sys.database_principals tblSP

inner join sys.database_permissions tblSDP

    on tblSP.principal_id = tblSDP.[grantee_principal_id]

left outer join cteObject cteO

    on tblSDP.major_id = cteO.objectID

where tblSP.[name] = @principal

order by

    tblSP.[name] asc


Review Access for contextual database

Code

use master
go

set nocount on
go

set XACT_ABORT on
go

declare @tblResult table
(
      [database]    sysname
    , [principal]   sysname
    , [access]      sysname
    , [grantee]     sysname null
)

declare @command nvarchar(4000)

set @command =
                'if databasepropertyex(''?'', ''Collation'') is not null '
                + ' begin '
                + ' use [?]; '
                + ' select [database] = db_name(), tblSP.name,  tblSDP.[state_desc], SUSER_NAME(tblSDP.grantor_principal_id) '
                + ' from sys.database_principals tblSP '
                + ' inner join sys.database_permissions tblSDP '
                + ' on tblSP.principal_id = tblSDP.grantee_principal_id '
                + ' where tblSP.[name] = ''guest'' '
                + ' and tblSDP.[permission_name] = ''CONNECT'' '
                + ' and tblSDP.[state_desc]  = ''GRANT'' '
                + ' end '

insert @tblResult
(
      [database]    
    , [principal]   
    , [access]      
    , [grantee] 
)
exec master.dbo.[sp_MSforeachdb]
        @command1 = @command

select *

from   @tblResult tblR

order by 
    tblR.[database]

SQL Server – Scripting Drop User/Schema for empty schemas

Background

Early this morning I initiated a process to move a database from Production to QA.

As I tried to drop the users from the originating environment, I ran into the error pasted below:

drop user [LAB\ITV-IIS01$]
Msg 15138, Level 16, State 1, Line 13
The database principal owns a schema in the database, and cannot be dropped.

 

Origin

I know that the schema mentioned is a shell.  It was simply created as part of our user creation script.

Using SQL Server Management Studio, we can script the user creation script.

And, here is a sample.


CREATE USER [LAB\ITV-IIS01$]
FOR LOGIN [LAB\ITV-IIS01$] 
WITH DEFAULT_SCHEMA=[LAB\ITV-IIS01$]
GO

Remediation

Let us programmatically identify and script out those users.

SQL

Stored Procedure

dbo.sp_ScriptDropUsersWithSchemas


use master
go

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

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

end
go

-- exec sp_helpuser
alter procedure [dbo].[sp_ScriptDropUsersWithSchemas] 
as
begin

	;  with cteSystemUser
	(
		[username]
	)
	as
	(
		select 'dbo'
		union
		select 'guest'
		union
		select 'INFORMATION_SCHEMA'
		union
		select 'sys'
	)
	, cteSystemSchema
	(
		[schema]
	)
	as
	(
		select 'INFORMATION_SCHEMA'
		union
		select 'sys'
	)
	, cteObjectSchema
	(
		  [schema]
		, [object]
		, [schemaOwnerID]
		, [schemaOwnername]
	)
	as
	(
		select 
				  [schema] = tblSS.[name]
				, [object] = tblSO.[name]
				, [schemaOwnerID] = tblSS.principal_id
				, [schemaOwnername] = user_name(tblSS.principal_id)

		from   sys.schemas tblSS

		inner join sys.objects tblSO

				on tblSS.[schema_id] = tblSO.[schema_id]

		/*
			Skip System Schemas
		*/
		where  tblSS.[name] not in
					(
						select [schema]
						from   cteSystemSchema
					)
	)
	, cteUser
	(
		  [username]
		, [usertype]
		, [userSchemaDefault]
		, [userCreateDate]
		, [schema]
		, [sqlCommandDropSchema]
		, [sqlCommandDropUser]
	)
	as
	(
		select 
				  [username] = tblSDP.[name]
				, [usertype] = tblSDP.[type_desc]
				, [userSchemaDefault] = tblSDP.default_schema_name
				, [userCreateDate]= tblSDP.[create_date]

				, [schema] = tblSS.[name]

				, [sqlCommandDropSchema]
					= 'if schema_id(''' + tblSS.[name] + ''')' 
							+ ' is not null '
							+ ' begin '
							+ ' print ''drop schema '
							+ quoteName(tblSS.[name])
							+ ''' '
							+ '; '
							+ ' DROP SCHEMA '
							+ quoteName(tblSS.[name])
							+ ' end '

				, [sqlCommandDropUser]
					= 'if user_id(''' + tblSDP.[name] + ''')' 
							+ ' is not null '
							+ ' begin '
							+ ' print ''drop user '
							+ quoteName(tblSDP.[name])
							+ ''' '
							+ '; '
							+ ' DROP USER '
							+ quoteName(tblSDP.[name])
							+ ' end '

		from   sys.database_principals tblSDP

		left outer join sys.schemas tblSS

				on tblSDP.[name] = tblSS.[name]

		where tblSDP.[type_desc] in
				(
					  'SQL_USER'
					, 'WINDOWS_USER'
				)

		and  tblSDP.default_schema_name not in
				(
					'dbo'
				)
	)

	select 
			cteU.*

	from   cteUser cteU

	left outer join cteSystemUser cteSU

		on cteU.username = cteSU.username

	/*
		Skip System Users
	*/
	where cteSU.[username] is null

	/*
		Ensure that user does not own objects
	*/
	and not exists
		(	
	
			select 1

			from   cteObjectSchema cteOS

			where  cteU.[username] = cteOS.[schemaOwnername]

		)

end
go

exec sp_MS_marksystemobject '[dbo].[sp_ScriptDropUsersWithSchemas]'
go

Output

 

SQL Server – Life after Database Restore – Inherited Security

Background

Once a database is restored there a few things one has to do.

With SQL based accounts, one of those things is to realign the database users.

With Windows Authenticated users, things should be OK if in same Windows Domain.

On the other hand, if different domain we have to grant access to targeted Logins, as well.

 

Inherited Security

Let us talk about what I will call “Inherited Security“.

Outline

  1. Restore Database
  2. Create Login
    • Normal login, not sysadmin
  3. Query Login as the non-privilege Login
    • Using “Execute As Login” query one of the tables in the restored database
    • Errored out indicating Login does not have access to restored database
  4. Grant Server role permission to created Account
    • Only applicable if SQL Version is v2014
    • The specific SQL Instance privilege we will like to grant is “Connect any database
    • Again, please keep in mind that “Connect any database” was instituted in v2014
      • Docs / S​QL / T-​SQ​L / statements / GRANT Server Permissions (Transact-SQL)
        Link
  5. On targeted database, allow guest access
    • On targeted database, grant connect access to guest
    • In SQL Server, the guest access is anyone who is registered in SQL Server, but does not have an account on the targeted database
    • Grant specific permissions to guest account

Code

Create Login



--Norton by Symantec // Password Generator
--https://identitysafe.norton.com/password-generator/#
--s19miewRoest

declare @loginName	varchar(30)
declare @password	varchar(30)

set @loginName = 'BISUser'
set @password = 's19miewRoest'

if suser_id(@loginName) is null
begin

	print 'Create Login ' + @loginName + ' ...'

	create login [BISUser]
		with password = 's19miewRoest'
		, check_policy=off
		, check_expiration= off

	print 'Create Login ' + @loginName
end
go

 

Query restored data under guise of normal Login

Code

use [tempdb]
go

EXECUTE AS LOGIN = 'BISUser'
go

begin tran

	SELECT TOP 1 *

	FROM [INDThirdParty].[dbo].[Contacts]

rollback tran
go

REVERT
go

 

Error Image

 

Error Text

Msg 916, Level 14, State 1, Line 9
The server principal "BISUser" is not able to access the database "INDThirdParty" under the current security context.

Remediation – Grant Connect Any database

Code

Grant Connect Any database to Principal
Code

use [master]
GO
GRANT CONNECT ANY DATABASE TO [BISUser]
GO

Retried querying data and got new error message
Error

 

Msg 229, Level 14, State 5, Line 9
The SELECT permission was denied on the object 'Contacts', database 'INDThirdParty', schema 'dbo'.

 

Grant permission on targeted database to all users

On targeted database, tried granting appropriate permissions to all users

Code

--v2005 and above
exec sp_addrolemember @rolename = 'db_datareader', @membername = 'public'
go

--v2012  and above
ALTER ROLE db_datareader
  ADD MEMBER [public]
go

Error

 


Msg 15405, Level 16, State 1, Line 36
Cannot use the special principal 'public'.
Msg 15405, Level 16, State 1, Line 41
Cannot use the special principal 'public'.

Conclusion

So we see that we need more.

 

Remediation – Allow Guest access

Code

Revoke “Connect Any Database” From Principal
Code

use [master]
GO

REVOKE CONNECT ANY DATABASE FROM [BISUser]
GO

On Targeted Database, Enable Guest Access
Code

use [INDThirdParty]
GO

GRANT CONNECT TO [Guest]
GO

Retried querying data and got the permission error message again
Error

Msg 229, Level 14, State 5, Line 9
The SELECT permission was denied on the object 'Contacts', database 'INDThirdParty', schema 'dbo'.
Grant permission on targeted database to guest

On targeted database, grant appropriate permissions to guest

Code

--v2005 and above
exec sp_addrolemember @rolename = 'db_datareader', @membername = 'guest'
go

--v2012  and above
ALTER ROLE db_datareader
  ADD MEMBER [guest]
go

Retry query

On targeted database, grant appropriate permissions to guest

Code

use [tempdb]
go

EXECUTE AS LOGIN = 'BISUser'
go

begin tran

	SELECT TOP 1 *

	FROM [INDThirdParty].[dbo].[Contacts]

rollback tran
go

REVERT
go

SQL Server Profiler

 

Explanation
  1. The NTUserName stays as the logged on user
  2. The LoginName changes as we transition in with “EXECUTE AS LOGIN” and out with “REVERT

Conclusion

We are good!

 

Dedicated

Dedicating to Matt Bowler & Dan,  a commenter on Matt’s Post

Restore database permissions
Posted On: 2013-Jan-10th
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