SSIS : The semaphore timeout period has expired

Semaphore? I thought that was men waving flags...

Some SSIS load packages failed this morning >

Error 121: "The semaphore timeout period has expired" (ERROR_SEM_TIMEOUT).

There is no one answer to this except to say that is a tcp/ip networking error.

Newsgroup posts on the issue range from antivirus software, network congestion and black hole routers. If anyone can be less vague, i'd love to hear your thoughts.

http://support.microsoft.com/kb/325487

DMV Performance Counters - Buffer Cache hit ratio example

Demonstrates the dmv, sys_dm_os_performance_counters.
Returns a single value, i.e. the buffer cache hit ratio.
This represents how well pages stay in buffer cache.
The closer the result is to 100%, the better.

Corrected from version here

This version -

1. includes the necessary join
2. will run on any server (wildcarded the server name)

SELECT (a.cntr_value * 1.0 / b.cntr_value) * 100.0 [BufferCacheHitRatio]
FROM (SELECT *, 1 x
     FROM sys.dm_os_performance_counters
    WHERE counter_name = 'Buffer cache hit ratio'
      AND object_name like '%:Buffer Manager%') a
JOIN
  (SELECT *, 1 x
  FROM sys.dm_os_performance_counters
    WHERE counter_name = 'Buffer cache hit ratio base'
      AND object_name like '%Buffer Manager%') b
ON a.x = b.x

Examining SQL Column types (2005+)

select o.type_desc, o.name, c.name, t.name , c.max_length, c.precision
from sys.columns c
inner join sys.types t
on c.system_type_id = t.system_type_id
inner join sys.objects o
on  c.object_id = o.object_id
where o.type_desc not like 'system%' 
and o.type_desc not like 'int%'
order by 1,2,3,4

TSQL : Agent Job Notififications via email

A quick run through of setting email notifications for emails

1) Set Sql agent to use a database mail profile.

-- Set default database mail profile for sql agent
-- My dbamail profile name is 'SQL Operator'

USE [msdb]
GO
EXEC master.dbo.xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent', N'DatabaseMailProfile', N'REG_SZ', N'SQL Operator'
GO

2) Add an operator (email recipient) for the alerts.

-- Add Operator Recipient for Sql Agent jobs
USE [msdb]
GO
EXEC msdb.dbo.sp_add_operator @name=N'Agent Job Operator', 
  @enabled=1, 
  @pager_days=0, 
  @email_address=N'dba@mydomain.co.uk'
GO

3) Set the jobs to send emails on failure.

USE [msdb]
GO
sp_update_job 
select 'exec sp_update_job @job_name =  '''+name+''' , @notify_email_operator_name = ''Agent Job Operator'' , @notify_level_email = 2'
from msdb.dbo.sysjobs
where enabled = 1
/*
note values for @notify_level_email signify when to send emails are - 
0 - never
1 - success
2 - failure
3 - always
*/

Moving TempDB / Splitting TempDB to multiple files

Script as below.
You need to restart SQL for tempdb to be recreated in the new locations...

USE master
GO

-- move tempdb data
ALTER DATABASE tempdb
MODIFY FILE (NAME = tempdev,
FILEGROWTH = 10% ,
MAXSIZE = UNLIMITED,
SIZE=1000MB ,
FILENAME = 'D:\databases\tempdb_1.mdf')
GO

-- move tempdb log
ALTER DATABASE tempdb
MODIFY FILE (NAME = templog, SIZE=1, FILENAME = 'D:\databases\templog.ldf')
GO

-- additional processor? split tempdb into equal filesizes, 1 per processor...

ALTER DATABASE tempdb ADD FILE
(NAME = tempdev_2,
FILEGROWTH = 10% ,
MAXSIZE = UNLIMITED,
SIZE=1000MB ,
FILENAME = 'D:\databases\tempdb_2.mdf')
GO

Configuring Event Forwarding - All events

USE msdb
go

EXEC master.dbo.sp_MSsetalertinfo 
  @forwardingserver=N'test', 
  @forwardalways=1, 
  @forwardingseverity=10
go

Vista : Disable TCP Window Scaling

TCP Window Scaling is a feature of Vista (& Windoze 2008 next year) that allows the intelligent use of a larger packet size when transferring data between hosts that support it.

The trouble is, not many hosts support it and some routers also dont like it.
The end result is people experiencing slow networking on Vista.

Turn it off like this (from Administrator command prompt) >

netsh interface tcp set global autotuninglevel=disabled

Check the status like this >

netsh interface tcp show global

A nice pretty screenshot >

Fast Row Counts

-- fast row counts
SELECT 
        S.name as schemaname,
        T.name as tablename,
        P.rows
FROM sys.partitions P 
INNER JOIN sys.tables T ON P.object_Id = T.object_id
INNER JOIN sys.schemas S ON T.schema_id = S.schema_id
WHERE P.index_id IN (0,1)
ORDER BY 1,2

USP_EffectiveLoginPermissions

Procedure to show login permissions for all databases on a server.
Create the Procedure in master db or your tools database.

EXEC USP_EffectiveLoginPermissions ''
go

CREATE PROCEDURE USP_EffectiveLoginPermissions
          @User VARCHAR(100)
AS
  BEGIN
  
    SET NOCOUNT ON
    
    IF OBJECT_ID('tempdb..##ServerLogins') IS NOT NULL
      BEGIN
        DROP TABLE ##SERVERLOGINS
      END
      
    IF OBJECT_ID('tempdb..##DBUsers') IS NOT NULL
      BEGIN
        DROP TABLE ##DBUSERS
      END
      
    IF OBJECT_ID('tempdb..##DBList') IS NOT NULL
      BEGIN
        DROP TABLE ##DBLIST
      END
          
    IF OBJECT_ID('tempdb..##Results') IS NOT NULL
      BEGIN
        DROP TABLE ##RESULTS
      END
      
    DECLARE  @DBName VARCHAR(128);    
    DECLARE  @SQLCmd VARCHAR(2000);    
    DECLARE  @NumberOfDBs INT;
    
    -- Get the SQL Server logins
    -- Create login table
    CREATE TABLE ##SERVERLOGINS (
  [SID] VARBINARY(85) NULL,
  [LOGIN_NAME] VARCHAR(128) NULL);
    
    -- Populate login table
    INSERT INTO ##SERVERLOGINS
    SELECT SID,CAST(LOGINNAME AS VARCHAR(128))  AS [LOGIN_NAME]
    FROM   MASTER.DBO.SYSLOGINS
    
    
    -- Create list of databases
    CREATE TABLE ##DBLIST (
  [DBNAME] VARCHAR(128))
    
    -- perform for all dbs on server
    INSERT INTO ##DBLIST
    SELECT   [NAME]
    FROM     MASTER..SYSDATABASES
    WHERE    [DBID] > 4
    ORDER BY [NAME];
    
    SELECT @NumberOfDBs = COUNT(* )
    FROM   ##DBLIST
           
    -- Create the output table for the Database User ID's
    CREATE TABLE ##DBUSERS (
  [DATABASE_USER_ID] VARCHAR(128),
  [SERVER_LOGIN] VARCHAR(128),
  [DATABASE_ROLE] VARCHAR(128),
  [DATABASE_NAME] VARCHAR(128));  
      
    -- Declare a cursor to loop through all the databases on the server
    DECLARE CSRDB CURSOR  FOR
  SELECT   [DBNAME]
  FROM     ##DBLIST
  ORDER BY [DBNAME]
             
    -- Open the cursor and get the first database name
    OPEN CSRDB
    
    FETCH NEXT FROM CSRDB
    INTO @DBName
         
    -- Loop through the cursor
    WHILE @@FETCH_STATUS = 0
      BEGIN      
        -- populate ##DBUsers table for current db
        SELECT @SQLCmd = 'INSERT ##DBUsers ' 
   + ' SELECT CAST(su.[name] AS VARCHAR(128)) AS [database_user_id], ' 
   + ' CAST(COALESCE (u.[login_name], ''* Orphaned *'') AS VARCHAR(128))AS [server_login], ' 
   + ' CAST(COALESCE (sug.name, ''Public'') AS VARCHAR(128)) AS [database_role],' 
   + ' CAST(''' + @DBName + ''' AS VARCHAR(128)) AS [database_name]' 
   + ' FROM [' + @DBName + '].[dbo].[sysusers] su' 
   + ' LEFT OUTER JOIN ##ServerLogins u' + ' ON su.sid = u.sid' 
   + ' LEFT OUTER JOIN ([' + @DBName + '].[dbo].[sysmembers] sm ' 
   + ' INNER JOIN [' + @DBName + '].[dbo].[sysusers] sug ' 
   + ' ON sm.groupuid = sug.uid)' 
   + ' ON su.uid = sm.memberuid ' 
   + ' WHERE su.hasdbaccess = 1' 

IF LTRIM(RTRIM(@User)) <> ''
  SELECT @SQLCmd = @SQLCmd + ' AND SU.name = ''' + @User + ''''

   -- uncomment to debug       
        -- PRINT @SQLCmd
        EXEC(@SQLCmd)
        
        -- Get the next database name
        FETCH NEXT FROM CSRDB
        INTO @DBName
             
      -- End of the cursor loop
      END
      
    -- Close and deallocate the CURSOR
    CLOSE CSRDB
    
    DEALLOCATE CSRDB
             
    CREATE TABLE ##RESULTS (
        [INSTANCE_NAME] VARCHAR(128),
  [DATABASE_NAME] VARCHAR(128),
  [OBJTYPE] VARCHAR(128),
  [LOGIN_STATUS] VARCHAR(128),
  [USERGRANTEE] VARCHAR(128));
    
    TRUNCATE TABLE ##RESULTS
    
    --data for logins
    INSERT INTO ##RESULTS
    SELECT 
  CAST(SERVERPROPERTY('ServerName') AS VARCHAR(50)) AS INSTANCE_NAME, 
  DATABASE_NAME,
  MAX(CASE [DATABASE_ROLE] WHEN 'db_datareader' THEN 'db_datareader, ' ELSE '' END) + 
  MAX(CASE [DATABASE_ROLE] WHEN 'db_accessadmin' THEN 'db_accessadmin, ' ELSE '' END) + 
  MAX(CASE [DATABASE_ROLE] WHEN 'db_backupoperator' THEN 'db_backupoperator, ' ELSE '' END) + 
  MAX(CASE [DATABASE_ROLE] WHEN 'db_datawriter' THEN 'db_datawriter, ' ELSE '' END) + 
  MAX(CASE [DATABASE_ROLE] WHEN 'db_ddladmin' THEN 'db_ddladmin, ' ELSE '' END) + 
  MAX(CASE [DATABASE_ROLE] WHEN 'db_denydatareader' THEN 'db_denydatareader, ' ELSE '' END) + 
  MAX(CASE [DATABASE_ROLE] WHEN 'db_denydatawriter' THEN 'db_denydatawriter, ' ELSE '' END) + 
  MAX(CASE [DATABASE_ROLE] WHEN 'db_owner' THEN 'db_owner, ' ELSE '' END) + 
  MAX(CASE [DATABASE_ROLE] WHEN 'db_securityadmin' THEN 'db_securityadmin, ' ELSE '' END) + 
  MAX(CASE [DATABASE_ROLE] WHEN 'public' THEN 'public, ' ELSE '' END) AS ROLE_OBJECTTYPE,
  CASE [SERVER_LOGIN] WHEN '* Orphaned *' THEN [SERVER_LOGIN] ELSE 'OK' END AS LOGIN_STATUS,
        CASE WHEN [DATABASE_USER_ID] = [SERVER_LOGIN] THEN [DATABASE_USER_ID] 
    WHEN [SERVER_LOGIN] = '* Orphaned *' THEN [DATABASE_USER_ID] 
    ELSE [DATABASE_USER_ID] + ' (' + [SERVER_LOGIN] + ')' END AS USER_GRANTEE
  
    FROM     
  ##DBUSERS
    GROUP BY 
  [DATABASE_NAME],
  [DATABASE_USER_ID],
  [SERVER_LOGIN]
                            
    SELECT   
  [INSTANCE_NAME], 
  [DATABASE_NAME],
  [USERGRANTEE],
  LEFT(OBJTYPE,LEN([OBJTYPE])-1) AS ROLES,
  [LOGIN_STATUS]
    FROM     
  ##RESULTS
    ORDER BY 
  1, 2, 3
  END
  go

USP_EffectivePermissions

Procedure to report on server & object permissions for a given user.
Usage : EXEC USP_EffectivePermissions 'username'
Install in master db to use from any db on the server.

CREATE PROC USP_EffectivePermissions
         @User VARCHAR(100)
AS
 BEGIN

   SET NOCOUNT ON
  
   IF OBJECT_ID('tempdb..##ServerLogins') IS NOT NULL
     BEGIN
       DROP TABLE ##SERVERLOGINS
     END
    
   IF OBJECT_ID('tempdb..##DBUsers') IS NOT NULL
     BEGIN
       DROP TABLE ##DBUSERS
     END
    
   IF OBJECT_ID('tempdb..##DBList') IS NOT NULL
     BEGIN
       DROP TABLE ##DBLIST
     END
    
   IF OBJECT_ID('tempdb..##DBObjPermissions') IS NOT NULL
     BEGIN
       DROP TABLE ##DBOBJPERMISSIONS
     END
    
   IF OBJECT_ID('tempdb..##CrossJoinMultiplier') IS NOT NULL
     BEGIN
       DROP TABLE ##CROSSJOINMULTIPLIER
     END
    
   IF OBJECT_ID('tempdb..##Results') IS NOT NULL
     BEGIN
       DROP TABLE ##RESULTS
     END
    
   DECLARE  @DBName VARCHAR(128);   
   DECLARE  @SQLCmd VARCHAR(2000);   
   DECLARE  @NumberOfDBs INT;
  
   -- Get the SQL Server logins
   -- Create login table
   CREATE TABLE ##SERVERLOGINS ([SID] VARBINARY(85) NULL,[LOGIN_NAME] VARCHAR(128) NULL);
  
   -- Populate login table
   INSERT INTO ##SERVERLOGINS
   SELECT SID,CAST(LOGINNAME AS VARCHAR(128))  AS [LOGIN_NAME]
   FROM   MASTER.DBO.SYSLOGINS
   WHERE  LOGINNAME = @User;
  
   -- Create list of databases
   CREATE TABLE ##DBLIST (
  [DBNAME] VARCHAR(128))
  
   -- perform for all dbs on server
   INSERT INTO ##DBLIST
   SELECT   NAME
   FROM     MASTER..SYSDATABASES
   WHERE    DBID > 4
   ORDER BY NAME;
  
   SELECT @NumberOfDBs = COUNT(* )
   FROM   ##DBLIST
         
   -- Create the output table for the Database User ID's
   CREATE TABLE ##DBUSERS (
  [DATABASE_USER_ID] VARCHAR(128),
  [SERVER_LOGIN] VARCHAR(128),
  [DATABASE_ROLE] VARCHAR(128),
  [DATABASE_NAME] VARCHAR(128));
  
   -- Create the output table for Object Level Permissions
   CREATE TABLE ##DBOBJPERMISSIONS (
  [DATABASE_NAME] VARCHAR(128),
  [GRANTOR] VARCHAR(128),
  [GRANTEE] VARCHAR(128),
  [OBJTYPE] VARCHAR(128),
  [OBJECTNAME] VARCHAR(128),
  [PERMISSION_LEVEL] VARCHAR(128),
  [PERMISSION] VARCHAR(128));
     
   -- Declare a cursor to loop through all the databases on the server
   DECLARE CSRDB CURSOR  FOR
  SELECT   DBNAME
  FROM     ##DBLIST
  ORDER BY DBNAME
           
   -- Open the cursor and get the first database name
   OPEN CSRDB
  
   FETCH NEXT FROM CSRDB
   INTO @DBName
       
   -- Loop through the cursor
   WHILE @@FETCH_STATUS = 0
     BEGIN     
       -- populate ##DBUsers table for current db
       SELECT @SQLCmd = 'INSERT ##DBUsers '
   + ' SELECT CAST(su.[name] AS VARCHAR(128)) AS [database_user_id], '
   + ' CAST(COALESCE (u.[login_name], ''* Orphaned *'') AS VARCHAR(128))AS [server_login], '
   + ' CAST(COALESCE (sug.name, ''Public'') AS VARCHAR(128)) AS [database_role],'
   + ' CAST(''' + @DBName + ''' AS VARCHAR(128)) AS [database_name]'
   + ' FROM [' + @DBName + '].[dbo].[sysusers] su'
   + ' LEFT OUTER JOIN ##ServerLogins u' + ' ON su.sid = u.sid'
   + ' LEFT OUTER JOIN ([' + @DBName + '].[dbo].[sysmembers] sm '
   + ' INNER JOIN [' + @DBName + '].[dbo].[sysusers] sug '
   + ' ON sm.groupuid = sug.uid)'
   + ' ON su.uid = sm.memberuid '
   + ' WHERE su.hasdbaccess = 1'
   + ' AND SU.name = ''' + @User + ''''
  -- uncomment to debug      
       -- PRINT @SQLCmd
       EXEC(@SQLCmd)
      
       -- populate ##DBObjPermissions table for current db
       SELECT @SQLCmd = 'use [' + @DBName + '];'
   + 'insert into ##DBObjPermissions '
   + ' select '
   + ' CAST(''' + @DBName + ''' AS VARCHAR(128)) AS [database_name],'
   + ' user_name(sec.grantor) as grantor, '
   + ' user_name(sec.uid) as grantee, '
   + ' case obj.type '
   + ' when ''C'' then ''Check constraint'' '
   + ' when ''D'' then ''Default (constraint or stand-alone)'' '
   + ' when ''F'' then ''Foreign Key'' '
   + ' when ''PK'' then ''Primary Key'' '
   + ' when ''P'' then ''Stored Procedure'' '
   + ' when ''FN'' then ''Function (Scalar)'' '
   + ' when ''IF'' then ''Function (Inline)'' '
   + ' when ''R'' then ''Rule (old-style, stand-alone)'' '
   + ' when ''RF'' then ''Replication-filter-procedure'' '
   + ' when ''S'' then ''System base table'' '
   + ' when ''TA'' then ''Assembly (CLR) DML trigger'' '
   + ' when ''TF'' then ''Function (TableValued)'' '
   + ' when ''U'' then ''Table'' '
   + ' when ''UQ'' then ''Unique constraint'' '
   + ' when ''V'' then ''View'' '
   + ' when ''X'' then ''Extended stored procedure'' '
   + ' end as objtype, '
   + ' stbl.name + ''.'' + obj.name as objectname, '
   + ' protecttype.name permission_level,  ' + ' action.name as permission '
   + ' from ' + '[' + @DBName + '].dbo.sysobjects as obj '
   + ' inner join [' + @DBName + '].dbo.sysusers as stbl on stbl.uid = obj.uid '
   + '        and stbl.name = ''' + @User + ''''
   + ' inner join ##DBUsers on ##DBUsers.[database_user_id] COLLATE SQL_Latin1_General_CP1_CS_AS = stbl.name COLLATE SQL_Latin1_General_CP1_CS_AS'
   + ' inner join [' + @DBName + '].dbo.sysprotects as sec on sec.id = obj.id '
   + ' inner join master.dbo.spt_values as action on sec.action = action.number and action.type = ''t'' '
   + ' inner join master.dbo.spt_values as protecttype on sec.protecttype = protecttype.number and protecttype.type = ''t'' '
   + ' where objectpropertyex(obj.id,''ismsshipped'') = 0 '
                     
  -- uncomment to debug                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      
       -- PRINT @SQLCmd
       EXEC(@SQLCmd)
      
       -- Get the next database name
       FETCH NEXT FROM CSRDB
       INTO @DBName
           
     -- End of the cursor loop
     END
    
   -- Close and deallocate the CURSOR
   CLOSE CSRDB
  
   DEALLOCATE CSRDB
  
   CREATE TABLE ##CROSSJOINMULTIPLIER (
     USERNAME VARCHAR(128)   NULL);
  
   IF @NumberOfDBs = 1
     INSERT INTO ##CROSSJOINMULTIPLIER
     SELECT [SERVER_LOGIN]
     FROM   ##DBUSERS -- 1 db, multiply section headers by users
   ELSE
     INSERT INTO ##CROSSJOINMULTIPLIER
     SELECT TOP 1 'dummy'
     FROM   SYSOBJECTS -- multiple dbs,
           
   CREATE TABLE ##RESULTS (
     [HEADER_ROW] INT,[DATABASE_NAME] VARCHAR(128),[SORT_ORDER] INT,[GRANTOR] VARCHAR(128),[GRANTEE] VARCHAR(128),[OBJTYPE] VARCHAR(128),[OBJECTNAME] VARCHAR(128),[USERGRANTEE] VARCHAR(128),[PERMISSION_LEVEL] VARCHAR(128),[PERMISSION] VARCHAR(128));
  
   TRUNCATE TABLE ##RESULTS
  
   --data for logins
   INSERT INTO ##RESULTS
   SELECT
  0 AS HEADER_ROW,
  CAST(SERVERPROPERTY('ServerName') AS VARCHAR(50)) + ' - ' + DATABASE_NAME AS DATABASE_NAME,
  2 AS SORT_ORDER,
  '' AS GRANTOR,
  '' AS GRANTEE,
  MAX(CASE [DATABASE_ROLE] WHEN 'db_datareader' THEN 'db_datareader, ' ELSE '' END) +
  MAX(CASE [DATABASE_ROLE] WHEN 'db_accessadmin' THEN 'db_accessadmin, ' ELSE '' END) +
  MAX(CASE [DATABASE_ROLE] WHEN 'db_backupoperator' THEN 'db_backupoperator, ' ELSE '' END) +
  MAX(CASE [DATABASE_ROLE] WHEN 'db_datawriter' THEN 'db_datawriter, ' ELSE '' END) +
  MAX(CASE [DATABASE_ROLE] WHEN 'db_ddladmin' THEN 'db_ddladmin, ' ELSE '' END) +
  MAX(CASE [DATABASE_ROLE] WHEN 'db_denydatareader' THEN 'db_denydatareader, ' ELSE '' END) +
  MAX(CASE [DATABASE_ROLE] WHEN 'db_denydatawriter' THEN 'db_denydatawriter, ' ELSE '' END) +
  MAX(CASE [DATABASE_ROLE] WHEN 'db_owner' THEN 'db_owner, ' ELSE '' END) +
  MAX(CASE [DATABASE_ROLE] WHEN 'db_securityadmin' THEN 'db_securityadmin, ' ELSE '' END) +
  MAX(CASE [DATABASE_ROLE] WHEN 'public' THEN 'public, ' ELSE '' END) AS ROLE_OBJECTTYPE,
       'database user' AS OBJ_NAME,
  CASE [SERVER_LOGIN] WHEN [DATABASE_USER_ID] THEN [DATABASE_USER_ID] ELSE [DATABASE_USER_ID] + ' (' + [SERVER_LOGIN] + ')' END AS USER_GRANTEE,
  '' AS PERMISSION_LEVEL,
  '' AS PERMISSION
   FROM    
  ##DBUSERS
   GROUP BY
  [DATABASE_NAME],
  [DATABASE_USER_ID],
  [SERVER_LOGIN]
           
   -- data for objects
   INSERT INTO ##RESULTS
   SELECT  0 AS HEADER_ROW,
   CAST(SERVERPROPERTY('ServerName') AS VARCHAR(50)) + ' - ' + DATABASE_NAME AS DATABASE_NAME,
   4 AS SORT_ORDER,
   GRANTOR,
   GRANTEE,
   [OBJTYPE] AS ROLE_OBJECTTYPE,
   [OBJECTNAME] AS OBJ_NAME,
   [GRANTEE] AS USER_GRANTEE,
   [PERMISSION_LEVEL] AS PERMISSION_LEVEL,
   [PERMISSION] AS PERMISSION
   FROM   ##DBOBJPERMISSIONS
         
   INSERT INTO ##RESULTS
   SELECT  1 AS HEADER_ROW,
   CAST(SERVERPROPERTY('ServerName') AS VARCHAR(50)) + ' - ' + DBNAME AS DATABASE_NAME,
   1 AS SORT_ORDER,
   '' AS GRANTOR,
   '' AS GRANTEE,
   'Role' AS ROLE_OBJECTTYPE,
   'Object' AS OBJ_NAME,
   'User' AS USER_GRANTEE,
   ' ' AS PERMISSION_LEVEL,
   ' ' AS PERMISSION
   FROM   ##DBLIST
          INNER JOIN ##RESULTS
            ON ##RESULTS.DATABASE_NAME = CAST(SERVERPROPERTY('ServerName') AS VARCHAR(50)) + ' - ' + ##DBLIST.DBNAME
               AND ##RESULTS.SORT_ORDER = 2
                                         
   -- header rows for objects
   INSERT INTO ##RESULTS
   SELECT  1 AS HEADER_ROW,
   CAST(SERVERPROPERTY('ServerName') AS VARCHAR(50)) + ' - ' + DBNAME AS DATABASE_NAME,
   3 AS SORT_ORDER,
   '' AS GRANTOR,
   '' AS GRANTEE,
   'Object Type' AS ROLE_OBJECTTYPE,
   'Object' AS OBJ_NAME,
   'Grantee' AS USER_GRANTEE,
   'Permission Level' AS PERMISSION_LEVEL,
   'Permission' AS PERMISSION
   FROM   ##DBLIST
          INNER JOIN ##RESULTS
            ON ##RESULTS.DATABASE_NAME = CAST(SERVERPROPERTY('ServerName') AS VARCHAR(50)) + ' - ' + ##DBLIST.DBNAME
               AND ##RESULTS.SORT_ORDER = 4
                                         
   SELECT  
  DATABASE_NAME,
  GRANTOR,
  GRANTEE,
  OBJTYPE,
  OBJECTNAME,
  USERGRANTEE,
  PERMISSION_LEVEL,
  PERMISSION
   FROM    
  ##RESULTS
   ORDER BY
  DATABASE_NAME,
  SORT_ORDER
 END
 go