Who changed my stored procedure?

/**********************************
** I currently work in an environment
** in which developers are
** allowed to make changes
** directly to the database objects
** in SQL Server.
**
** I have always endorsed empowerment
** and as a database developer/DBA for the
** same company, I do not feel threatened
** by their continued development in an
** environment under which they have this
** level of control.
**
** The problem arrises when we (as the database
** professionals on the development side of the house)
** are required to keep track of the data model and
** all objects. In order to accomplish this feat, we must be
** explicitly aware of all changes that occur. Well...
**
** ...here's a really cool tool that records these changes
** by taking advantage of features in SQL Server 2005
** namely the databse level trigger and the EVENTDATA()
** xml data type.
**
** What follows is a script that will allow you to see
** who is making changes to your objects in SQL
** Server 2005 databases.

** To see a full explanation of this solution
** by the author: Chris Rock (follow that link!!!)
**
** Happy TSQLing
** SQLPoet
************************************************/

USE [master]
GO
CREATE DATABASE [EventsLogDB]
GO
USE [EventsLogDB]
GO
--This goes into the eventslogdb database
CREATE TABLE [dbo].[ChangeLog](
[LogId] [int] IDENTITY(1,1) NOT NULL,
[DatabaseName] [varchar] (256) ,
[EventType] [varchar](50) ,
[ObjectName] [varchar](256) ,
[ObjectType] [varchar](25) ,
[SqlCommand] [varchar](max) ,
[EventDate] [datetime] NOT NULL CONSTRAINT [DF_EventsLog_EventDate] DEFAULT (getdate()),
[LoginName] [varchar](256) NOT NULL
) ON [PRIMARY]

--Note : Our DDL trigger goes into the database to monitor and is defined as:
USE [YourMonitoredDatabase]
GO
CREATE TRIGGER backup_objects
ON DATABASE
FOR CREATE_PROCEDURE, ALTER_PROCEDURE, DROP_PROCEDURE,
CREATE_TABLE, ALTER_TABLE, DROP_TABLE,
CREATE_FUNCTION, ALTER_FUNCTION, DROP_FUNCTION
AS
SET NOCOUNT ON
DECLARE @data xml
SET @data = EVENTDATA()
INSERT INTO eventslogdb.dbo.changelog(databasename, eventtype,
objectname, objecttype, sqlcommand, loginname)
VALUES(
@data.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'varchar(256)'),
@data.value('(/EVENT_INSTANCE/EventType)[1]', 'varchar(50)'),
@data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'varchar(256)'),
@data.value('(/EVENT_INSTANCE/ObjectType)[1]', 'varchar(25)'),
@data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'varchar(max)'),
@data.value('(/EVENT_INSTANCE/LoginName)[1]', 'varchar(256)')
)
GO

read the name of the sql server agent account

As time goes on and the push to make things more dynamic continues, I find myself needing to create procedures and DTS/SSIS packs more intelligent about the environment to which it is being deployed. This is especially true now with the upgrade from earlier versions of SQL Server to SQL 2005 and soon SQL 2008.

This little script is what I use to determine the name of the sql server agent account so that I can correctly set the @owner variable of the sp_add_job procedure.

One could concievably put this into a stored procedure, if one so desired:)

Enjoy:


-- script to read the name of the sql server agent account
/**********************************************************************
-- Declare your variables...
***********************************************************************/
DECLARE @rc int,
@machine nvarchar(128),
@instance nvarchar(128),
@profile nvarchar(128),
@regpath nvarchar(256),
@account nvarchar(128)

IF (charindex(N'7.00', @@version, 0) > 0)
BEGIN
SELECT @instance = NULL
END
IF (charindex(N'8.00', @@version, 0) > 0) or (charindex(N'9.00', @@version, 0) > 0)
BEGIN
SELECT @machine = CONVERT(nvarchar(128), serverproperty('machine')),
@instance = CONVERT(nvarchar(128),serverproperty('instance'))
END
/**********************************************************************
-- Check if not running SQL Server as LocalSystem
-- @rc 0 = LocalSystem, 1 = running under account
***********************************************************************/
EXEC @rc = master.dbo.xp_MSLocalSystem @machine, @instance
IF (@rc = 0)
BEGIN
PRINT 'SQL Server is running under the LocalSystem account'
RETURN
END
/**********************************************************************
-- Get account name running SQL Server
**********************************************************************/
IF (@instance IS NULL)
BEGIN
SELECT @regpath = N'SYSTEM\CurrentControlSet\Services\SQLSERVERAGENT'
END
ELSE
BEGIN
SELECT @regpath = N'SYSTEM\CurrentControlSet\Services\SQLSERVERAGENT$' + @instance
END
EXEC master.dbo.xp_regread N'HKEY_LOCAL_MACHINE',
@regpath,
N'ObjectName',
@account OUTPUT,
N'no_output'
PRINT 'SQL Server is running under account ' + @account
RETURN
GO

In the beginning...

So today I started my first ever blog as an online repository of all of the SQL scripts I keep in a folder called Code Library on my flash drive. This venue strives to make my scripts available to me regardless of whether I remebered my keys or not. As long as I have a connection to the internet, I can get to these little nuggets of time saving splendor.

In time I hope that this can be a place for more folks to share and store their favorite scripts:)

Happy TSQLing!