防止用户在用户数据库中使用"dbo" SQL Server

Question

我正试图阻止使用默认模式"dbo"；在我的SQL Server数据库中。这被应用于一个现有的长期项目，该项目正在进行维护，开发人员还管理SQL Server(都是sysadmin(。

这是允许更好地跟踪代码和SQL Server对象之间的依赖关系的主要原因，这样我们就可以慢慢地迁移到更好的命名约定。例如"；dbo。用户"dbo。Projects"dbo。类别"；在DB中几乎不可能在创建后的代码中找到；dbo"经常被排除在SQL语法之外。

然而，正确定义的模式需要在代码中使用。例如"；Tracker.Users"Tracker.Projects"；，等

尽管我们制定了不使用"；dbo"；对于对象，由于管理/业务对开发速度的压力，它仍然是意外发生的。

注意：我创建这个问题只是为了提供一个其他人可以发现有用的解决方案

EDIT：正如所指出的，对于非sysadmin用户，所述的安全选项是一个可行的解决方案，但是DDL触发器解决方案也适用于sysadmin用户。对于许多不得不在那里管理自己箱子的小团队来说，情况就是这样

Answer 1

我觉得在dbo模式上只使用DENY ALTER会简单10000倍：

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

如果每个人都以sa连接，那就不太方便了，但首先修复。

Answer 2

以下数据库DLL触发器会在SQL Manager GUI中引起错误反馈，并通过手动TSQL代码尝试为指定类型创建对象。

它包括一种拥有特殊用户的方式，并为尝试创建对象的用户提供清晰的反馈。它还可以向系统管理员用户提出错误。

它不影响现有对象，除非GUI/SQL尝试DROP和CREATE一个现有的"；dbo"；基于对象。

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