[EN] How to DO IT? FIX sp_timerequest Stored Procedure (and it’s FIXED now)

I like bubbles. Generally not too big, not too small. Better in less than two colors. Few weeks ago I discovered at one of my SQL Server, a little disquieting bubble on my free Idera SQL Server Monitor  – bigger than others and multicolored:

Capture_SQLSRV2005Wait_01

Yes, process No 78 is “a little bit different that others”. When I click on the red bubble {unfortunately is not 99 Red Baloon(s)} I get info:

image

As we see in last row: inputbuffer  is stored procedure: msdb.dbo.sp_readrequest. It will be good to look inside this procedure. This instance of SQL Server is compatible with 90 (DatabaseMail90) so it is SQL Server 2005. This stored procedure execute in specified interval task for checking queue data (for sending/receiving email information). We know that is the name of procedure – if not… anytime we can ask our server with the code:

   1:  SELECT Name 

   2:      FROM sys.procedures 

   3:      WHERE OBJECT_DEFINITION(object_id) 

   4:      LIKE '%part_of_sp_procedure_name%'

If we want (and in this case need) go inside this procedure, we can climb on the tree on this path: INSTANCE/ Databases/ System Databases/ msdb/ Programmability/ Stored Procedures/ System Stored Procedures/ dbo.sp_readrequest but… I don’t like it. Much better workaround I found at Pinal Dave {BLOG|TWITTER} post {HERE} with the fantastic function:

       sp_helptext 'dbo.sp_readrequest'

After executing – we can see full procedure code in very friendly view:

image

Of course some of You – the T-SQL coders – don’t like tables. If you want see this procedure – it’s simple too:

USE [msdb] GO /****** Object: StoredProcedure [dbo].[sp_readrequest] Script Date: 8.6.2012 12:39:38 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER OFF GO — sp_readrequest : Reads a request from the the queue and returns its — contents. ALTER PROCEDURE [dbo].[sp_readrequest] @receive_timeout INT — the max time this read will wait for new message AS BEGIN SET NOCOUNT ON — Table to store message information. DECLARE @msgs TABLE ( [conversation_handle] uniqueidentifier, [service_contract_name] nvarchar(256), [message_type_name] nvarchar(256), [message_body] varbinary(max) ) — Declare variables to store row values fetched from the cursor DECLARE @exit INT, @idoc INT, @mailitem_id INT, @profile_id INT, @conversation_handle uniqueidentifier, @service_contract_name NVARCHAR(256), @message_type_name NVARCHAR(256), @xml_message_body VARCHAR(max), @timediff INT, @rec_timeout INT, @start_time DATETIME, @localmessage NVARCHAR(256), @rc INT –Init variables SELECT @start_time = GETDATE(), @timediff = 0, @exit = 0, @rc = 0 WHILE (@timediff <= @receive_timeout) BEGIN — Delete all messages from @msgs table DELETE FROM @msgs — Pick all message from queue SET @rec_timeout = @receive_timeout – @timediff WAITFOR(RECEIVE conversation_handle, service_contract_name, message_type_name, message_body FROM ExternalMailQueue INTO @msgs), TIMEOUT @rec_timeout — Check if there was some error in reading from queue SET @rc = @@ERROR IF (@rc <> 0) BEGIN IF(@rc < 4) — make sure return code is not in reserved range (1-3) SET @rc = 4 –Note: we will get error no. 9617 if the service queue ‘ExternalMailQueue’ is currently disabled. BREAK END –If there is no message in the queue return 1 to indicate a timeout IF NOT EXISTS(SELECT * FROM @msgs) BEGIN SET @rc = 1 BREAK END — Create a cursor to iterate through the messages. DECLARE msgs_cursor CURSOR FOR SELECT conversation_handle, service_contract_name, message_type_name, CONVERT(VARCHAR(MAX), message_body) FROM @msgs; — Open the cursor OPEN msgs_cursor; — Perform the first fetch and store the values in the variables. FETCH NEXT FROM msgs_cursor INTO @conversation_handle, @service_contract_name, @message_type_name, @xml_message_body — Check @@FETCH_STATUS to see if there are any more rows to fetch. WHILE (@@FETCH_STATUS = 0) BEGIN — Check if the message is a send mail message IF(@message_type_name = N'{//www.microsoft.com/databasemail/messages}SendMail’) BEGIN — Get the handle to the xml document EXEC @rc = sp_xml_preparedocument @idoc OUTPUT, @xml_message_body, N’‘ IF(@rc <> 0) BEGIN IF(@rc < 4) — make sure return code is not in reserved rang (1-3) SET @rc = 4 END ELSE — parse the document and process its contents BEGIN — Execute a SELECT statement that uses the OPENXML rowset provider to get the MailItemId. SELECT @mailitem_id = MailItemId FROM OPENXML (@idoc, ‘/requests:SendMail’, 1) WITH (MailItemId INT ‘./MailItemId’) –Close the handle to the xml document EXEC sp_xml_removedocument @idoc — get account information SELECT @profile_id = profile_id FROM sysmail_mailitems WHERE mailitem_id = @mailitem_id IF(@profile_id IS NULL) — mail item has been deleted from the database BEGIN — log warning SET @localmessage = FORMATMESSAGE(14667, convert(NVARCHAR(50), @mailitem_id)) exec msdb.dbo.sysmail_logmailevent_sp @event_type=2, @description=@localmessage — Resource clean-up IF(@conversation_handle IS NOT NULL) END CONVERSATION @conversation_handle; — return code has special meaning and will be propagated to the calling process SET @rc = 2 END ELSE BEGIN — This returns the mail item to the client as multiple result sets EXEC sp_MailItemResultSets @mailitem_id = @mailitem_id, @profile_id = @profile_id, @conversation_handle = @conversation_handle, @service_contract_name = @service_contract_name, @message_type_name = @message_type_name SET @exit = 1 — make sure we exit outer loop END END — always break from the loop upon processing SendMail message BREAK END — Check if the message is a dialog timer. This is used for account retries ELSE IF(@message_type_name = N’http://schemas.microsoft.com/SQL/ServiceBroker/DialogTimer’) BEGIN — Handle the retry case. – DialogTimer is used for send mail reties EXEC @rc = sp_process_DialogTimer @conversation_handle = @conversation_handle, @service_contract_name = @service_contract_name, @message_type_name = N'{//www.microsoft.com/databasemail/messages}SendMail’ — Always break from the loop upon processing DialogTimer message — In case of failure return code from procedure call will simply be propagated to the calling process SET @exit = 1 — make sure we exit outer loop BREAK END — Error case ELSE IF (@message_type_name = ‘http://schemas.microsoft.com/SQL/ServiceBroker/Error’) — Error in the conversation, hence ignore all the messages of this conversation. BREAK — This is executed as long as fetch succeeds. FETCH NEXT FROM msgs_cursor INTO @conversation_handle, @service_contract_name, @message_type_name, @xml_message_body END CLOSE msgs_cursor; DEALLOCATE msgs_cursor; — Check if we read any request or only SSB generated messages — If a valid request is read with or without an error break out of loop IF (@exit = 1 OR @rc <> 0) BREAK –Keep track of how long this sp has been running select @timediff = DATEDIFF(ms, @start_time, getdate()) END — propagate return code to the calling process RETURN @rc END

I’m kidding now – just used “paste as html”  – but in this procedure You need find one line (line no 121):

SET @localmessage = FORMATMESSAGE
     (14667, convert(NVARCHAR(50), @mailitem_id))

which should be changing for:

SET @localmessage = FORMATMESSAGE(14667,  @mailitem_id)

After changing, just EXECUTE and effect will be shown immediately (HURRA!!):

Capture_SQLSRV2005Wait_06

Yes, no more red bubbles. But it is not the end of this story…

###

This problem appears first time in SQL Server 2005 and still is alive – for all versions except the new one – SQL Server 2012. Lets see it:

SQL Server 2005 (express) – line 121:

image

SQL Server 2008 (express) – line 121:

image

SQL Server 2008 R2 (express) – line 121:

image

SQL Server 2008 R2 (enterprise) – line 121:

image

ACHTUNG BABY!!! This problem has been fixed in SQL Server 2012

SQL Server 2012 (express) – line 111:

image

SQL Server 2012 (enterprise) – line 111:

image

That’s good. That’s good news for Aaron Bertrand, right?

So my advice for all DBA’a – after installation correct this procedure as an element of “first SQL Server Configuration Steps” – and no more red baloons (of course with this case and of course if it is important for you and of course if you will use DatabaseMail) for SQL Server 2005, 2008 and 2008 R2.

[EN] Sunday Market–PowerShell Scripts by Idera

HeroShot_PowershellScriptsIt Sunday again. And some shops are closed. If Yes, it’s time to get next useful tool for free. it’s my fifth episode in non-frequently series: Sunday Market. This time we will look for Idera again. This time we will not install anything. Just download and run. For free download You must:
>> go to Idera PowerShell Scripts site HERE
>> fill registration data
>> checking Your mail box for download link

Easy. As always. Next – when You downloaded nat too large (82 MB) file – we can look inside. This special package conains compilation of scripts for: Sharepoint Server, SQL Server (2005 & 2008), Exchange Server, Active Directory and just for PowerShell Plus Trial Installators (for 32- and 64-bits). Lets look for all parts:

\IderaPowerShellScripts\Idera_ADPowerShellScripts\

Add-IADGroupMember.ps1 / Disable-IADObject.ps1 / Disable-UserCannotChangePassword.ps1 / Enable-IADObject.ps1 / Enable-UserCannotChangePassword.ps1 / Get-IADAltRecipient.ps1 / Get-IADComputer.ps1 / Get-IADCurrentDomain.ps1 / Get-IADCurrentForest.ps1 / Get-IADDomainController.ps1 / Get-IADDomainController2.ps1 / Get-IADDomainPasswordPolicy.ps1 / Get-IADEmptyGroup.ps1
/ Get-IADExchangeAdmin.ps1 / Get-IADFSMORoleHolder.ps1 / Get-IADGroup.ps1 / Get-IADGroupMember.ps1 / Get-IADGroupMembership.ps1 / Get-IADObjectBySID.ps1 / Get-IADObjectSID.ps1 / Get-IADSite.ps1 / Get-IADSubnet.ps1 / Get-IADTombstoneComputer.ps1 / Get-IADTombstoneObject.ps1 / Get-IADTombstoneUser.ps1 / Get-IADUser.ps1 / Move-IADObject.ps1 / New-IADGroup.ps1 / New-IADPassword.ps1 / New-IADUser.ps1 / Remove-IADGroupMember.ps1 / Remove-IADObject.ps1 / Set-IADUser.ps1

\Idera_ExchangeScripts\
Disable-IEXUser.ps1 / Get-IEXAvailableSpace.ps1 / Get-IEXClusterNode.ps1 / Get-IEXCurrentRole.ps1 / Get-IEXDatabaseQuotaStatistics.ps1 / Get-IEXDatabaseSize.ps1 / Get-IEXDatabaseStatistics.ps1 / Get-IEXEmailAddressOwner.ps1 / Get-IEXEmailAddressReport.ps1 / Get-IEXForwardMailbox.ps1 / Get-IEXInactiveMailbox.ps1 / Get-IEXLastSendAndReceivedDate.ps1 / Get-IEXMailboxAddressCount.ps1 / Get-IEXMailboxCountReport.ps1 / Get-IEXMailboxFolderStatistics.ps1 / Get-IEXMailboxStatistics.ps1 / Get-IEXSendAsPermission.ps1 / Get-IEXTip.ps1 / New-IEXAddressList.ps1 / New-IEXMailboxFromCsv.ps1 / New-IEXTestMailbox.ps1 / Purge-IEXMailbox.ps1 / Remove-IEXDisabledUser.ps1 / Set-IEXEmailAddress.ps1 / Test-IEXDistinguishedName.ps1 / Test-IEXEmailAddress.ps1 / Test-IEXEmailAddress2.ps1

\IderaPowerShellScripts\Idera_SharePointScripts\
Add-SPList.ps1 / Add-SPSite.ps1 / Add-SPUser.ps1 / CheckIn-SPDocument.ps1 / CheckOut-SPDocument.ps1 / Display-SPItemInformation.ps1 / Display-SPList.ps1 / Display-SPWebPart.ps1 / Export-SPSite.ps1 / Get-SPView.ps1 / Get-SPWeb.ps1 / Import-SPSite.ps1 / Remove-SPList.ps1 / Remove-SPSite.ps1 / Set-SPItem.ps1 / ++ Readme.doc & Readme.pdf

\IderaPowerShellScripts\Idera_SQLServerScripts\
for SQL Server 2005
Backup-SSAS.ps1 / CheckDB-MSSQL-UsingADO.ps1 / Connect-MSSQL-IPSQLAuth.ps1 / Connect-MSSQL-IPWindowsAuth.ps1 / CreateDB-MSSQL-UsingSMO.ps1 / Create-MSSQLJob-UsingSMO.ps1 / Create-MSSQL-TestDb.ps1 / CreateTable-MSSQL-UsingSMO.ps1 / Get-MSSQL-DB-UsingADO.ps1 / Get-MSSQL-DB-UsingSMO.ps1 / Get-MSSQL-MaxMemory.ps1 / Get-MSSQL-Port-UsingDMO.ps1 / Get-MSSQL-Port-UsingWMI.ps1 / Get-MSSQL-ServerAttrib-Csv.ps1 / Get-MSSQL-ServerAttrib-Html.ps1 / Get-MSSQL-ServerRegisterations.ps1 / Get-MSSQL-Views-Csv.ps1 / Insert-MSSQL-SampleData-Csv.ps1 / List-MSSQL-FailedJobs.ps1List-MSSQL-PropertyInfo.ps1Load-MSSQL-SMO.ps1Start-SQLServerService.ps1Stop-SQLServerService.ps1 / ++SampleData.csv / ++Readme.doc & Readme.pdf

for SQL Server 2008
Backup-SSAS.ps1 / CheckDB-MSSQL-UsingADO.ps1 / Connect-MSSQL-IPSQLAuth.ps1 / Connect-MSSQL-IPWindowsAuth.ps1 / CreateDB-MSSQL-UsingSMO.ps1 / Create-MSSQLJob-UsingSMO.ps1 / Create-MSSQL-TestDb.ps1 / CreateTable-MSSQL-UsingSMO.ps1 / Get-MSSQL-DB-UsingADO.ps1 / Get-MSSQL-DB-UsingSMO.ps1 / Get-MSSQL-MaxMemory.ps1 / Get-MSSQL-Port-UsingDMO.ps1 / Get-MSSQL-Port-UsingWMI.ps1 / Get-MSSQL-ServerAttrib-Csv.ps1 / Get-MSSQL-ServerAttrib-Html.ps1 / Get-MSSQL-ServerRegisterations.ps1 / Get-MSSQL-Views-Csv.ps1 / Insert-MSSQL-SampleData-Csv.ps1 / List-MSSQL-FailedJobs.ps1 / List-MSSQL-PropertyInfo.ps1 / Load-MSSQL-SMO.ps1 / Start-SQLServerService.ps1 / Stop-SQLServerService.ps1 / ++SampleData.csv / ++Readme.doc & Readme.pdf

Interesting listing, right? Let’s run few examples below:

*** CHECKDB ***

## ======================================================

## Title       : CheckDB-MSSQL-UsingADO

## Description : Run a DBCC against specified server instance and database

## Author      : Idera

## Date        : 9/1/2008

## Input       : -serverInstance <server\instance>

##               -dbName <database name>

##               -verbose

##               -debug             

## Output      :

## Usage                                              : PS> .\CheckDB-MSSQL-UsingADO -serverInstance local -dbName master -verbose -debug

## Notes                                               : Adapted from Jakob Bindslet script

## Tag                                   : SQL Server, ADO, CheckDB

## Change log  :

## ======================================================

 

param

(

   [string]$serverInstance = “.”,

                [string]$dbName = “AdventureWorks”,

                [switch]$verbose,

                [switch]$debug

)

 

function main()

{

                if ($verbose) {$VerbosePreference = “Continue”}

                if ($debug) {$DebugPreference = “Continue”}

                CheckDBMSSQLUsingADO $serverInstance $dbName

}

 

function CheckDB-MSSQL-UsingADO($serverInstance, $dbName)

{

                trap [Exception]

                {

                               write-error $(“TRAPPED: “ + $_.Exception.Message);

                               continue;

                }

 

                $cn = new-object system.data.SqlClient.SqlConnection( `

                               “Data Source=$serverInstance;Integrated Security=SSPI;Initial Catalog=$dbName”);

                $ds = new-object System.Data.DataSet “dsCheckDB”

                $query = “DBCC CHECKDB($dbName) WITH TABLERESULTS”

                $da = new-object “System.Data.SqlClient.SqlDataAdapter” ($query, $cn)

                $da.Fill($ds)

 

                $dtCheckDB = new-object “System.Data.DataTable” “dsCheckDB”

                $dtCheckDB = $ds.Tables[0]

                $dtCheckDB | Format-Table -autosize `

                               -property Error, Level, State, MessageText, `

                                                                                              RepairLevel, Status, DbId, ObjectId, `

                                                                                              IndexId, PartitionId, AllocUnitId, File, `

                                                                                              Page, Slot, RefFile, RefPage, RefSlot, Allocation

}

 

main

*** MAXMEMORY ***

## ======================================================

## Title       : Get-MSSQL-MaxMemory

## Description : Get max memory property from SQL Server

## Author      : Idera

## Date        : 9/1/2008

## Input       : -serverInstance           <server\instance>

##                                                            -verbose

##                                                            -debug

## Output      : Integer in MB,

##                                                                          -1 if memory is unlimited,

##                                                                          -2 if no connection to the desired DB can be obtained

## Usage                                              : PS> .\Get-MSSQL-MaxMemory -serverInstance MyServer -verbose -debug

## Notes                                               : Adapted from Jakob Bindslet script

## Tag                                   : SQL Server, ADO, Configuration, Memory

## Change log  :

## ======================================================

 

param

(

                [string]$serverInstance = “local”,

                [switch]$verbose,

                [switch]$debug

)

 

function main()

{

                if ($verbose) {$VerbosePreference = “Continue”}

                if ($debug) {$DebugPreference = “Continue”}

                GetMSSQLMaxMemory $serverInstance

}

 

function Get-MSSQL-MaxMemory($serverInstance)

{

                trap [Exception]

                {

                               write-error $(“TRAPPED: “ + $_.Exception.Message);

                               continue;

                }

 

                #initialize ADO connection parameters

                Write-Debug “Initializing connection parameters…”

                $adoOpenStatic = 3

                $adoLockOptimistic = 3

                $timeout = 3

 

                #get memory configuration setting from sysconfigures system table

                # TIP: using ADO to run a query

                Write-Debug “Connecting to server: $serverInstance”

                $adoConnection = New-Object -comobject ADODB.Connection

                $adoRecordset = New-Object -comobject ADODB.Recordset

                $query = “SELECT value FROM [master].[dbo].[sysconfigures] WHERE config = 1544”

                $adoConnection.Set_ConnectionString(“Provider=SQLOLEDB; Data Source=” + $srv + “; Initial Catalog=master; Integrated Security=SSPI”)

                $adoConnection.Set_ConnectionTimeout($timeout)

                $adoConnection.Open()

 

                if ($adoConnection.State -eq “1”)

                {

                               Write-Debug “Connection succeeded…”

                $adoRecordset.Open($query, $adoConnection, $adoOpenStatic, $adoLockOptimistic)

                $adoRecordset.MoveFirst()

                $maxMemory = ($adoRecordset.Fields.Item(“value”).Value)

                if ($maxMemory -eq 2147483647)

                {

                                               Write-Verbose “Max memory is set to unlimited”

                      $maxMemory = 1

                }

                $adoRecordset.Close()

                $adoConnection.Close()

                }

                else

                {

                               Write-Debug “Connection failed…”

                   $maxMemory = 2

                }

                Write-Debug “Max memory is set to $maxMemory”

                Write-Output $maxMemory

}

 

main

*** CREATE TABLE ***

# ======================================================

## Title       : CreateTable-MSSQL-UsingSMO

## Description : Create a new table using SMO

## Author      : Idera

## Date        : 1/28/2009

## Input       : -serverInstance <server\instance>

##                                                            -dbName <database name>

##                                                            -schemaName <schema name>

##                                                            -tblName <table name>

##                                                            -verbose

##                                                            -debug

## Output      : Create a demo database and table

## Usage                                              : PS> .\CreateTable-MSSQL-UsingSMO -serverInstance MyServer -dbName SMOTestDB

##                                                                                                                                                        -schemaName SMOSchema -tblName SMOTable -verbose -debug

## Notes                                               : Adapted from Allen White script

## Tag                                   : SMO, SQL Server, Table

## Change log  : Revised SMO Assemblies

## ======================================================

 

param

(

                [string]$serverInstance = “(local)”,

                [string]$dbName = “SMOTestDB”,

                [string]$schemaName = “SMOSchema”,

                [string]$tblName = “SMOTable”,

                [switch]$verbose,

                [switch]$debug

)

 

function main()

{

                if ($verbose) {$VerbosePreference = “Continue”}

                if ($debug) {$DebugPreference = “Continue”}

                CreateTableMSSQLUsingSMO $serverInstance $dbName $schemaName $tblName

}

 

function CreateTable-MSSQL-UsingSMO($serverInstance, $dbName, $schemaName, $tblName)

{

                trap [Exception]

                {

                               write-error $(“TRAPPED: “ + $_.Exception.Message);

                               continue;

                }

               

                # Load SMO assemblies

                [void][reflection.assembly]::LoadWithPartialName( “Microsoft.SqlServer.Management.Common” );

                [void][reflection.assembly]::LoadWithPartialName( “Microsoft.SqlServer.SmoEnum” );

                [void][reflection.assembly]::LoadWithPartialName( “Microsoft.SqlServer.Smo” );

                [void][reflection.assembly]::LoadWithPartialName( “Microsoft.SqlServer.SmoExtended “ );

               

                # Instantiate a server object for the default instance

                $namedInstance = New-Object (‘Microsoft.SqlServer.Management.Smo.Server’) ($serverInstance)

               

                # Disable connection pooling

                $namedInstance.ConnectionContext.Set_NonPooledConnection($TRUE)

               

                # Explicitly connect because connection pooling is disabled

                $namedInstance.ConnectionContext.Connect()

 

                cls

               

                # Connect to the server with Windows Authentication and drop database if exist

                if ($namedInstance.Databases[$dbName] -ne $null)

                {

                               Write-Debug “The test database already exists on $namedInstance”

                               Write-Debug “Deleting it now…”

                               $namedInstance.Databases[$dbName].drop()

                }

 

                # Instantiate a database object

                Write-Debug “Creating database: $dbName”

                $database = newobject(“Microsoft.SqlServer.Management.Smo.Database”) ($namedInstance, $dbName)

               

                # Create the new database on the server

                $database.Create()

               

                # Instantiate a schema object

                Write-Debug “Creating schema: $SchemaName”

                $schema = newobject(“Microsoft.SqlServer.Management.Smo.Schema”) ($database, $schemaName)

               

                # Create the new schema on the server

                $schema.Create()

               

                # Instantiate a table object

                Write-Debug “Creating table: $TblName”

                $table = newobject(“Microsoft.SqlServer.Management.Smo.Table”) ($Database, $TblName)

               

                # Add Field1 column

                $colField1 = NewObject(“Microsoft.SqlServer.Management.Smo.Column”) ($table, “Field1”)

                # TIP: setting an object type from a NetClassStaticMethod

                $colField1.DataType = [Microsoft.SqlServer.Management.Smo.Datatype]::Int

                $table.Columns.Add($colField1)

               

                # Add Field2 column

                $colField2 = NewObject(“Microsoft.SqlServer.Management.Smo.Column”) ($table, “Field2”)

                $colField2.DataType =  [Microsoft.SqlServer.Management.Smo.Datatype]::NVarchar(25)

                $table.Columns.Add($colField2)

               

                # Add Field3 column

                $colField3 = NewObject(“Microsoft.SqlServer.Management.Smo.Column”) ($table, “Field3”)

                $colField3.DataType = [Microsoft.SqlServer.Management.Smo.Datatype]::NVarchar(50)

                $Table.Columns.Add($colField3)

               

                # Set the schema property of the table.

                $table.Schema = $schemaName

               

                # Create the table on the server

                $table.Create()

               

                Write-Host “Table: $dbName.$schemaName.$tblName created”

               

                # Explicitly disconnect because connection pooling is disabled

                Write-Debug “Disconnecting…”

                $namedInstance.ConnectionContext.Disconnect()

}

 

main

 

[EN] Sunday Market–SQL Permissions by Idera (part two)

CaptureSQLPermissionThe second part of my previous post is still about SQL Permission – free tool from Idera. In previous I wrote about installation post. In this post I’ll showhow look script from one of my database: by default: instance, all databases, all bermissions, all logins, all users. It’s small database (SQL Server 2012 (Denali) CTP3 Express Edition) but the result of script is very large: over seventy pages in MS Word document. Yay! I loose weight (freed some space by deleting sixty pages) of this file by deleting all part of the code begins with:

USE [msdb]

GO

Grant EXECUTE ON [dbo].[xxx] TO [yyy] 

GO

 

Hope it will help to reading quick and understanding this script. Full version of the script You can download from my SlideShare profile (HERE). OK, let’s go to the script, how it looks?

Part.1 | Creating special permissions with login MS_PolicyEventProcessingLogin, creating and executing query

/*********** Permissions Script for WRON023\KEYKO Login

##MS_PolicyEventProcessingLogin## Generated 2011-10-16 16:22:45 ***************/

 

 

–Scripting Server Login (password is null) and Default Database

/* For security reasons the login is created disabled and with a random password. */

IF NOT EXISTS (SELECT * FROM sys.server_principals

WHERE name = N’##MS_PolicyEventProcessingLogin##’)

BEGIN

CREATE LOGIN [##MS_PolicyEventProcessingLogin##]

WITH PASSWORD=N’îK”îΓN_g•Â_!é@ôz«7_§Oæö½_VˬU#4′,

DEFAULT_DATABASE=[master],

DEFAULT_LANGUAGE=[us_english],

CHECK_EXPIRATION=OFF,  CHECK_POLICY=ON

ALTER LOGIN [##MS_PolicyEventProcessingLogin##] DISABLE

END

 

 

–Scripting Server Roles

USE [master]

GO

 

–Grant Database Access

IF NOT EXISTS (SELECT * FROM sys.database_principals

WHERE name = N’##MS_PolicyEventProcessingLogin##’)

CREATE USER [##MS_PolicyEventProcessingLogin##]

FOR LOGIN [##MS_PolicyEventProcessingLogin##]

WITH DEFAULT_SCHEMA=[dbo]

 

–Scripting Object and Statement Permissions

USE [master]

GO

Grant CONNECT ON Database::[master] TO [##MS_PolicyEventProcessingLogin##] 

GO

 

USE [msdb]

GO

 

 

–Grant Database Access

IF NOT EXISTS (SELECT * FROM sys.database_principals

WHERE name = N’##MS_PolicyEventProcessingLogin##’)

CREATE USER [##MS_PolicyEventProcessingLogin##]

FOR LOGIN [##MS_PolicyEventProcessingLogin##]

WITH DEFAULT_SCHEMA=[dbo]

 

 

–Create Database Role

USE [msdb]

GO

IF NOT EXISTS (SELECT * FROM sys.database_principals

WHERE name = N’PolicyAdministratorRole’ AND type = ‘R’)

CREATE ROLE [PolicyAdministratorRole] AUTHORIZATION [dbo]

 

 

–Scripting Database Role Members

USE [msdb]

GO

 

exec sp_addrolemember N’PolicyAdministratorRole’, N’##MS_PolicyEventProcessingLogin##’

GO

 

 

–Create Database Role

USE [msdb]

GO

IF NOT EXISTS (SELECT * FROM sys.database_principals

WHERE name = N’SQLAgentOperatorRole’ AND type = ‘R’)

CREATE ROLE [SQLAgentOperatorRole] AUTHORIZATION [dbo]

 

USE [msdb]

GO

Grant EXECUTE ON [dbo].[sp_enum_login_for_proxy] TO [SQLAgentOperatorRole] 

GO

 

exec sp_addrolemember N’SQLAgentOperatorRole’, N’##MS_PolicyEventProcessingLogin##’

GO

 

–Create Database Role

USE [msdb]

GO

IF NOT EXISTS (SELECT * FROM sys.database_principals

WHERE name = N’SQLAgentReaderRole’ AND type = ‘R’)

CREATE ROLE [SQLAgentReaderRole] AUTHORIZATION [dbo]

 

exec sp_addrolemember N’SQLAgentReaderRole’, N’##MS_PolicyEventProcessingLogin##’

GO

 

 

–Create Database Role

USE [msdb]

GO

IF NOT EXISTS (SELECT * FROM sys.database_principals

WHERE name = N’SQLAgentUserRole’ AND type = ‘R’)

CREATE ROLE [SQLAgentUserRole] AUTHORIZATION [dbo]

 

exec sp_addrolemember N’SQLAgentUserRole’, N’##MS_PolicyEventProcessingLogin##’

GO

Part.2 | Creating special permissions with login MS_PolicyTsqlExecutionLogin: creating and executing query

 

/*********** Permissions Script for WRON023\KEYKO

Login ##MS_PolicyTsqlExecutionLogin## Generated 2011-10-16 16:22:51 ***************/

 

 

–Scripting Server Login (password is null) and Default Database

/* For security reasons the login is created disabled and with a random password. */

IF NOT EXISTS (SELECT * FROM sys.server_principals

WHERE name = N’##MS_PolicyTsqlExecutionLogin##’)

BEGIN

CREATE LOGIN [##MS_PolicyTsqlExecutionLogin##]

WITH PASSWORD=N’_á*³*óÎõÁ·¹%"_G) š¹`&]ç(æØ]þX*0_’,

DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english],

CHECK_EXPIRATION=OFF,  CHECK_POLICY=ON

ALTER LOGIN [##MS_PolicyTsqlExecutionLogin##] DISABLE

END

 

 

–Scripting Server Roles

USE [msdb]

GO

 

 

–Grant Database Access

IF NOT EXISTS (SELECT * FROM sys.database_principals

WHERE name = N’##MS_PolicyTsqlExecutionLogin##’)

CREATE USER [##MS_PolicyTsqlExecutionLogin##]

FOR LOGIN [##MS_PolicyTsqlExecutionLogin##]

WITH DEFAULT_SCHEMA=[dbo]

 

 

–Create Database Role

USE [msdb]

GO

IF NOT EXISTS (SELECT * FROM sys.database_principals

WHERE name = N’PolicyAdministratorRole’ AND type = ‘R’)

CREATE ROLE [PolicyAdministratorRole] AUTHORIZATION [dbo]

 

–Scripting Database Role Members

USE [msdb]

GO

 

exec sp_addrolemember N’PolicyAdministratorRole’, N’##MS_PolicyTsqlExecutionLogin##’

GO

 

–Create Database Role

USE [msdb]

GO

IF NOT EXISTS (SELECT * FROM sys.database_principals

WHERE name = N’SQLAgentOperatorRole’ AND type = ‘R’)

CREATE ROLE [SQLAgentOperatorRole] AUTHORIZATION [dbo]

 

exec sp_addrolemember N’SQLAgentOperatorRole’, N’##MS_PolicyTsqlExecutionLogin##’

GO

 

–Create Database Role

USE [msdb]

GO

IF NOT EXISTS (SELECT * FROM sys.database_principals

WHERE name = N’SQLAgentReaderRole’ AND type = ‘R’)

CREATE ROLE [SQLAgentReaderRole] AUTHORIZATION [dbo]

 

exec sp_addrolemember N’SQLAgentReaderRole’, N’##MS_PolicyTsqlExecutionLogin##’

GO

 

 

–Create Database Role

USE [msdb]

GO

IF NOT EXISTS (SELECT * FROM sys.database_principals

WHERE name = N’SQLAgentUserRole’ AND type = ‘R’)

CREATE ROLE [SQLAgentUserRole] AUTHORIZATION [dbo]

 

 

exec sp_addrolemember N’SQLAgentUserRole’, N’##MS_PolicyTsqlExecutionLogin##’

GO

 

–Scripting Object and Statement Permissions

USE [msdb]

GO

Grant CONNECT ON Database::[msdb] TO [##MS_PolicyTsqlExecutionLogin##] 

GO

 

Part.3 | Creating special permissions with builtin logins Users / SYSTEM / NT AUTHORITY / NT SERVICE / sa / SQLSRVReportingServices / SQLSRVAdmin / “current user”: creating and executing query

/*********** Permissions Script for WRON023\KEYKO

Login BUILTIN\Users Generated 2011-10-16 16:22:53 ***************/

 

–Scripting Server Login (password is null) and Default Database

IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = N’BUILTIN\Users’)

BEGIN

CREATE LOGIN [BUILTIN\Users] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]

END

 

 

–Scripting Server Roles

/*********** Permissions Script for WRON023\KEYKO

Login NT AUTHORITY\SYSTEM Generated 2011-10-16 16:22:55 ***************/

 

–Scripting Server Login (password is null) and Default Database

 

IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = N’NT AUTHORITY\SYSTEM’)

BEGIN

CREATE LOGIN [NT AUTHORITY\SYSTEM] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]

END

 

–Scripting Server Roles

 

exec sp_addsrvrolemember N’NT AUTHORITY\SYSTEM’, N’sysadmin’

GO

 

 

/*********** Permissions Script for WRON023\KEYKO

Login NT SERVICE\MSSQL$KEYKO Generated 2011-10-16 16:22:57 ***************/

 

 

–Scripting Server Login (password is null) and Default Database

 

IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = N’NT SERVICE\MSSQL$KEYKO’)

BEGIN

CREATE LOGIN [NT SERVICE\MSSQL$KEYKO] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]

END

 

–Scripting Server Roles

 

exec sp_addsrvrolemember N’NT SERVICE\MSSQL$KEYKO’, N’sysadmin’

GO

 

/*********** Permissions Script for WRON023\KEYKO

Login sa Generated 2011-10-16 16:22:58 ***************/

 

–Scripting Server Login (password is null) and Default Database

/* For security reasons the login is created disabled and with a random password. */

IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = N’sa’)

BEGIN

CREATE LOGIN [sa] WITH PASSWORD=N’¶„áqùm‚G·kƒj_ÂÈò)[¤ã2

ÉY_vY

%¹’, DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF,  CHECK_POLICY=ON

EXEC sys.sp_addsrvrolemember @loginame = N’sa’, @rolename = N’sysadmin’

ALTER LOGIN [sa] DISABLE

END

 

–Scripting Server Roles

exec sp_addsrvrolemember N’sa’, N’sysadmin’

GO

 

/*********** Permissions Script for WRON023\KEYKO

Login WRON023\KoprowskiT Generated 2011-10-16 16:23:00 ***************/

 

–Scripting Server Login (password is null) and Default Database

IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = N’WRON023\KoprowskiT’)

BEGIN

CREATE LOGIN [WRON023\KoprowskiT] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]

END

 

–Scripting Server Roles

 

exec sp_addsrvrolemember N’WRON023\KoprowskiT’, N’sysadmin’

GO

 

/*********** Permissions Script for WRON023\KEYKO

Login WRON023\SQLSRVAdmin Generated 2011-10-16 16:23:02 ***************/

 

–Scripting Server Login (password is null) and Default Database

IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = N’WRON023\SQLSRVAdmin’)

BEGIN

CREATE LOGIN [WRON023\SQLSRVAdmin] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]

END

 

–Scripting Server Roles

 

exec sp_addsrvrolemember N’WRON023\SQLSRVAdmin’, N’sysadmin’

GO

 

/*********** Permissions Script for WRON023\KEYKO

Login WRON023\SQLSRVReporting Generated 2011-10-16 16:23:04 ***************/

 

–Scripting Server Login (password is null) and Default Database

IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = N’WRON023\SQLSRVReporting’)

BEGIN

CREATE LOGIN [WRON023\SQLSRVReporting] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]

END

 

–Scripting Server Roles

 

USE [master]

GO

 

–Grant Database Access

IF NOT EXISTS (SELECT * FROM sys.database_principals

WHERE name = N’WRON023\SQLSRVReporting’)

CREATE USER [WRON023\SQLSRVReporting]

FOR LOGIN [WRON023\SQLSRVReporting]

WITH DEFAULT_SCHEMA=[WRON023\SQLSRVReporting]

 

–Create Database Role

USE [master]

GO

IF NOT EXISTS (SELECT * FROM sys.database_principals

WHERE name = N’RSExecRole’ AND type = ‘R’)

CREATE ROLE [RSExecRole] AUTHORIZATION [dbo]

 

 

–Scripting Database Role Members

USE [master]

GO

 

exec sp_addrolemember N’RSExecRole’, N’WRON023\SQLSRVReporting’

GO

 

 

–Scripting Object and Statement Permissions

USE [master]

GO

Grant CONNECT ON Database::[master] TO [WRON023\SQLSRVReporting] 

GO

 

USE [msdb]

GO

 

–Grant Database Access

IF NOT EXISTS (SELECT * FROM sys.database_principals

WHERE name = N’WRON023\SQLSRVReporting’)

CREATE USER [WRON023\SQLSRVReporting]

FOR LOGIN [WRON023\SQLSRVReporting]

WITH DEFAULT_SCHEMA=[WRON023\SQLSRVReporting]

 

–Create Database Role

USE [msdb]

GO

IF NOT EXISTS (SELECT * FROM sys.database_principals

WHERE name = N’RSExecRole’ AND type = ‘R’)

CREATE ROLE [RSExecRole] AUTHORIZATION [dbo]

  

 

–Scripting Database Role Members

USE [msdb]

GO

 

exec sp_addrolemember N’RSExecRole’, N’WRON023\SQLSRVReporting’

GO

 

–Create Database Role

USE [msdb]

GO

IF NOT EXISTS (SELECT * FROM sys.database_principals

WHERE name = N’SQLAgentOperatorRole’ AND type = ‘R’)

CREATE ROLE [SQLAgentOperatorRole] AUTHORIZATION [dbo]

 

exec sp_addrolemember N’SQLAgentOperatorRole’, N’WRON023\SQLSRVReporting’

GO

 

–Create Database Role

USE [msdb]

GO

IF NOT EXISTS (SELECT * FROM sys.database_principals

WHERE name = N’SQLAgentReaderRole’ AND type = ‘R’)

CREATE ROLE [SQLAgentReaderRole] AUTHORIZATION [dbo]

 

exec sp_addrolemember N’SQLAgentReaderRole’, N’WRON023\SQLSRVReporting’

GO

 

–Create Database Role

USE [msdb]

GO

IF NOT EXISTS (SELECT * FROM sys.database_principals

WHERE name = N’SQLAgentUserRole’ AND type = ‘R’)

CREATE ROLE [SQLAgentUserRole] AUTHORIZATION [dbo]

 

 

exec sp_addrolemember N’SQLAgentUserRole’, N’WRON023\SQLSRVReporting’

GO

 

–Scripting Object and Statement Permissions

USE [msdb]

GO

Grant CONNECT ON Database::[msdb] TO [WRON023\SQLSRVReporting] 

GO

 

USE [ReportServer$KEYKO]

GO

  

–Grant Database Access

IF NOT EXISTS (SELECT * FROM sys.database_principals

WHERE name = N’WRON023\SQLSRVReporting’)

CREATE USER [WRON023\SQLSRVReporting]

FOR LOGIN [WRON023\SQLSRVReporting]

WITH DEFAULT_SCHEMA=[WRON023\SQLSRVReporting]

 

–Scripting Database Role Members

USE [ReportServer$KEYKO]

GO

 

exec sp_addrolemember N’db_owner’, N’WRON023\SQLSRVReporting’

GO

 

–Create Database Role

USE [ReportServer$KEYKO]

GO

IF NOT EXISTS (SELECT * FROM sys.database_principals

WHERE name = N’RSExecRole’ AND type = ‘R’)

CREATE ROLE [RSExecRole] AUTHORIZATION [dbo]

 

exec sp_addrolemember N’RSExecRole’, N’WRON023\SQLSRVReporting’

GO

 

 

–Scripting Object and Statement Permissions

USE [ReportServer$KEYKO]

GO

Grant CONNECT ON Database::[ReportServer$KEYKO] TO [WRON023\SQLSRVReporting] 

GO

 

USE [ReportServer$KEYKOTempDB]

GO

 

 

–Grant Database Access

IF NOT EXISTS (SELECT * FROM sys.database_principals

WHERE name = N’WRON023\SQLSRVReporting’)

CREATE USER [WRON023\SQLSRVReporting]

FOR LOGIN [WRON023\SQLSRVReporting]

WITH DEFAULT_SCHEMA=[WRON023\SQLSRVReporting]

 

 

–Scripting Database Role Members

USE [ReportServer$KEYKOTempDB]

GO

 

exec sp_addrolemember N’db_owner’, N’WRON023\SQLSRVReporting’

GO

 

–Create Database Role

USE [ReportServer$KEYKOTempDB]

GO

IF NOT EXISTS (SELECT * FROM sys.database_principals

WHERE name = N’RSExecRole’ AND type = ‘R’)

CREATE ROLE [RSExecRole] AUTHORIZATION [dbo]

 

exec sp_addrolemember N’RSExecRole’, N’WRON023\SQLSRVReporting’

GO

 

–Scripting Object and Statement Permissions

USE [ReportServer$KEYKOTempDB]

GO

Grant CONNECT ON Database::[ReportServer$KEYKOTempDB] TO [WRON023\SQLSRVReporting] 

GO

 

 

/*** WARNING: SQLpermssions is freeware – use it at your own risk. ***/

/*** There are potential security issues with copying logins and permissions ***/

/*** from one SQL Server to another. By using SQLpermissions, you acknowledge ***/

/*** and knowingly accept these risks. It is recommended that users take steps ***/

/*** to thoroughly evaluate the necessity of replicating logins and permissions ***/

/*** before using this tool. Idera shall not be liable for any damages you may ***/

/*** sustain by using this tool, whether direct, indirect, special, incidental ***/

/*** or consequential, even if it has been advised of the possibility of such damages.***/