觸發器限制使用者在數據庫中創建新表

如何創建限制任何使用者在數據庫中創建新表的 SQL Server 觸發器？

請幫我解決一下這個。

謝謝和問候， Ritesh。

如果你真的必須使用觸發器，你可以使用數據庫觸發器。

CREATE TRIGGER NoCreateTable ON DATABASE 
   FOR CREATE_TABLE
AS 
PRINT 'You may not create a table' 
ROLLBACK TRANSACTION

我知道這是一篇舊文章，但我們有一個類似的場景，我們授予開發人員控制數據庫權限，但我們想阻止他們管理表。所以我想出了這個伺服器觸發器。為將來訪問此文章的任何人發帖：

USE [master]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


/***
Create Date: 2017-03-01
Author: Tim Cartwright
Description: |-
   Block users from being able to alter tables or w/e else as we grant them control rights.
   https://msdn.microsoft.com/en-us/library/bb510452.aspx DDL Event Groups
Revisions:
 - Revision Date: 2019-04-23
   Author: Matthew Naul    
   Description: added handling for DDL Admin role  
***/
ALTER TRIGGER [block_user_modifications]
   ON ALL SERVER 
   FOR DDL_TABLE_EVENTS
   AS
BEGIN
   DECLARE @data XML  
   SET @data = EVENTDATA() 

   DECLARE @DbName nvarchar(128) = @data.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'nvarchar(128)')

   -- ignore any databases that you allow them to play in
   IF @DbName = 'PlayGround' RETURN;
   
   -- seperated the rights out into seperate output parameters just in case we wish to do something different with them.
   DECLARE @sql nvarchar(MAX) = 'USE [' + @DbName + ']; SELECT @db_owner = IS_ROLEMEMBER (''db_owner''), @db_ddladmin = IS_ROLEMEMBER (''db_ddladmin'')',
       @db_owner int, @db_ddladmin int;

   EXEC sys.sp_executesql @stmt=@sql, @params = N'@db_owner int output, @db_ddladmin int output', @db_owner = @db_owner OUTPUT, @db_ddladmin = @db_ddladmin OUTPUT
   
   IF (IS_SRVROLEMEMBER('sysadmin') + @db_owner + @db_ddladmin) = 0 BEGIN

       -- https://msdn.microsoft.com/en-us/library/ms187909.aspx Use the EVENTDATA Function
       DECLARE @action varchar(50), 
           @schema_name nvarchar(255),
           @object_name nvarchar(255)

       SELECT @action = @data.value('(/EVENT_INSTANCE/EventType)[1]', 'varchar(50)'),
           @schema_name = @data.value('(/EVENT_INSTANCE/SchemaName)[1]',  'NVARCHAR(255)'),
           @object_name = @data.value('(/EVENT_INSTANCE/ObjectName)[1]',  'NVARCHAR(255)');

       DECLARE @ErrorMsg varchar(1024) = 'User (' + SUSER_NAME() + ') does not have the appropriate rights to perform the ' + @action 
           + ' action on object ' + @DbName + '.' + @schema_name + '.' + @object_name + '. Please contact your DBA.';

       ROLLBACK;
       RAISERROR ( 'DDL Trigger: %s', 18, 1, @ErrorMsg) 
           WITH 
               LOG,        -- Make sure this error is in the SQL Log
               NOWAIT,     -- Return the error now
               SETERROR;   -- Sets @@ERROR and ERROR_NUMBER
   END
END

GO

引用自：https://dba.stackexchange.com/questions/60266