22,208
社区成员
发帖
与我相关
我的任务
分享
-------------- 测试表及增加的视图 Begin ----------------
--这里的测试库为 tempdb
--弄清楚之后,修改相关代码,再上正式生产环境操作
USE tempdb
GO
IF OBJECT_ID('dbo.ICStockBill') IS NOT NULL
DROP TABLE dbo.ICStockBill
GO
CREATE TABLE dbo.ICStockBill(
id INT IDENTITY(1,1) PRIMARY KEY,
[Type] INT --假设1为出库单,2为其它
)
GO
SET NOCOUNT ON
INSERT INTO dbo.ICStockBill([Type]) VALUES(1)
INSERT INTO dbo.ICStockBill([Type]) VALUES(2)
GO
IF OBJECT_ID('dbo.view_ICStockBill_Type2') IS NOT NULL
DROP VIEW dbo.view_ICStockBill_Type2
GO
CREATE VIEW dbo.view_ICStockBill_Type2
AS
SELECT * FROM dbo.ICStockBill WHERE [type]=2
GO
-------------- 测试表及增加的视图 End ----------------
--1. 创建登录名
USE [master]
GO
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
GRANT SELECT ON dbo.view_ICStockBill_Type2 TO testUser
--------------------- 用 testUser 用户登录之后 -----------------------
USE [tempdb]
GO
SELECT [id]
,[Type]
FROM ICStockBill
/*
消息 229,级别 14,状态 5,第 4 行
拒绝了对对象 'ICStockBill' (数据库 'tempdb',架构 'dbo')的 SELECT 权限。
*/
SELECT [id] ,[Type] FROM [dbo].[view_ICStockBill_Type2]
/*
id Type
2 2
*/
GRANT SELECT on 视图 to 用户名