One DBA's Ongoing Search for Clarity in the Middle of Nowhere


*or*

Yet Another Andy Writing About SQL Server

Tuesday, December 22, 2015

The transaction log for database 'ABC' is full due to 'ACTIVE_BACKUP_OR_RESTORE'

I have recently had a client with a problem each morning where they were having processes fail with this message in their SQL Server Error Log:
Date 12/01/2015 5:58:48 AM
Log SQL Server (Current - 12/01/2015 1:00:00 PM)
Source spid118
Message Error: 9002, Severity: 17, State: 3.
--
Date 12/01/2015 5:58:48 AM
Log SQL Server (Current - 12/01/2015 1:00:00 PM)
Source spid118
Message The transaction log for database 'ABC' is full due to 'ACTIVE_BACKUP_OR_RESTORE'.
Their question was how their log could be full since their database is in SIMPLE recovery.   

Their particular situation is that database ABC is a 650GB OLTP database, and the backup to the remote location was taking 7-8 hours each night over the time in question:

server_name
database_name
backup_start_date
backup_finish_date
physical_device_name
type
BackupSizeGB
Server1
ABC
12/4/2015 23:30
12/5/2015 7:11
VNBU0-29756-4248-1450758615
D
630
Server1
ABC
12/3/2015 0:00
12/3/2015 7:46
VNBU0-33644-31396-1450499421
D
630
Server1
ABC
12/1/2015 23:30
12/2/2015 6:22
VNBU0-30500-35052-1450413013
D
424
Server1
ABC
11/30/2015 23:30
12/1/2015 6:37
VNBU0-18236-33032-1450326613
D
468
Server1
ABC
10/30/2015 23:30
10/31/2015 4:51
VNBU0-5696-14276-1447734610
D
386
Server1
ABC
10/29/2015 0:05
10/29/2015 5:27
VNBU0-14976-21580-1447475427
D
378
Server1
ABC
10/27/2015 23:31
10/28/2015 4:59
VNBU0-18040-27960-1447389025
D
367
Server1
ABC
10/26/2015 23:31
10/27/2015 4:34
VNBU0-20180-26980-1447302625
D
356
Server1
ABC
10/25/2015 23:31
10/26/2015 5:00
VNBU0-22808-28180-1447216223
D
372
Server1
ABC
10/24/2015 23:31
10/25/2015 5:21
VNBU0-6160-15336-1447129821
D
372
Server1
ABC
10/23/2015 23:31
10/24/2015 5:01
VNBU0-5396-24044-1447043425
D
359
Server1
ABC
10/22/2015 23:31
10/23/2015 7:47
VNBU0-8796-18884-1446957027
D
375
Server1
ABC
10/21/2015 23:37
10/22/2015 5:29
VNBU0-18032-28004-1446870623
D
364
Server1
ABC
10/20/2015 23:31
10/21/2015 5:00
VNBU0-8692-19836-1446784216
D
371


The way FULL backups work in SQL Server, the transaction log is not released for re-use during a FULL backup, even if regular LOG backups are occurring or the database is in SIMPLE recovery.  This is due to the fact that the portion of the LOG that is used during the FULL has to be persisted during the FULL in order to be backed up at the end of the FULL – that is, the FULL backup includes the data at the start of the FULL (23:30) *plus* the LOG used until the end of the FULL (in the case of the 12/04-12/05 backup, the LOG used from 23:30 to 07:11).  This is the meaning of the ACTIVE_BACKUP_OR_RESTORE message – the LOG is waiting for the end of the active FULL backup before it can be released for re-use, which in this case was causing the LOG/LDF file to grow to fill its mount point.

What this means is that the LOG file has to be large enough (or be able to auto-grow large enough) to hold all of the work done during the FULL backup.  In this system (like many others) there is maintenance done overnight (the largest on this system being a purge job which runs for 7-8 hours each night against database ABC almost exactly during this same time window).  This maintenance was generating hundreds of GB of LOG each night during the FULL backup, resulting in the errors shown above.

For this client, the problem turned out to be that the NetBackup process performing these particular backups had been replaced by a snapshot style backup and disabled at the end of October but had recently been accidentally re-enabled.  Shutting off this NetBackup schedule killed these backups, directly resolving the issue.

The alternative would be to have a sufficiently large LOG/LDF file to hold the work done during the FULL backup - even in SIMPLE recovery this is required.  Another option would be to examine the nightly maintenance and schedule it to a different window, away from the FULL backup to minimize the amount of LOG/LDF that needs to persist during the FULL.

Many people don't consider this situation, and it doesn't come up frequently - usually only in this specific situation of a relatively large database that is also high traffic to fill the LOG during the FULL.

Hope this helps!

--

NOTE: as I was finishing polishing this for release I noticed that Erik Darling (blog/@dumb_chauffeur) released a similar post earlier today "What happens to transaction log backups during full backups?" on the Brent Ozar Unlimited blog.  His post has good detail on proving the situation's existence via the Log Sequence Numbers (LSNs) stored in the FULL and LOG backups.




Thursday, December 17, 2015

What Permissions Does This Login Have?

I recently was tasked with this ticket:
Please add new login Domain\Bob to server MyServer.  Grant the login the same permissions as Domain\Mary.
On the face of it, this seems relatively straightforward, right?  It is the kind of request that we all get from time to time, whether as an ad-hoc task or as part of a larger project, such as a migration.

The catch of course is that it isn't that easy - how do you know what permissions Mary has?

  • Is Mary a member of any server-level roles?
  • What specific individual server permissions does she have?
  • What database(s) is she a member of?  What database role(s) is she in?
  • What specific object(s) does she have permissions to? (this is often the killer)
Each of these items can be manually checked, of course - you can browse to Security>>Logins and right-click on Mary to see what server-level roles she is in and which databases she has access to, and then you can browse to each database and check what roles she is in and which objects she has explicit permissions on....

Sounds like fun, right?

As with all things SQL, almost anything you can do via the GUI can also be done programmatically (although it can be ugly).  

If you read my blog with any regularity you know that I am a firm believer in building on the work of others while granting credit where due - that is, there is no need to reinvent the wheel when you have a freely available example to start with.  Over time I have compiled links in my personal store to code created by other people for each of the components of the task at hand:
Each of these gave me part of the answer to my question about Mary, but why not roll them all together?  After some brief digging I couldn't find someone who had taken that next step.

I took the code from each of the three sources above (thanks Kendal, Phillip, and Wayne!) and modified them to play nicely together, including wrapping them in sp_msforeachdb as relevant and adding WHERE clauses to filter for an individual login.

I considered what I might use this code for, and then took one further step - I took the code from sp_help_revlogin (thanks Microsoft!) and added it to the start of my new code block.  This allows me to script the login itself at the beginning in case I want to transfer the login and its permissions to a new server (or replace them if something goes wrong on the original server!)

--

In my Bob and Mary case, I ran the code, and ran a find/replace for Mary>>Bob - this gave me a script to create Domain\Bob and grant him all of the same role memberships and permissions as Domain\Mary.

One note - if you run this for SQL logins (rather than Windows logins) you have the issue of the login SID (Security Identifier).  That is one of the big bonuses of using sp_help_revlogin is that it scripts out the SID of the login, so that when you try to re-create that login it maintains its SID for all security chains.  Of course for what we are doing in this example you wouldn't want that - if Mary and Bob were SQL logins, you would want to take one additional step in the final script of editing the CREATE LOGIN statement to remove the @sid parameter (since your new login Bob couldn't use Mary's SID),  

--

The resulting script takes an input parameter of a login name (or NULL) and then outputs CREATE/GRANT/etc. commands for the login, role memberships, and object permissions of that login (or of all logins if NULL is specified).
  • First, sp_help_revlogin (and sp_hexadecimal) is created if it doesn't exist - if it does exist it is ALTER'ed with this recent version
  • sp_help_revlogin is run to generate a CREATE LOGIN
  • Kendal's code (slightly modified) is run to generate sp_addsrvrolemember  and GRANT/DENY statements for server-level security
  • Wayne's code (again modified) is run to generate CREATE USER statements for the databases
  • Phillip's code (modified) is run to generate sp_AddRoleMember statements for database roles
  • Finally, another piece of Wayne's code (from that same post) is run to generate the GRANT/DENY statements for individual database object permissions
The current version of the script (v 1.0) is located here. http://tiny.cc/PermScript

A few caveats - because I include the CREATE/ALTER PROC for sp_help_revlogin and sp_hexadecimal in my script, my actual parameter (@LoginName) to specify your desired login is roughly halfway down the script (after the CREATE/ALTER PROC code) - the easiest way to deal with this is to CTRL-F for "SET @LoginName"

The code outputs via a large number of result sets, so I *strongly* recommend you change your query output from Grid to Text before you run the script.

I have run this against versions from 2005 through 2014 and it has generated successfully.

I am working on improving commenting and error handling. but check it out and let me know what you think and any holes you find (and as with all things on the Web, run it at your own risk).

Hope this helps!

--

NOTE - someone mentioned in a comment that they had trouble accessing the link - I originally didn't publish the script in the blog because the length was prohibitive but here it is:

--

/*

Permissions Scripter v1.0

All code on the web should be examined and run at your own risk!

--

2015/12/16
Andy Galbraith @DBA_ANDY
http://nebraskasql.blogspot.com/

--

IMPORTANT - CTRL-F for 'SET @LoginName' and set the name

Strongly recommend you change the Query output from Grid to Text for best output results

--

Uses code from several sources that I have combined and modifed to work together.

Sources are attributed thoughout but are also noted here:

**  Microsoft - sp_help_revlogin - https://support.microsoft.com/en-us/kb/918992

**  Kendal Van Dyke @SQLDBA - "Scripting Server Permissions And Role Assignments" - http://www.kendalvandyke.com/2009/01/scripting-server-permissions-and-role.html

**  Phillip Kelley - "Generating scripts for database role membership in SQL Server 2005"
http://stackoverflow.com/questions/3265526/generating-scripts-for-database-role-membership-in-sql-server-2005

**  Wayne Sheffield @DBAWayne - "script out database users for the selected database" - http://www.sqlservercentral.com/Forums/Topic977700-146-1.aspx


*/


SET NOCOUNT ON

SELECT 'SET NOCOUNT ON;'+CHAR(13)+'USE [MASTER];'+CHAR(13)+'GO'+CHAR(13) as '/* Set Database Context to master */'



USE [master]
GO

/*
Microsoft - sp_help_revlogin - https://support.microsoft.com/en-us/kb/918992
*/

/****** Object:  StoredProcedure [dbo].[sp_hexadecimal]    Script Date: 10/12/2010 13:58:44 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = 'sp_hexadecimal')
EXEC ('CREATE PROC dbo.sp_hexadecimal AS SELECT GETDATE()')
GO

ALTER PROCEDURE [dbo].[sp_hexadecimal]
@binvalue varbinary(256),
@hexvalue varchar(256) OUTPUT
AS
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
SELECT @hexvalue = @charvalue

GO


/****** Object:  StoredProcedure [dbo].[sp_help_revlogin]    Script Date: 10/12/2010 13:58:51 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = 'sp_help_revlogin')
EXEC ('CREATE PROC dbo.sp_help_revlogin AS SELECT GETDATE()')
GO

ALTER PROCEDURE [dbo].[sp_help_revlogin] @login_name sysname = NULL AS
DECLARE @name sysname
DECLARE @type varchar (1)
DECLARE @hasaccess int
DECLARE @denylogin int
DECLARE @is_disabled int
DECLARE @PWD_varbinary  varbinary (256)
DECLARE @PWD_string  varchar (514)
DECLARE @SID_varbinary varbinary (85)
DECLARE @SID_string varchar (514)
DECLARE @tmpstr  varchar (MAX)
DECLARE @is_policy_checked varchar (3)
DECLARE @is_expiration_checked varchar (3)

DECLARE @defaultdb sysname

IF (@login_name IS NULL)
  DECLARE login_curs CURSOR FOR

      SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM
sys.server_principals p LEFT JOIN sys.syslogins l
      ON ( l.name = p.name ) WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name <> 'sa'
ELSE
  DECLARE login_curs CURSOR FOR


      SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM
sys.server_principals p LEFT JOIN sys.syslogins l
      ON ( l.name = p.name ) WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name = @login_name
OPEN login_curs

FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin
IF (@@fetch_status = -1)
BEGIN
  PRINT 'No login(s) found.'
  CLOSE login_curs
  DEALLOCATE login_curs
  RETURN -1
END
SET @tmpstr = '/* sp_help_revlogin script ** Generated ' + CONVERT (varchar, GETDATE()) + ' on ' + @@SERVERNAME + ' */'
PRINT @tmpstr
PRINT ''
WHILE (@@fetch_status <> -1)
BEGIN
  IF (@@fetch_status <> -2)
  BEGIN
    PRINT ''
    SET @tmpstr = '/* Login: ' + @name+' */'
    PRINT @tmpstr
    IF (@type IN ( 'G', 'U'))
    BEGIN -- NT authenticated account/group

      SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' FROM WINDOWS WITH DEFAULT_DATABASE = [' + @defaultdb + ']'
    END
    ELSE BEGIN -- SQL Server authentication
        -- obtain password and sid
            SET @PWD_varbinary = CAST( LOGINPROPERTY( @name, 'PasswordHash' ) AS varbinary (256) )
        EXEC sp_hexadecimal @PWD_varbinary, @PWD_string OUT
        EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT

        -- obtain password policy state
        SELECT @is_policy_checked = CASE is_policy_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END FROM sys.sql_logins WHERE name = @name
        SELECT @is_expiration_checked = CASE is_expiration_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END FROM sys.sql_logins WHERE name = @name

            SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' WITH PASSWORD = ' + @PWD_string + '
HASHED, SID = ' + @SID_string + ', DEFAULT_DATABASE = [' + @defaultdb + ']'

        IF ( @is_policy_checked IS NOT NULL )
        BEGIN
          SET @tmpstr = @tmpstr + ', CHECK_POLICY = ' + @is_policy_checked
        END
        IF ( @is_expiration_checked IS NOT NULL )
        BEGIN
          SET @tmpstr = @tmpstr + ', CHECK_EXPIRATION = ' + @is_expiration_checked
        END
    END
    IF (@denylogin = 1)
    BEGIN -- login is denied access
      SET @tmpstr = @tmpstr + '; DENY CONNECT SQL TO ' + QUOTENAME( @name )
    END
    ELSE IF (@hasaccess = 0)
    BEGIN -- login exists but does not have access
      SET @tmpstr = @tmpstr + '; REVOKE CONNECT SQL TO ' + QUOTENAME( @name )
    END
    IF (@is_disabled = 1)
    BEGIN -- login is disabled
      SET @tmpstr = @tmpstr + '; ALTER LOGIN ' + QUOTENAME( @name ) + ' DISABLE'
    END
    PRINT @tmpstr
  END

  FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin
   END
CLOSE login_curs
DEALLOCATE login_curs


RETURN 0


GO

DECLARE @LoginName sysname

/*

SET LOGIN NAME!!!!

*/


SET @LoginName = 'sa'

/*
For all Logins set to NULL

SET @LoginName = NULL
*/


EXEC sp_help_revlogin @login_name = @LoginName

PRINT ' '

/*
Kendal Van Dyke @SQLDBA - "Scripting Server Permissions And Role Assignments" - http://www.kendalvandyke.com/2009/01/scripting-server-permissions-and-role.html

Modifed by me to filter for an individual login if one is specified
*/

/* Generate statements to create server permissions for SQL logins, Windows Logins, and Groups */

-- Role Members
IF @LoginName is NOT NULL
SELECT  'EXEC sp_addsrvrolemember @rolename =' + SPACE(1)
        + QUOTENAME(usr1.name, '''') + ', @loginame =' + SPACE(1)
        + QUOTENAME(usr2.name, '''') +';' AS '/* Server Role Memberships */'
FROM    sys.server_principals AS usr1
        INNER JOIN sys.server_role_members AS rm ON usr1.principal_id = rm.role_principal_id
        INNER JOIN sys.server_principals AS usr2 ON rm.member_principal_id = usr2.principal_id
and usr2.name = @LoginName
ORDER BY rm.role_principal_id ASC
ELSE
SELECT  'EXEC sp_addsrvrolemember @rolename =' + SPACE(1)
        + QUOTENAME(usr1.name, '''') + ', @loginame =' + SPACE(1)
        + QUOTENAME(usr2.name, '''') +';' AS '/* Server Role Memberships */'
FROM    sys.server_principals AS usr1
        INNER JOIN sys.server_role_members AS rm ON usr1.principal_id = rm.role_principal_id
        INNER JOIN sys.server_principals AS usr2 ON rm.member_principal_id = usr2.principal_id

-- Permissions
IF @LoginName is NOT NULL
SELECT  server_permissions.state_desc COLLATE SQL_Latin1_General_CP1_CI_AS
        + ' ' + server_permissions.permission_name COLLATE SQL_Latin1_General_CP1_CI_AS
        + ' TO [' + server_principals.name COLLATE SQL_Latin1_General_CP1_CI_AS
        + '];' AS '/* Server Level Permissions */'
FROM    sys.server_permissions AS server_permissions WITH ( NOLOCK )
        INNER JOIN sys.server_principals AS server_principals WITH ( NOLOCK ) ON server_permissions.grantee_principal_id = server_principals.principal_id
WHERE   server_principals.type IN ( 'S', 'U', 'G' )
and server_principals.name = @LoginName
ORDER BY server_principals.name,
        server_permissions.state_desc,
        server_permissions.permission_name
ELSE
SELECT  server_permissions.state_desc COLLATE SQL_Latin1_General_CP1_CI_AS
        + ' ' + server_permissions.permission_name COLLATE SQL_Latin1_General_CP1_CI_AS
        + ' TO [' + server_principals.name COLLATE SQL_Latin1_General_CP1_CI_AS
        + '];' AS '/* Server Level Permissions */'
FROM    sys.server_permissions AS server_permissions WITH ( NOLOCK )
        INNER JOIN sys.server_principals AS server_principals WITH ( NOLOCK ) ON server_permissions.grantee_principal_id = server_principals.principal_id
WHERE   server_principals.type IN ( 'S', 'U', 'G' )
ORDER BY server_principals.name,
        server_permissions.state_desc,
        server_permissions.permission_name


/*
Wayne Sheffield @DBAWayne - "script out database users for the selected database" - http://www.sqlservercentral.com/Forums/Topic977700-146-1.aspx

Modified by me to run inside sp_msforeachdb and to filter for an individual login if one is specified
Also added COLLATE DATABASE_DEFAULT statements to handle databases with collations different from the instance
*/


DECLARE @strsql nvarchar(4000)

PRINT '/* Database Users */'
IF @LoginName is not NULL
set @strsql = 'SELECT ''/* ? */'';SELECT ''USE [?];
GO
IF NOT EXISTS (SELECT 1 FROM sys.database_principals WHERE name = '' +
QuoteName(dp.name, char(39)) COLLATE DATABASE_DEFAULT +        '')
CREATE USER '' + QuoteName(dp.name) +
       IsNull('' FOR LOGIN '' + QuoteName(sp.name),'''') +
       IsNull('' WITH DEFAULT_SCHEMA = '' + QuoteName(dp.default_schema_name),'''') + '';''
  FROM [?].sys.database_principals dp
       LEFT JOIN [?].sys.server_principals sp
         ON sp.sid = dp.sid
 WHERE dp.type like ''[GUS]''
and dp.name = '''+@LoginName+''''
ELSE
set @strsql = 'SELECT ''/* ? */'';SELECT ''USE [?];
GO
IF NOT EXISTS (SELECT 1 FROM sys.database_principals WHERE name = '' +
QuoteName(dp.name, char(39)) COLLATE DATABASE_DEFAULT +        '')
CREATE USER '' + QuoteName(dp.name) +
       IsNull('' FOR LOGIN '' + QuoteName(sp.name),'''') +
       IsNull('' WITH DEFAULT_SCHEMA = '' + QuoteName(dp.default_schema_name),'''') + '';''
  FROM [?].sys.database_principals dp
       LEFT JOIN [?].sys.server_principals sp
         ON sp.sid = dp.sid
 WHERE dp.type like ''[GUS]'''


EXEC sp_msforeachdb @strsql


/*
Phillip Kelley - "Generating scripts for database role membership in SQL Server 2005"
http://stackoverflow.com/questions/3265526/generating-scripts-for-database-role-membership-in-sql-server-2005


Modified by me to run inside sp_msforeachdb and to filter for an individual login if one is specified
*/

PRINT '/* Database Role Memberships */'

IF @LoginName is not NULL
set @strsql = 'SELECT ''/* ? */'';
SELECT ''USE [?];
GO
EXECUTE sp_AddRoleMember '''''' + roles.name + '''''', '''''' + users.name + ''''''''+'';''
 from [?].sys.database_principals users
  inner join [?].sys.database_role_members link
   on link.member_principal_id = users.principal_id
  inner join [?].sys.database_principals roles
   on roles.principal_id = link.role_principal_id
where users.name = '''+@LoginName+''''
ELSE
set @strsql = 'SELECT ''/* ? */'';
SELECT ''USE [?];
GO
EXECUTE sp_AddRoleMember '''''' + roles.name + '''''', '''''' + users.name + ''''''''+'';''
 from [?].sys.database_principals users
  inner join [?].sys.database_role_members link
   on link.member_principal_id = users.principal_id
  inner join [?].sys.database_principals roles
   on roles.principal_id = link.role_principal_id'

EXEC sp_msforeachdb @strsql


/*
Wayne Sheffield @DBAWayne - "script out database users for the selected database" - http://www.sqlservercentral.com/Forums/Topic977700-146-1.aspx

Modified by me to run inside sp_msforeachdb and to filter for an individual login if one is specified
*/


PRINT '/* Database Object Permissions */'

IF @LoginName is not NULL
set @strsql = 'SELECT ''/* ? */'';
SELECT ''USE [?];
GO
''+ dp.state_desc COLLATE SQL_Latin1_General_CP1_CI_AS + '' '' +
       dp.permission_name COLLATE SQL_Latin1_General_CP1_CI_AS +
       '' ON '' + QuoteName(ss.name) + ''.'' + QuoteName(so.name) +
       '' TO '' + QuoteName(dp2.name) + '';''+CHAR(13)
  FROM [?].sys.database_permissions dp
       JOIN [?].sys.database_principals dp2
         ON dp2.principal_id = dp.grantee_principal_id
       JOIN [?].sys.objects so
         ON so.object_id = dp.major_id
       JOIN [?].sys.schemas ss
         ON ss.schema_id = so.schema_id
WHERE dp2.name  = '''+@LoginName+''''
ELSE
set @strsql = 'SELECT ''/* ? */'';
SELECT ''USE [?];
GO
''+ dp.state_desc COLLATE SQL_Latin1_General_CP1_CI_AS + '' '' +
       dp.permission_name COLLATE SQL_Latin1_General_CP1_CI_AS +
       '' ON '' + QuoteName(ss.name) + ''.'' + QuoteName(so.name) +
       '' TO '' + QuoteName(dp2.name) + '';''+CHAR(13)
  FROM [?].sys.database_permissions dp
       JOIN [?].sys.database_principals dp2
         ON dp2.principal_id = dp.grantee_principal_id
       JOIN [?].sys.objects so
         ON so.object_id = dp.major_id
       JOIN [?].sys.schemas ss
         ON ss.schema_id = so.schema_id'

EXEC sp_msforeachdb @strsql


PRINT '/* END OF SCRIPT */'