Adding Bulk Rights SQL 2005

Was trying to do some quick administration and realized the SQL Server 2005 had a different method than 2012. Do not put any [] brackets in the user name. However, make sure you put brackets around the DB name especially with Sharepoint to handle the dashed in the DB name.


DECLARE @cmd nvarchar(255);

--first create user
SET @cmd = 'IF ''[?]'' NOT IN(''master'', ''model'', ''msdb'', ''tempdb'') 
BEGIN USE [?] CREATE USER [domain\user] FOR LOGIN [domain\user]''  END';

EXEC master.sys.sp_MSforeachdb  @command1 = @cmd

--second assign role
SET @cmd = 'IF ''[?]'' NOT IN(''master'', ''model'', ''msdb'', ''tempdb'') 
BEGIN USE [?] exec sp_addrolemember ''db_owner'', ''Domain\User''  END';

EXEC master.sys.sp_MSforeachdb  @command1 = @cmd

Reporting Services and xml

I am currently struggling to create method of scheduling Reporting service subscriptions without having that stupid GUID as the job name. First I needed to learn more about querying the dubiously formatted Reporting services xml.

USE [ReportServer];

/****** Script for SelectTopNRows command from SSMS  ******/

WITH cteSubs (SubscriptionId, Params) AS
(
SELECT
SubscriptionID
,  CAST(CAST([ExtensionSettings] AS NVARCHAR(max)) AS XML) AS params
  FROM [dbo].[Subscriptions] subs
  INNER JOIN [dbo].[Schedule] sch
  ON subs.SubscriptionID = sch.[EventData]
 )
SELECT
SubscriptionID
,(SELECT nref.value('Value[1]', 'nvarchar(50)') Comment FROM Params.nodes('/ParameterValues/ParameterValue') AS R(nref) WHERE  nref.exist('.[Name = "Comment"]') = 1) AS Comment
 FROM cteSubs

This extracts the value of the comment so I can use it for the Job Name later

Sql Auditing Changes Table Design

/*
This is the start of documenting the process by which I collect information about changes in the database. This is done through ddl triggers and I keep a copy of all of the changes. This step is creating an audit table in the AuditDB. I always give myself one database on every server to collect this kind of information. The later posts will have the stored procedure to populate this table
*/

USE [AuditDB]
GO
/****** Object: Table [dbo].[Common_SP_Events] Script Date: 06/02/2009 16:48:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Common_SP_Events](
[EventType] [nvarchar](25) NULL,
[EventTime] [datetime] NULL CONSTRAINT [DF_Common_SP_Events_EventTime] DEFAULT (getdate()),
[LoginName] [nvarchar](50) NULL,
[UserName] [nvarchar](25) NULL,
[ObjectName] [nvarchar](50) NULL,
[ObjectType] [nvarchar](25) NULL,
[Command] [nvarchar](max) NULL,
[FullFile] [xml] NULL
) ON [PRIMARY];

CREATE CLUSTERED INDEX [cdx_Common_SP_Events_EventTime] ON [dbo].[Common_SP_Events]
(
[EventTime] DESC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF
, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON
, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY];

SQL 2005 Database Mail

I put this here so I have a standard template for sending mail. This script assumes there is a public email account with the name [Instance Name]Admin to send the email. The coalesce statement first checks if there is an instance name. The function will return NULL is it is the default. The second value will then insert the machine name to generate the user name. This is included to allow the same email script to be used on multiple servers especially test and production with no changes to the script.

There are many more options i

DECLARE @profile varchar(100)
DECLARE @EmailTo varchar(128)
DECLARE @Subjectvarchar(128)
DECLARE @Body varchar(max)
DECLARE @BodyType varchar(25)

SELECT @profile = CAST(COALESCE(SERVERPROPERTY ( 'InstanceName' ), SERVERPROPERTY ( 'ServerName')) AS varchar(80)) + 'Admin'
SET @EmailTo = 'address@domain.com'
SET @Subject = 'test'
SET @Body = 'test'
SET @BodyType = 'HTML' --TEXT

EXEC msdb.dbo.sp_send_dbmail
@profile_name = @profile,
@recipients = @EmailTo ,
@subject = @Subject,
@body = @Body ,
@body_format = @BodyType


----------------
Now playing: NPR - 0905136: NPR: 05-16-2009 Wait Wait... Don't Tell Me!

SP2 Upgrade Issue

Had an issue while updating SQL Server 2005 RTM to SP2. The SISS packages would start and then go into a weird suspended mode.

The history error message was:
Step 1 of job 'Inventory Reconciliation Job' (0x2070EF085C1D99489E13145AC7D55282) cannot be run because the SSIS subsystem failed to load. The job has been suspended.

The answer was :

http://support.microsoft.com/?kbid=914171

The SQL that fixed it was:
use msdb
go

select * from msdb.dbo.syssubsystems;
/*
--useful bit commented out here
delete from msdb.dbo.syssubsystems
exec msdb.dbo.sp_verify_subsystems 1
*/
go