Click here to Skip to main content
Click here to Skip to main content
Technical Blog

Tagged as

SQL Server: Placing Alert for Compatibility Level Change in SQL 2005

, 22 Oct 2012 CPOL
Rate this:
Please Sign up or sign in to vote.
In SQL Server 2005 there is no way to find out WHO AND WHEN someone change compatibility level of a database. Here is a trick for this functionality.
Microsoft SQL Server allows its users to keep behavior of a database compatible to its older versions. Like, if someone is using “*=” type of left outer joins in some quires/Stored Procedures as she created it for SQL Server 2000. Though such join are not allowed in SQL Server 2005 and subsequent versions but one still can keep database behavior as SQL Server 2000 by keeping its compatibility level to 80.

Recently, a client reported that someone (DBA or Application) is changing his database compatibility, which should remain compatible to SQL Server 2000 (compatibility level 80). He wants to know at what time this change is being made.

SQL Server 2008 and subsequent versions keep record of this compatibility change to its log, but SQL Server 2005 has no such facility. It means, in SQL Server 2005, you never know when someone has changed compatibility level.

In SQL Server 2008 and subsequent versions one can change compatibility level of a database by following simple TSql statement.

ALTER DATABASEAdventureWorks SET COMPATIBILITY_LEVEL= 90;

But in SQL Server 2005, only method to change this compatibility level is its system stored procedure i.e., sys.sp_dbcmptlevel. SQL Profiler is the only place where you can trace when this stored procedure was executed. But what if, we need to place an alert for this change and generate a mail for this change. Or what if, we need to stop users/applications to change a database compatibility level.

Only way to achieve this functionality is to update system stored procedure  sp_dbcmptlevel.

Let's perform this task, step by step.

Step 1: Stop SQL Server 2005 services

 

Step 2:  Login using DAC (Dedicated Administrative Connection). For this right click on SQL Server 2005 service, on Advanced tab, change startup parameters by adding -m; at existing values. 

Step 3: Start SQL Server 2005 services

 

Step 4: Open SQL Server Management Studio and open Database Engine Query

Step 5: Login as valid sysadmin user or ADMIN:InstanceName 

 

Step 6: Change mssqlsystemresource database to read_write mode

 

Step 7: It’s the time to update our system stored procedure i.e. sp_dbcmptlevel. If you need to keep only compatibility level to 80 or 90 then change following lines of stored procedures with same values i.e.80 or 90 or as per your choice.

select  @cmptlvl60 =60,
@cmptlvl60 =65,
@cmptlvl60 = 70,
@cmptlvl60 = 80,
@cmptlvl60 = 90,
And if you also need to add a mail alert for this change then add following code in error control portion of stored procedure.
DECLARE @bodyText VARCHAR(200)
SET @bodyText='User '
+ CONVERT(VARCHAR,SYSTEM_USER)
+' trying to change Compatibility Level of Database ' + CONVERT(VARCHAR,@dbname)
+ ' at '
 + CAST(GETDATE() AS VARCHAR(50))
EXEC msdb.dbo.sp_send_dbmail @recipients='essmess@gmail.com;', --Change Email Address Accordingly
@subject = 'Compatibility Level Change Alter',
@profile_name = 'DBTeam', --Change DB mail Profile Accordingly
@body = @bodyText,
@body_format = 'TEXT' ;
Here is complete updated script of stored procedure. (This script is only applicable to SQL Server 2005, for SQL Server 2008 and subsequent version, its totally different, which you can get by sp_helptext).
USE mssqlsystemresource
GO
alter procedure sys.sp_dbcmptlevel     
 @dbname sysname = NULL,     -- database name to change  
 @new_cmptlevel tinyint = NULL OUTPUT -- the new compatibility level to change to  
as  
 set nocount    on  
  
 declare @exec_stmt nvarchar(max)  
 declare @returncode int  
 declare @comptlevel float(8)  
 declare @dbid int     -- dbid of the database  
 declare @dbsid varbinary(85)  -- id of the owner of the database  
 declare @orig_cmptlevel tinyint  -- original compatibility level  
 declare @input_cmptlevel tinyint -- compatibility level passed in by user  
  ,@cmptlvl60 tinyint    -- compatibility to SQL Server Version 6.0  
  ,@cmptlvl65 tinyint    -- compatibility to SQL Server Version 6.5  
  ,@cmptlvl70 tinyint    -- compatibility to SQL Server Version 7.0  
  ,@cmptlvl80 tinyint    -- compatibility to SQL Server Version 8.0  
  ,@cmptlvl90 tinyint    -- compatibility to SQL Server Version 9.0  
--Only compatablility level 80 should be allowed to users and applications for whole instance
 select  @cmptlvl60 = 80,  
   @cmptlvl65 = 80,  
   @cmptlvl70 = 80,  
   @cmptlvl80 = 80,  
   @cmptlvl90 = 80  
  
 -- SP MUST BE CALLED AT ADHOC LEVEL --  
 if (@@nestlevel > 1)  
 begin  
  raiserror(15432,-1,-1,'sys.sp_dbcmptlevel')  
  return (1)  
 end  
  
 -- If no @dbname given, just list the valid compatibility level values.  
 if @dbname is null  
 begin  
    raiserror (15048, -1, -1, @cmptlvl60, @cmptlvl65, @cmptlvl70, @cmptlvl80, @cmptlvl90)  
    return (0)  
 end  
  
 --  Verify the database name and get info  
 select @dbid = dbid, @dbsid = sid ,@orig_cmptlevel = cmptlevel  
  from master.dbo.sysdatabases  
  where name = @dbname  
  
 --  If @dbname not found, say so and list the databases.  
 if @dbid is null  
 begin  
  raiserror(15010,-1,-1,@dbname)  
  print ' '  
  select 'Available databases:' = name  
   from master.dbo.sysdatabases  
  return (1)  
 end  
  
 -- Now save the input compatibility level and initialize the return clevel  
 -- to be the current clevel  
 select @input_cmptlevel = @new_cmptlevel  
 select @new_cmptlevel = @orig_cmptlevel  
  
 -- If no clevel was supplied, display and output current level.  
 if @input_cmptlevel is null  
 begin  
  raiserror(15054, -1, -1, @orig_cmptlevel)  
  return(0)  
 end  
  
 -- If invalid clevel given, print usage and return error code  
 -- 'usage: sp_dbcmptlevel [dbname [, compatibilitylevel]]'  
 if @input_cmptlevel not in (@cmptlvl60, @cmptlvl65, @cmptlvl70, @cmptlvl80, @cmptlvl90)  
 begin  
-- Send an email alert
DECLARE @bodyText VARCHAR(200)
SET @bodyText='User ' 
					+ CONVERT(VARCHAR,SYSTEM_USER) +' trying to change Compatibility Level of Database '
					+ CONVERT(VARCHAR,@dbname)
					+ ' at '
					+ CAST(GETDATE() AS VARCHAR(50))
EXEC msdb.dbo.sp_send_dbmail @recipients='essmess@gmail.com;', --Change Email Address Accordingly
@subject = 'Compatibility Level Change Alter',
@profile_name = 'DBTeam', --Change DB mail Profile Accordingly
@body = @bodyText,
@body_format = 'TEXT' ;

  raiserror(15416, -1, -1)  
  print ' '  
  raiserror (15048, -1, -1, @cmptlvl60, @cmptlvl65, @cmptlvl70, @cmptlvl80, @cmptlvl90)  
  return (1)  
 end  
  
 -- We should not allow the user to change the compatibility level if there exists IV or ICC  
 if @orig_cmptlevel >= @cmptlvl80 and @input_cmptlevel < @cmptlvl80  
 begin  
  -- CHECK FOR INDEXED VIEWS OR INDEXED COMPUTED-COLUMNS  
  if exists (select * from sysobjects where xtype = 'V' and id in (select id from sysindexes)) or  
   exists (select * from sysobjects o join sysindexkeys k on o.id=k.id  
    where o.xtype = 'U' and ColumnProperty(k.id, col_name(k.id, k.colid), 'IsComputed') = 1)  
  begin  
   -- Cannot set compat mode because database has a view or computed column that is indexed.  
   -- These indexes require an 8.0-compatible database.  
   raiserror(15414, -1, -1)  
   return (1)  
  end  
 end  
  
 --  Only the SA or the dbo of @dbname can execute the update part  
 --  of this procedure sys.so check.  
 if (not (is_srvrolemember('sysadmin') = 1)) and suser_sid() <> @dbsid  
  -- ALSO ALLOW db_owner ONLY IF DB REQUESTED IS CURRENT DB  
  and (@dbid <> db_id() or is_member('db_owner') <> 1)  
 begin  
  raiserror(15418,-1,-1)  
  return (1)  
 end  
  
 -- We should not allow the user to change the compatibility level for  
 -- replicated or distributed databases  
 select @comptlevel = (case @input_cmptlevel  
    when 60 then 6.0  
    when 65 then 6.5  
    when 70 then 7.0  
    when 80 then 8.0  
    when 90 then 9.0  
    end)  
  
 EXEC @returncode = master.dbo.sp_MSreplicationcompatlevel @dbname, @comptlevel  
  
 if @returncode <> 0  
 begin  
  raiserror(15306, -1, -1)  
  return (1)  
 end  
  
 -- If we're in a transaction, disallow this since it might make recovery impossible.  
 set implicit_transactions off  
 if @@trancount > 0  
 begin  
  raiserror(15002,-1,-1,'sys.sp_dbcmptlevel')  
  return (1)  
 end  
  
 -- Note: database @dbname may not exist anymore  
 -- Change compatibility level  
 -- If invoke gets error, exception will abort this proc.  
 EXEC %%DatabaseEx(Name = @dbname).SetCompatibility(Level = @input_cmptlevel)  
  
 -- Checkpoint the database that was changed.  
 select @exec_stmt = 'use ' +  quotename(@dbname, '[')   + ' checkpoint'  
 EXEC(@exec_stmt )  
  
 -- If checkpoint unsuccessful, restore the old compatibility level,  
 --  otherwise update output clevel and flush all the SPs of this database from the cache  
 if (@@error <> 0)  
 begin  
  -- If invoke gets error, exception will abort this proc.  
  EXEC %%DatabaseEx(Name = @dbname).SetCompatibility(Level = @orig_cmptlevel)  
  return (1)  
 end  
  
 dbcc flushprocindb(@dbid)  
 select @new_cmptlevel = @input_cmptlevel  
  
 return (0) -- sp_dbcmptlevel  
Step 8: Change mssqlsystemresource database to read_only mode

  

Step 9: Close SSMS session, stop SQL Server services and change its startup parameters back to normal.

  

Step 10: Start SQL Server Services and you are done.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

aasim abdullah
Team Leader CureMD
Pakistan Pakistan
Aasim Abdullah is working as SQL Server DBA with CureMD (www.curemd.com) based in NY, USA. He has been working with SQL Server since 2007 (Version 2005) and has used it in many projects as a developer, administrator, database designer. Aasim's primary interest is SQL Server performance tuning. If he finds the time, he like to sketch faces with graphite pencils.
Follow on   Twitter   Google+

Comments and Discussions

 
-- There are no messages in this forum --
| Advertise | Privacy | Terms of Use | Mobile
Web04 | 2.8.141030.1 | Last Updated 22 Oct 2012
Article Copyright 2012 by aasim abdullah
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid