-------------- 测试表 Begin ----------------
--这里的测试库为 tempdb
--弄清楚之后,修改相关代码,再上正式生产环境操作
USE tempdb
GO
IF EXISTS(SELECT * FROM sys.triggers AS t WHERE t.name='TR_Safety_Table')
BEGIN
DROP TRIGGER TR_Safety_Table ON DATABASE;
END
GO
IF OBJECT_ID('dbo.t1') IS NOT NULL
DROP TABLE dbo.t1
GO
CREATE TABLE dbo.t1(
id INT IDENTITY(1,1) PRIMARY KEY,
[Type] INT
)
GO
SET NOCOUNT ON
INSERT INTO dbo.t1([Type]) VALUES(1)
INSERT INTO dbo.t1([Type]) VALUES(2)
GO
-------------- 测试表 End ----------------
--1. 创建登录名
USE [master]
GO
--删除所有 testUser 的连接
DECLARE @sql NVARCHAR(MAX)
SELECT @sql=ISNULL(@sql,'')+'kill '+CAST(s.spid AS VARCHAR(50))+'
' FROM sys.sysprocesses AS s WHERE s.spid>50 AND s.loginame='testUser'
EXEC(@sql)
IF EXISTS(SELECT * FROM sys.syslogins AS s WHERE NAME='testUser')
BEGIN
DROP LOGIN [testUser]
END
GO
CREATE LOGIN testUser WITH PASSWORD=N'testUser', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
--2. 进入需要授权的库,创建用户名并授权
USE tempdb
GO
IF EXISTS(SELECT 1 FROM sys.database_principals WHERE NAME='testUser' AND type_desc='SQL_USER')
DROP USER testUser
GO
CREATE USER testUser FOR LOGIN testUser
GO
EXEC sp_addrolemember N'db_owner', N'testUser'
GO
DENY DELETE,ALTER ON dbo.t1 TO testUser
GO
IF EXISTS(SELECT * FROM sys.triggers AS t WHERE t.name='TR_Safety_Table')
BEGIN
DROP TRIGGER TR_Safety_Table ON DATABASE;
END
GO
-- =============================================
-- Author:
-- Create date: <2010-04-03>
-- Description: <除了sa用户,禁止其他数据库用户 ALTER TABLE 和DROP TABLE的权限>
-- =============================================
CREATE TRIGGER TR_Safety_Table
ON DATABASE
FOR DROP_TABLE, ALTER_TABLE
AS
BEGIN
DECLARE @EVENTDATA XML;
SET @EVENTDATA = EVENTDATA();
IF @EVENTDATA.value('(/EVENT_INSTANCE/EventType)[1]','nvarchar(max)') IN ('DROP_TABLE','ALTER_TABLE')
and @EVENTDATA.value('(/EVENT_INSTANCE/LoginName)[1]','nvarchar(max)')='testUser'
and @EVENTDATA.value('(/EVENT_INSTANCE/DatabaseName)[1]','nvarchar(max)')='tempdb'
and @EVENTDATA.value('(/EVENT_INSTANCE/SchemaName)[1]','nvarchar(max)')='dbo'
and @EVENTDATA.value('(/EVENT_INSTANCE/ObjectName)[1]','nvarchar(max)')='t1'
and @EVENTDATA.value('(/EVENT_INSTANCE/ObjectType)[1]','nvarchar(max)')='TABLE'
BEGIN
PRINT SUSER_SNAME()+'你无DROP TABLE 和 ALTER TABLE 的权限!';
ROLLBACK;
END
END
GO
--------------------- 用 testUser 用户登录之后 -----------------------
USE tempdb
GO
DELETE FROM t1
/*
消息 229,级别 14,状态 5,第 8 行
拒绝了对对象 't1' (数据库 'tempdb',架构 'dbo')的 DELETE 权限。
*/
TRUNCATE TABLE t1
/*
消息 1088,级别 16,状态 7,第 9 行
找不到对象“t1”,因为它不存在或者您没有所需的权限。
*/
DROP TABLE t1
/*
testUser你无DROP TABLE 和 ALTER TABLE 的权限!
消息 3609,级别 16,状态 2,第 3 行
事务在触发器中结束。批处理已中止。
*/
SELECT * FROM t1
/*
id Type
1 1
2 2
*/
UPDATE t1 SET [Type]=[Type]+1
/*
(2 行受影响)
*/