Last week I was engaged on SQL 2000 to SQL 2005 migration project, and we had identified some issues related to significant changes in execution plans.  Before migration we tested the application with using Upgrade Advisor (test passed without any caution), but hopefully we also had made an additional “trace replace” test. During this stage it was found that some stored procedures works fine in SQL 2000 but always failed in SQL 2005. Here is a script on “how to reproduce” :

declare @xmlDoc int

 

exec sp_xml_preparedocument @xmlDoc output, '<?xml  version=''1.0'' encoding=''windows-1251'' ?><filter><first-name></first-name><mid-name></mid-name><last-name>John</last-name><table-number></table-number><login></login><active>1</active></filter>'

 

select * into #temp_xml  from openxml(@xmlDoc, '/filter')

 

exec sp_xml_removedocument @xmlDoc

 

select a.attr_id, b.attr_val_id

from

  (

      select t1.id id, cast(cast(t2.text as varchar(10)) as int) attr_id

      from #temp_xml t1

            join #temp_xml t2 on t1.id = t2.parentid

      where t1.localname = 'AttributeID'

            and t2.localname  = '#text'

  ) a

      left outer join

  (

      select t1.parentid parentid, cast(cast(t2.text as varchar(10)) as int) attr_val_id

      from #temp_xml t1

            join #temp_xml t2 on t1.id = t2.parentid

      where t1.localname = 'AttrValueID'

            and t2.localname  = '#text'

  ) b on a.id = b.parentid

where a.attr_id is not null

order by a.attr_id

 

drop table #temp_xml

 

As mentioned above, SQL 2000 gives us an empty resultset, but SQL 2005 failed with

Msg 245, Level 16, State 1, Line 14

Conversion failed when converting the varchar value 'John' to data type int.

 

We compared execution plans,  and  it was found, that the following part has different execution rule for different SQL version.

select t1.id id, cast(cast(t2.text as varchar(10)) as int) attr_id

      from #temp_xml t1

            join #temp_xml t2 on t1.id = t2.parentid

      where t1.localname = 'AttributeID'

            and t2.localname  = '#text'

 

SQL 2000 gives us the following:

  |--Compute Scalar(DEFINE:([Expr1002]=Convert(Convert([t2].[text]))))

       |--Hash Match(Inner Join, HASH:([t1].[id])=([t2].[parentid]), RESIDUAL:([t2].[parentid]=[t1].[id]))

            |--Filter(WHERE:([t1].[localname]='AttributeID'))

            |    |--Table Scan(OBJECT:([tempdb].[dbo].[aaa] AS [t1]))

            |--Filter(WHERE:([t2].[localname]='#text'))

                 |--Table Scan(OBJECT:([tempdb].[dbo].[aaa] AS [t2]))

 

SQL 2005’s version:

  |--Hash Match(Inner Join, HASH:([t1].[id])=([t2].[parentid]), RESIDUAL:([tempdb].[dbo].[aaa].[parentid] as [t2].[parentid]=[tempdb].[dbo].[aaa].[id] as [t1].[id]))

       |--Table Scan(OBJECT:([tempdb].[dbo].[aaa] AS [t1]), WHERE:([tempdb].[dbo].[aaa].[localname] as [t1].[localname]=N'AttributeID'))

       |--Compute Scalar(DEFINE:([Expr1006]=CONVERT(int,CONVERT(varchar(10),[tempdb].[dbo].[aaa].[text] as [t2].[text],0),0)))

            |--Table Scan(OBJECT:([tempdb].[dbo].[aaa] AS [t2]), WHERE:([tempdb].[dbo].[aaa].[localname] as [t2].[localname]=N'#text'))

 

As we can see SQL 2000 include two additional steps, but absolutely correct. SQL 2005 is much shorter, but absolutely incorrect and far from optimal (because of CONVERT applied to much more records than necessary). Tested with public 9.0.3159 version.

Summary: Upgrade advisor will never give you a 100% guaranty on safe migration. If possible please always collect some traces and replace it during testing phase of migration project.

In this demo i will try to explain why SET TRUSTWORTHY ON on some databases may make the sysadmin job unsafe. In previous posts i explained how db owner (or any developer) can try to implemet simplest luring attack against server sysadmin. According to BOL to avoid this problem sysadmin should switch to the context of the account/login with lowest possible privileges. Lets try to test this approach.

USE master
GO

-- create test database
CREATE DATABASE [TestDB] ON PRIMARY ( NAME = N'TestDB', FILENAME = N'C:\TestDB.mdf', SIZE = 3072KB, FILEGROWTH = 1024KB )
 LOG ON ( NAME = N'TestDB_log', FILENAME = N'C:\TestDB_log.ldf', SIZE = 1024KB, FILEGROWTH = 10%)
GO

-- set option
EXEC dbo.sp_dbcmptlevel @dbname=N'TestDB', @new_cmptlevel=90
GO

-- first time test with TRUSTWORTHY = OFF

ALTER DATABASE [TestDB] SET TRUSTWORTHY OFF
GO

-- create login Test
USE [master]
GO
CREATE LOGIN [Test] WITH PASSWORD=N'111', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO

USE [TestDB]
GO

-- create user test
CREATE USER [Test] FOR LOGIN [Test]
GO

-- create test DDL trigger - fired on any DENY event
CREATE TRIGGER [ddl_test_trigger]
ON DATABASE
FOR DENY_DATABASE
AS
 
SET NOCOUNT ON

 IF IS_SRVROLEMEMBER ('sysadmin') = 1
  PRINT 'Sysadmin found, security can be broken easily.'
 ELSE
  PRINT 'Sysadmin not found, you are in safe.'
 
GO

-- test under sysadmin (sa) account
DECLARE @cmd nvarchar(1000)

SET @cmd = 'DENY BACKUP DATABASE TO [Test]'
EXEC (@cmd)
GO

-- you'll see "Sysadmin found, security can be broken easily."

-- so the next step according to BOL - switch to lowest privileges context for desired operation
DECLARE @cmd nvarchar(1000)
SET @cmd = 'DENY BACKUP DATABASE TO [Test]'
EXEC (@cmd) AS USER = 'dbo'
GO

-- you'll see now "Sysadmin not found, you are in safe."
-- Thats is our desired goal - to be in safe. Lets try to set TRUSTWORTHY ON

ALTER DATABASE [TestDB] SET TRUSTWORTHY ON
GO

-- try again the "safe" version
DECLARE @cmd nvarchar(1000)
SET @cmd = 'DENY BACKUP DATABASE TO [Test]'
EXEC (@cmd) AS USER = 'dbo'
GO

-- you'll see again "Sysadmin found, security can be broken easily."

-- clear objects
USE Master
GO

DROP DATABASE [TestDB]
GO

DROP LOGIN [Test]
GO

--------------------------------------------------------

As as result of this demo i would recommend you do not use TRUSTWORTHY ON on your databases. Otherwise before making any
changes under 'sa' account in unknown databases please check TRUSTWORTHY setting for the database you are
working with, because simple EXECUTE AS .... (even with cookie) will not protect you from loosing you privileges or
GRANT 'sa' privileges to everybody (as explained in my previous articles). And be always in safe :-).

--------------------------------------------------------

 

Last month I’m working with a client to create something like non-standard security model. He asked for the following features:

 

-          server–level management only for sa (server admin).

-          database–level management for dbo (one or more dbo per database). Dbo is responsible for database's user management, update / refresh structure etc.; but dbo should not be able to alter database.

-          dbo as well as other database persons should not be able to make a database backup (or log file backup). This is necessary because database backups covered by Veritas software and any unwanted backup may hang database recovery process.

-          strong security, to restrict sa rights from been captured by dbo.

 

 So we’ve decided to start from server-level permissions. To capture server control somebody can run

 

GRANT CONTROL SERVER TO test_user.

 

To run this statement you should be a member of sysadmin server role, but this is not a big problem for dbo at all (see below).

To prevent form this issue happens we’ve decided to use server-level events. Lets have a look :

 

CREATE TRIGGER [ddl_restrict_any_serverlevelpermission]

ON ALL SERVER

-- i'm using event groups here, but it is possible to track events more precise.

FOR DDL_GDR_SERVER_EVENTS

AS

 

DECLARE @data XML

SET @data = EVENTDATA()

--SELECT @data

 

/*

<EVENT_INSTANCE>

  <EventType>GRANT_SERVER</EventType>

  <PostTime>2007-01-12T19:28:39.580</PostTime>

  <SPID>63</SPID>

  <ServerName>NAME</ServerName>

  <LoginName>sa</LoginName>

  <Grantor>sa</Grantor>

  <Permissions>

    <Permission>control server</Permission>

  </Permissions>

  <Grantees>

    <Grantee>test_user</Grantee>

  </Grantees>

  <AsGrantor />

  <GrantOption>0</GrantOption>

  <CascadeOption>0</CascadeOption>

  <TSQLCommand>

    <SetOptions ANSI_NULLS="ON" ANSI_NULL_DEFAULT="ON" ANSI_PADDING="ON" QUOTED_IDENTIFIER="ON" ENCRYPTED="FALSE" />

    <CommandText>GRANT CONTROL SERVER TO test_user</CommandText>

  </TSQLCommand>

</EVENT_INSTANCE>

*/

 

DECLARE @event_type nvarchar(200), @login_name sysname, @grantor sysname, @grantee sysname

 

-- actualy you can find spid here, but this value useless

SELECT @event_type = @data.value('(/EVENT_INSTANCE/EventType)[1]','sysname')

            , @login_name = @data.value('(/EVENT_INSTANCE/LoginName)[1]','sysname')

            , @grantor = @data.value('(/EVENT_INSTANCE/Grantor)[1]','sysname')

            , @grantee = @data.value('(/EVENT_INSTANCE/Grantees/Grantor)[1]','sysname')

 

-- send email to sa before you will raise error

--EXEC msdb.dbo.sp_send_dbmail @recipients='server_admin@Adventure-Works.com',

--    @subject = 'Attempt to catch server-level permission',

--    @body = @data,

--    @body_format = 'HTML'

 

-- additional features.

-- 1. we can analyze <grantor> here to add some additional logic here.

-- 2. you can analyze <grantee> list here to restrict the list of persons from been granted server control (for example, by mistake or misprint).

-- 3. for demostration purposes we will restrict any kind of server-level permissions at all.

 

RAISERROR ('GRANT/DENY/REVOKE SERVER level permission denied! Ask your server administrator.',10, 1)

ROLLBACK

 

GO

 

That’s all. This trigger will inform server admin for any attempt to grant / deny / revoke any server level permission.

 After that we will prevent dbo from altering database the same way:

 

CREATE TRIGGER [ddl_restrict_alterdatabase]

ON ALL SERVER

FOR ALTER_DATABASE, DROP_DATABASE

AS

 

DECLARE @data xml

DECLARE @trigger_name sysname, @LoginName sysname, @UserName sysname, @dbname sysname

 

SET @data = EVENTDATA()

--SELECT @data

 

/*

<EVENT_INSTANCE>

  <EventType>ALTER_DATABASE</EventType>

  <PostTime>2007-01-12T20:05:27.527</PostTime>

  <SPID>65</SPID>

  <ServerName>NAME</ServerName>

  <LoginName>sa</LoginName>

  <DatabaseName>TestSecurity</DatabaseName>

  <TSQLCommand>

    <SetOptions ANSI_NULLS="ON" ANSI_NULL_DEFAULT="ON" ANSI_PADDING="ON" QUOTED_IDENTIFIER="ON" ENCRYPTED="FALSE" />

    <CommandText>ALTER DATABASE [TestSecurity] SET RECOVERY SIMPLE WITH NO_WAIT

</CommandText>

  </TSQLCommand>

</EVENT_INSTANCE>

*/

 

-- send email to self before you will raise error

-- EXEC msdb.dbo.sp_send_dbmail @recipients='server_admin@Adventure-Works.com',

--    @subject = 'Attempt to alter database',

--    @body = @data,

--    @body_format = 'HTML'

 

-- If you want to allow ALTER DATABASE operation to some person – please use <login_name> tag and add some logic here

-- If you want to exclude your personal databases from been tracked by this trigger – please pay attention on <DatabaseName> tag.

 

RAISERROR ('ALTER DATABASE DISABLED!',10, 1)

ROLLBACK

GO

 

Also we will protect sa from any server-level authorization attemts:

 

CREATE TRIGGER [ddl_deny_alter_serverauthorization]

ON ALL SERVER

FOR DDL_AUTHORIZATION_SERVER_EVENTS

AS

 

DECLARE @data XML

SET @data = EVENTDATA()

--SELECT @data

 

-- send email to self before you will raise error

--EXEC msdb.dbo.sp_send_dbmail @recipients='server_admin@Adventure-Works.com',

--    @subject = 'Attempt to alter server-level object ownership',

--    @body = @data,

--    @body_format = 'HTML'

 

RAISERROR ('Alter server-level authorization denied. Ask your server administrator.',10, 1)

ROLLBACK

 

GO

 

That’s all for server-level section. Below we will describe some database-level triggers. To improve your audit procedures you (server administrator) can use WITH ENCRYPTION option to prevent dbo from viewing the sources.

 

The first goal is to protect database from any attempt of making backup. How we will cover this requirement:

-          sa will create a script, this script will DENY BACKUP DATABASE, BACKUP LOG for each database user. This option will restrict even dbo from making backup. Nobody from db_backupoperator will be able to make backup J. Suddenly dbo or member of db_securityadmin still be able to return this privilege back with using GRANT BACKUP DATABASE TO <account>. Lets restrict these persons from been doing that.

 

CREATE TRIGGER [ddl_denybackup_for_new_user]

ON DATABASE

FOR CREATE_USER

AS

 

DECLARE @data XML

SET @data = EVENTDATA()

--SELECT @data

 

DECLARE @user sysname, @stmt nvarchar(4000)

 

SET @user = @data.value('(/EVENT_INSTANCE/ObjectName)[1]','sysname')

--SELECT @user

 

SET @stmt = 'DENY BACKUP DATABASE, BACKUP LOG TO ' + @user

 -- here it is necessary to add some logic to check for SQL injection, sysname (nvarchar(256)) are to long. Please do that J.

 EXEC(@stmt)

 

GO

 

This trigger will deny backup privilege for any new database user. The following trigger will prevent anybody from restoring BACKUP DATABASE privilege.

 

CREATE TRIGGER [ddl_prevent_grant_events]

ON DATABASE

FOR GRANT_DATABASE

AS

 

DECLARE @data XML

SET @data = EVENTDATA()

--SELECT @data

 

DECLARE @permission sysname, @login_name sysname, @user_name sysname, @dbname sysname

            , @grantor sysname, @grantee sysname

 

SELECT @permission = @data.value('(/EVENT_INSTANCE/Permissions/Permission)[1]','sysname')

            , @login_name = @data.value('(/EVENT_INSTANCE/LoginName)[1]','sysname')

            , @user_name = @data.value('(/EVENT_INSTANCE/UserName)[1]','sysname')

            , @dbname = @data.value('(/EVENT_INSTANCE/DatabaseName)[1]','sysname')

            , @grantor = @data.value('(/EVENT_INSTANCE/Grantor)[1]','sysname')

            , @grantee = @data.value('(/EVENT_INSTANCE/Grantees/Grantee)[1]','sysname')

 

IF UPPER(@permission) in ('CONTROL', 'BACKUP DATABASE', 'BACKUP LOG')

BEGIN

 

            -- send email to self before you will raise error

            -- EXEC msdb.dbo.sp_send_dbmail @recipients='server_admin@Adventure-Works.com',

            --    @subject = 'Attempt to take database ownership',

            --    @body = @data,

            --    @body_format = 'HTML'

 

            RAISERROR ('Option restricted. Please contact system administrator.', 16, 1)

            ROLLBACK

 

END

 

That’s all what you need. You can create this trigger, log into database as dbo and check the following:

 

Use <test_security_database>

GO

 

GRANT CONTROL TO <any_user>

GRANT BACKUP DATABASE TO <any_user>

 

Suddenly hungry dbo can drop your triggers from his/her database. Lets protect our trigger for dbo too.

Be very careful to create this trigger on your production database – you will have to think how to drop it after that J.

 

CREATE TRIGGER [ddl_prevent_drop_trigger]

ON DATABASE

FOR DROP_TRIGGER, ALTER_TRIGGER

AS

 

DECLARE @data xml

DECLARE @trigger_name sysname, @LoginName sysname, @UserName sysname, @dbname sysname

 

SET @data = EVENTDATA()

 

SELECT @trigger_name = @data.value('(/EVENT_INSTANCE/ObjectName)[1]','sysname')

            , @LoginName = @data.value('(/EVENT_INSTANCE/LoginName)[1]','sysname')

            , @UserName = @data.value('(/EVENT_INSTANCE/UserName)[1]','sysname')

            , @dbname = @data.value('(/EVENT_INSTANCE/DatabaseName)[1]','sysname')

 

--SELECT [trigger_name] = @trigger_name

--          , [LoginName] = @LoginName

--          , [UserName] = @UserName

--          , [DBname] = @dbname

 

IF @trigger_name in ('ddl_prevent_drop_trigger', 'ddl_denybackup_for_new_user',

                        'ddl_prevent_grant_events')

 

            BEGIN

 

                        -- send email to self before you will raise error

                        -- EXEC msdb.dbo.sp_send_dbmail @recipients='server_admin@Adventure-Works.com',

                        --    @subject = 'Attempt to drop trigger',

                        --    @body = @data,

                        --    @body_format = 'HTML'

 

                        RAISERROR ('Please don''t touch this object!', 16, 1)

                        ROLLBACK

            END

 

-- drop all other trigger

 

GO

 

This trigger will protect self and other triggers. This technology can be used to protect important information from been deleted / altered by mistake.

 

Here is the end of successful story. You can build your additional security model with using server / database events or event groups. For more detail please have a look here and here.

 

Now I will show you how to pass through this “security” and why this kind of security doesn’t make sense (at least with using SQL Server 2005 sp1).

 Let’s assume you are dbo and you’ve decided to grant control over the server. You will create the trigger for each your database:

 

CREATE TRIGGER dbo.MyNiceTrigger

ON DATABASE

WITH ENCRYPTION

FOR ALTER_OBJECT, DROP_TRIGGER, ALTER_TRIGGER – put here all possible database events

AS

BEGIN

SET NOCOUNT ON;

IF IS_SRVROLEMEMBER ('sysadmin') = 1

begin

SET @sqlstr='use master

GRANT CONTROL SERVER TO <dbo_account> '

EXEC sp_executesql @sqlstr

End

END

GO

After that ask server admin (sa) to make any changes in your database (or wait for a while when this will happens). After sa will change something this trigger fired and…. Server-level trigger will catch your attempt. Lets improve our trigger a little. As you can see there is no event to subscribe for disable trigger action. So lets try to use it J.

 

CREATE TRIGGER dbo.MyNiceTrigger

ON DATABASE

WITH ENCRYPTION

FOR ALTER_OBJECT, DROP_TRIGGER, ALTER_TRIGGER – put here all possible database events

AS

BEGIN

SET NOCOUNT ON;

IF IS_SRVROLEMEMBER ('sysadmin') = 1

Begin

SET @sqlstr='DISABLE Trigger ALL ON ALL SERVER'

EXEC sp_executesql @sqlstr

SET @sqlstr='DISABLE Trigger ALL ON DATABASE'

EXEC sp_executesql @sqlstr

SET @sqlstr='use master

GRANT CONTROL SERVER TO <dbo_account> '

EXEC sp_executesql @sqlstr

End

END

GO

Common conclusion. Nobody can protect sa.

 

Conclusion for sa:

n      if you are working with databases – please don’t use sa privileges, please re-logon under appropriate account with minimal privileges.

n      create some audit procedures for your server. It is possible to scan triggers / procedures bodies for harm code by phrases (at least try to do that).

n      create small trigger for CREATE_TRIGGER and CREATE_PROCEDURE event in every database). Try to scan object body for potential problems.

 

Conclusion for dbo:

n      …. It’s simple. You are always winner in this situation. Hope DISABLE_TRIGGER will be in the list of allowed events soon.

As all of you know Microsoft has made a small problem for all SQL DBA and developers. If you have a database with any of SQL_xxxxx collations (for more details please have a look at the TERTIARY_WEIGHTS() function here), you will defenetily have a significant problem with index search and ordering performance if you'll try to upgrade your SQL Server with 2005 version. Some of developers was excited when first time reading this article, really :-).

Suddenly ALTER DATABASE .. ALTER COLLATION command will change database collation for you, but all objects will stay "as is" with old collation (by design). The only way to change collation for all existing objects is to create a new empty database with new desired collation and copy data into this new database (copy wizzard, for example).

To sort this issue out i created this small script (in attachment). It was tested both manually and with using VSTS DBPro, and i can confirm that you can change the collation of your database without any problem and manual work. As an input you should provide it with database with one collation, and as output you will have the same database with another (desired) collation. So as for me this is an analoque of ALTER DATABASE ... ALTER COLLATION FORCED command :-). How it works:

- you should use sqlcmd or SSMS in sqlcmd mode,

- specify database name as a parameter,

- specify desired collation as a parameter,

- run it and wait for results.

What is not covered by the script: table and index partitioning. I guess that if you are using these advanced options, you are smart enough to change this script to add required feature. Mostly i created this script for the following scenario: (1) detach database from SQL Server 6.5/7.0/200 version; (2) attach it to SQL Server 2005; (3) run this script; (4) use your database asap.

Some words about performance: than more tables with computed columns (computed column not at the end of the table) you have than more time you will have to wait. 100 Gigabyte db can be easily transformed within one hour on AI64 4way with EVA8000.

Also it is possible to use this script to study SQL 2005 system views and relationship between them.

Many thanks for any reply and comments.

 ---- January 2008

Many thanks to WaitForPete, script updated to fix these (and some other) issues.