Prevent User Usage of "dbo" in User Databases SQL Server-CodePudding

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.

CodePudding user response：

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.

CodePudding user response：

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)[email protected]('(/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)[email protected]('(/EVENT_INSTANCE/SchemaName)[1]', 'NVARCHAR(2000)');
DECLARE @ObjectName nvarchar(2000)[email protected]('(/EVENT_INSTANCE/ObjectName)[1]', 'NVARCHAR(2000)');
DECLARE @LoginName nvarchar(2000)[email protected]('(/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