SQL 在创建数据库时授予其他用户 db_owner

Question

我想设置一个 MS SQL 触发器(或其他机制)，它会自动授予某些组 db_owner 权限。

我 不要希望我们的用户拥有 db_admin 在 SQL Server 上，我只想授予它们 db_creator 但是每当有人创建数据库时，其他用户也应该有权访问该数据库。

我试过创建一个 SQL 触发器:

USE master
GO
CREATE TRIGGER trg_DDL_CreateDatabase
ON ALL SERVER
FOR CREATE_DATABASE
AS

DECLARE @databaseName varchar(max)
SET @databaseName = (SELECT EVENTDATA().value('(/EVENT_INSTANCE/DatabaseName)[1]', 'VARCHAR(255)'))

EXEC ('USE [' + @databaseName + ']' )

IF IS_MEMBER ('DOMAIN\GROUP') = 1
EXEC sp_addrolemember N'db_owner', N'DOMAIN\SOMEGROUP'
ELSE
EXEC sp_addrolemember N'db_owner', N'DOMAIN\SOMEOTHERGROUP'

GO

由于我不能使用 USE我尝试使用 EXEC ('USE [' + @databaseName + ']' ) 的触发器中的语句方法 - 但是它也不起作用。

请问有什么建议吗？

编辑:如果我可以根据组成员身份授予不同的权限，那就太棒了。例如，用户是组 A 的成员，则将 A 添加到 db_owner，如果 B，则添加 B、C 和 E 等。

Answer 1

每个数据库都被创建为 [model] 的副本.您在 [model] 中申请的任何用户、组和授权将自动复制到任何新创建的数据库中。请注意，这并不涉及附加的数据库。

如果要通过 DDL 触发器执行此操作，则需要对整个批处理使用动态 sql:

CREATE TRIGGER trg_DDL_CreateDatabase
ON ALL SERVER
FOR CREATE_DATABASE
AS
DECLARE @databaseName sysname;
SET @databaseName = (SELECT EVENTDATA().value('(/EVENT_INSTANCE/DatabaseName)[1]', 'sysname'));
declare @sql nvarchar(max);
set @sql = N'USE ' + quotename(@databaseName) + N';
IF IS_MEMBER (''DOMAIN\GROUP'') = 1
EXEC sp_addrolemember N''db_owner'', N''DOMAIN\SOMEGROUP''
ELSE
EXEC sp_addrolemember N''db_owner'', N''DOMAIN\SOMEOTHERGROUP'';';
exec sp_executesql @sql;

作为良好做法，请使用 QUOTENAME() 正确转义名称。使用 sysname对于数据库名称类型，提取(xml 方法)和存储(变量类型)。因为你的代码很容易被 db_creator 利用通过 SQL 注入(inject)将自己提升为系统管理员(创建一个适当的数据库名称并 pwn 你)。