剛測了個例子給樓主參照,按自己情況更改 /* 創建角色 */ USE [ Test ] GO CREATE ROLE [ AppSelectRole ] GO use [ Test ] GO GRANT SELECT TO [ AppSelectRole ] GO GRANT CREATE TABLE TO [ AppSelectRole ] GO USE [ Test ] GO CREATE SCHEMA [ UserTest ] AUTHORIZATION [ AppSelectRole ] GO /* 創建登陸用戶 */ USE [ master ] GO CREATE LOGIN [ TestUser ] WITH PASSWORD = N ' 1 ' , DEFAULT_DATABASE = [ master ] , CHECK_EXPIRATION = OFF , CHECK_POLICY = OFF GO USE [ Test ] GO CREATE USER [ TestUser ] FOR LOGIN [ TestUser ] GO USE [ Test ] GO EXEC sp_addrolemember N ' AppSelectRole ' , N ' TestUser ' GO /* 測試 */ USE Test GO EXECUTE AS LOGIN = ' TestUser ' GO CREATE TABLE userTest.t2(ID int ) -- OK go DROP TABLE userTest.t2 -- OK go SELECT * FROM dbo.t -- OK go DROP TABLE dbo.t -- Error /* 訊息 3701,層級 14,狀態 20,行 1 無法 卸除 資料表 'T',因為它不存在或您沒有權限。 */ GO REVERT 精华推荐: SQL Server 2008 亮点