-1

I am attempting to prevent usage of the default schema of "dbo" in my SQL Server databases. This is being applied to an existing long term project with ongoing maintenance where the developers also manage the SQL Server (are all sysadmin).

This is for the main reason to allow better dependency tracking between code and the SQL Server objects so that we can slowly migrate to a better naming convention. Eg. "dbo.Users", "dbo.Projects", "dbo.Categories" in a DB are nearly impossible to find in code once created because the "dbo." is often left out of SQL Syntax.

However a proper defined schema requires the usage in code. Eg. "Tracker.Users", "Tracker.Projects", etc ...

Even though we have standards set to not use "dbo" for objects it is still accidentally occurring due to management/business pressures for speed to develop.

Note: I'm creating this question simply to provide a solution someone else can find useful

EDIT: As pointed out, for non-sysadmin users the security option stated is a viable solution, however the DDL Trigger solution will also work on sysadmin users. The case for many small teams who have to manage there own boxes.

DarrenMB
  • 2,342
  • 1
  • 21
  • 26

2 Answers2

0

The following Database DLL Trigger causes error feedback in both the SQL Manager GUI and via Manual TSQL code attempts to create an object for the types specified.

It includes a means to have a special user and provides clear feedback to the user attempting the object creation. It also works to raise the error with users who are sysadmin.

It does not affect existing objects unless the GUI/SQL tries to DROP and CREATE an existing "dbo" based object.

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TRIGGER [CREATE_Prevent_dbo_Usage_2] ON DATABASE 
    FOR CREATE_TABLE, CREATE_VIEW, CREATE_PROCEDURE, CREATE_FUNCTION
AS 

DECLARE @E XML = EVENTDATA();

DECLARE @CurrentDB nvarchar(200)=@E.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'NVARCHAR(2000)');
DECLARE @TriggerFeedbackName nvarchar(max)=@CurrentDB+N'.CREATE_Prevent_dbo_Usage'; -- used to feedback the trigger name on a failure so the user can disable it (or know where the issue is raised from)

DECLARE @temp nvarchar(2000)='';
DECLARE @SchemaName nvarchar(2000)=@E.value('(/EVENT_INSTANCE/SchemaName)[1]', 'NVARCHAR(2000)');
DECLARE @ObjectName nvarchar(2000)=@E.value('(/EVENT_INSTANCE/ObjectName)[1]', 'NVARCHAR(2000)');
DECLARE @LoginName nvarchar(2000)=@E.value('(/EVENT_INSTANCE/LoginName)[1]', 'NVARCHAR(2000)');

DECLARE @CurrentObject nvarchar(200)=''; -- Schema.Table

IF @LoginName NOT IN ('specialUser') BEGIN -- users to exclude in evaluation.
    IF CASE WHEN @SchemaName IN ('dbo') THEN 1 ELSE 0 END = 1 BEGIN -- is a DBO attempt to create.
        SET @CurrentObject = @SchemaName+'.'+@ObjectName; -- grouped here for easy cut and paste/modify.
        SET @temp='Cannot create "'+@CurrentObject+'".
This Database "'+@CurrentDB+'" has had creation of "dbo" objects restricted to improve code maintainability.
Use an existing schema or create a new one to group the purpose of the objects/code.
Disable this Trigger TEMPORARILY if you need to do some advanced manipulation of it.
(This message was produced by "'+@TriggerFeedbackName+'")';
        throw 51000,@temp,1;
    END
END

GO

ENABLE TRIGGER [CREATE_Prevent_dbo_Usage] ON DATABASE
GO
DarrenMB
  • 2,342
  • 1
  • 21
  • 26
0

I feel like it would be 10,000 times simpler to just DENY ALTER on the dbo schema:

DENY ALTER ON SCHEMA::dbo TO [<role(s)/user(s)/group(s)>];

That's not too handy if everyone connects as sa but, well, fix that first.

Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490
  • If you were creating a fresh Database from scratch then that is an option. However on a long term solution that is undergoing allot of refactoring along side new development with hundreds of users the DDL approach can provide more clear feedback while the migration is done. I totally get your point though. – DarrenMB Apr 05 '22 at 16:38
  • @DarrenMB Nah, the stated problem is "we don't want developers doing X" - if the actual problem is "we only want developers doing X some of the time" that's different, but still is solvable in a simpler way (if they do need to deploy or change an object in dbo then you make a process for that, e.g. _you_ run it or some API runs as an elevated user after the change gets approved). You can't fix this in any way (DDL trigger, role-based permissions, or anything else) if you don't really want to fix it. – Aaron Bertrand Apr 05 '22 at 16:41
  • 1
    @DarrenMB to be honest if it's that narrow of a use case it sounds more like a blog post than a Q & A. – Aaron Bertrand Apr 05 '22 at 16:46
  • Just tried your solution. It doesn't work on people with sysadmin role. They do not have user entries at the database level. – DarrenMB Apr 05 '22 at 16:51
  • It also doesn't work for sysadmin users who do have logins at the database level. – DarrenMB Apr 05 '22 at 16:54
  • So, not everyone has the resources or time allocation for dedicated "sa" people. You also cannot have 2 domain NT accounts assigned to the same person. So if that person needs to be SA then they are always SA unless we start creating SQL Server accounts for SA access only (has it's own set of issues). – DarrenMB Apr 05 '22 at 17:05
  • That's why I said in my answer `That's not too handy if everyone connects as sa but, well, fix that first.` – Aaron Bertrand Apr 05 '22 at 17:27