Today’s script is one I use as an example to explain that there is method behind my standards that some people initially see as madness. I’m sure some people think I am trying to make their job harder and that I get a kick out of telling them to “go away and do it again properly”. The answer is yes I do get a kick out of it, but not because I am telling them they are wrong because it proves that the standards in place are providing value to the business by adding a layer of protection.
Let me explain the script below. I create two schemas, two tables (one in each schema) and populate both with one record. I then create two logins and two users, each user has a different default schema (one of each of the schemas created earlier). These objects are to simply support the example.
Now lets assume that SchemaBlogUser1 is a developer and SchemaBlogUser2 is a DBA the process that I am trying to demonstrate is;
Developer creates and submits a script
DBA executes the script
Problems arrise with users reporting errors in the application
DBA investiagtes the issue
What the DBA ultimately finds is that because the script the developer submitted did not qualify the table with a schema in the check or the drop statement SQL Server used the default schema of the DBA which was different to that of the developer and ultimately dropped the wrong table!
It is important to remember that standards are there to protect everyone, yes they can make life more difficult in the now, but can save a lot of time and headaches in future, and that includes DBA’s!!!
/*
-----------------------------------------------------------------
Object Qualification
-----------------------------------------------------------------
For more SQL resources, check out SQLServer365.blogspot.co.uk
-----------------------------------------------------------------
You may alter this code for your own purposes.
You may republish altered code as long as you give due credit.
You must obtain prior permission before blogging this code.
THIS CODE AND INFORMATION ARE PROVIDED "AS IS"
-----------------------------------------------------------------
*/
USE SQLServer365
GO
-- Create test schemas
CREATE SCHEMA SchemaBlog1 AUTHORIZATION dbo;
GO
CREATE SCHEMA SchemaBlog2 AUTHORIZATION dbo;
GO
-- Create test tables
CREATE TABLE SchemaBlog1.Table1
(
Table1ID INT IDENTITY (1,1),
Column1 VARCHAR(50)
);
GO
CREATE TABLE SchemaBlog2.Table1
(
Table1ID INT IDENTITY (1,1),
Column1 VARCHAR(50)
);
GO
-- Insert test data
INSERT INTO SchemaBlog1.Table1
VALUES ('This table is in the SchemaBlog1 schema');
GO
INSERT INTO SchemaBlog2.Table1
VALUES ('This table is in the SchemaBlog2 schema');
GO
-- Create Logins
USE [master]
GO
CREATE LOGIN [SchemaBlogUser1] WITH PASSWORD=N'SchemaBlogUser1', DEFAULT_DATABASE=[SQLServer365], DEFAULT_LANGUAGE=[British], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF;
GO
CREATE LOGIN [SchemaBlogUser2] WITH PASSWORD=N'SchemaBlogUser2', DEFAULT_DATABASE=[SQLServer365], DEFAULT_LANGUAGE=[British], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF;
GO
-- Create users
USE [SQLServer365];
GO
CREATE USER [SchemaBlogUser1] FOR LOGIN [SchemaBlogUser1];
GO
ALTER USER [SchemaBlogUser1] WITH DEFAULT_SCHEMA=[SchemaBlog1];
GO
EXEC sp_addrolemember N'db_owner', N'SchemaBlogUser1';
GO
CREATE USER [SchemaBlogUser2] FOR LOGIN [SchemaBlogUser2];
GO
ALTER USER [SchemaBlogUser2] WITH DEFAULT_SCHEMA=[SchemaBlog2];
GO
EXEC sp_addrolemember N'db_owner', N'SchemaBlogUser2';
GO
-- SchemaBlogUser1 created the below script to drop table1 -- DO NOT RUN THIS PART!
IF EXISTS ( SELECT 1
FROM sys.objects
WHERE [object_id] = OBJECT_ID('Table1')
AND [type] = 'U' )
DROP TABLE Table1;
GO
/*
Connect to the instance of SQL Server as SchemaBlogUser2 to run the script
*/
-- Sets the execution context to SchemaBlogUser2 (to mimic the user executing the script)
EXECUTE AS USER = 'SchemaBlogUser2';
GO
-- Drop the table
IF EXISTS ( SELECT 1
FROM sys.objects
WHERE [object_id] = OBJECT_ID('Table1')
AND [type] = 'U' )
DROP TABLE Table1;
GO
-- Revert the execution context (just to show the syntax)
REVERT;
GO
-- Set the execution context to SchemaBlogUser2 (to mimic the user executing the script)
EXECUTE AS USER = 'SchemaBlogUser2';
GO
-- Quick check of record count in SchemaBlog2.Table1
SELECT
COUNT(*)
FROM SchemaBlog2.Table1;
GO
/*
Msg 208, Level 16, State 1, Line 1
Invalid object name 'SchemaBlog2.Table1'.
*/
-- 0 Records
SELECT
*
FROM
sys.objects
WHERE
[object_id] = OBJECT_ID('Table1')
AND [type] = 'U';
GO
-- 1 Record
SELECT
*
FROM
sys.objects
WHERE
[object_id] = OBJECT_ID('SchemaBlog1.Table1')
AND [type] = 'U';
GO
-- 0 Records - Wrong table has been dropped!
SELECT
*
FROM
sys.objects
WHERE
[object_id] = OBJECT_ID('SchemaBlog2.Table1')
AND [type] = 'U';
GO
-- The script should have read!
IF EXISTS ( SELECT 1
FROM sys.objects
WHERE [object_id] = OBJECT_ID('SchemaBlog1.Table1')
AND [type] = 'U' )
DROP TABLE Table1;
GO
Enjoy!
Chris
