存储过程权限

[labway]

存储过程如下:
ALTER PROCEDURE usp_LisMonthReqFromReport (@WHERE VARCHAR(8000))
AS

DECLARE @SQL VARCHAR(8000)
--DECLARE @DateFromYear CHAR(10)
DECLARE @DateFrom CHAR(10)
DECLARE @DateTo CHAR(10)
DECLARE @Pos1 INT
DECLARE @Pos2 INT

DECLARE @InpatientID CHAR(20)
DECLARE @ChargeItemName VARCHAR(50)
DECLARE @Charge Dec(10,2)
DECLARE @ChargeItemNameALL VARCHAR(8000)
DECLARE @ChargeALL Dec(10,2)
DECLARE @Requisition_from VARCHAR(20)

SET @Pos1 = CHARINDEX('receive_time',@WHERE)
IF @Pos1 > 0
BEGIN
SET @Pos1 = CHARINDEX('''',@WHERE,@Pos1)
SET @Pos2 = CHARINDEX('''',@WHERE,@Pos1 + 1)
SET @DateFrom = SUBSTRING(@WHERE,@Pos1 + 1,@Pos2 - @Pos1 - 1)
--SET @DateFromYear = SUBSTRING(@DateFrom,1,4) + '-01-01'

SET @Pos1 = CHARINDEX('''',@WHERE,@Pos2 + 1)
SET @Pos2 = CHARINDEX('''',@WHERE,@Pos1 + 1)
SET @Dateto = SUBSTRING(@WHERE,@Pos1 + 1,@Pos2 - @Pos1 - 1)
END

SET @Pos1 = CHARINDEX('requisition_from',@WHERE)
IF @Pos1 > 0
BEGIN
SET @Pos1 = CHARINDEX('''',@WHERE,@Pos1)
SET @Requisition_from = SUBSTRING(@WHERE,@Pos1 + 1,7)


END

--set @DateFrom = '2008-03-01'
--set @DateTo = '2008-04-01'
--set @Requisition_from ='LIS1942'

IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE name = '##LisTable1')
DROP TABLE ##LisTable1

SET @SQL = 'SELECT DISTINCT CONVERT(CHAR(10),a.print_time,102) as print_time,a.requisition_from,a.outpatient_id,a.inpatient_id,SPACE(1000) AS chinese_name, 1000000000.00 AS charge,d.chinese_name as hotpital ,a.patient_name ' +
'INTO ##LisTable1 ' +
'FROM lis_requisition_info a, ' +
' lis_requisition_item b, ' +
' lis_base_data d, ' +
' lis_charge_item c ' +
'WHERE (a.receive_time BETWEEN ''' + @DateFrom + ''' AND ''' + @DateTo + ''') AND' +
' d.base_data_id ='''+ @Requisition_from +''' AND ' +
' a.requisition_from ='''+ @Requisition_from +''' AND ' +
' a.requisition_from = d.base_data_id AND ' +
' a.requisition_id = b.requisition_id AND ' +
' b.charge_item_id = c.charge_item_id AND ' +
' a.requisition_state <> ''deleted'' AND ' +
' a.patient_type <> ''9'' AND ' +
' a.requisition_state <> ''returned'' '

EXECUTE(@SQL)
--PRINT @SQL
--cast(space(500) as text)
ALTER TABLE ##LisTable1 ALTER column chinese_name text null
UPDATE ##LisTable1 SET charge = 0

DECLARE CUSROR_XHLis1 CURSOR FOR
SELECT DISTINCT inpatient_id FROM ##LisTable1
OPEN CUSROR_XHLis1
FETCH CUSROR_XHLis1 INTO @InpatientID
WHILE @@FETCH_STATUS = 0
BEGIN
SET @ChargeItemNameALL = ''
SET @ChargeALL = 0

DECLARE CURSOR_XHLis2 CURSOR FOR
SELECT c.chinese_name,c.charge FROM lis_requisition_info a,lis_requisition_item b,lis_charge_item c WHERE a.inpatient_id = @InpatientID AND a.requisition_from = @Requisition_from AND a.requisition_id = b.requisition_id AND b.charge_item_id = c.charge_item_id and a.requisition_state <> 'deleted' AND a.requisition_state <> 'returned' and (a.receive_time BETWEEN @DateFrom AND @DateTo )
OPEN CURSOR_XHLis2
FETCH CURSOR_XHLis2 INTO @ChargeItemName,@Charge
WHILE @@FETCH_STATUS = 0
BEGIN
--PRINT @ChargeItemName
SET @ChargeItemName = IsNull(@ChargeItemName,'')
IF @ChargeItemName <> ''
BEGIN
SET @ChargeItemName = @ChargeItemName + SPACE(24 - DATALENGTH(@ChargeItemName))
SET @ChargeItemNameALL = @ChargeItemNameALL + @ChargeItemName
END
SET @ChargeALL = @ChargeALL + ISNULL(@Charge,0.00)
FETCH CURSOR_XHLis2 INTO @ChargeItemName,@Charge
END
CLOSE CURSOR_XHLis2
DEALLOCATE CURSOR_XHLis2

UPDATE ##LisTable1 SET chinese_name = @ChargeItemNameALL,charge = @ChargeALL WHERE inpatient_id = @InPatientID

FETCH CUSROR_XHLis1 INTO @InpatientID
END

CLOSE CUSROR_XHLis1
DEALLOCATE CUSROR_XHLis1


SELECT * FROM ##LisTable1

我单独有个用户a 已经将usp_LisMonthReqFromReport的权限付给了用户a,执行usp_LisMonthReqFromReport时提示
Select error: 拒绝了对对象 'lis_base_data'（数据库 'lisdb_2005'，所有者 'dbo'）的 SELECT 权限。
我的确没有对用户a对'lis_base_data'的select权限,应该不用对存储过程里的具体表给权限吧,请帮助,谢谢

[baoshan]

需要给用户授权

[labway]

已解决,不需要给具体的表授权,只要给存储过程授权即可,不能用EXECUTE(@SQL)
直接写sql语句即可
揭贴

[nzperfect]

我的确没有对用户a对'lis_base_data'的select权限,应该不用对存储过程里的具体表给权限吧,请帮助,谢谢

---

当然需要

[Limpire]

貌似是需要对存储过程里的具体表给权限

[kuangdp]

给这个用户DB_DATEREADER角色好了~

[linsyuan]

同意

[lystey]

里面的＃＃什么意思 我弱弱的问

[labway]

是个临时表

[labway]

我还有一个存储过程
REATE PROCEDURE usp_inspection_state_update
(@inspection_id char(50),
@print_person char(20))
AS
declare @print_count int
DECLARE @standart_ID CHAR(10)
SELECT @standart_ID = lis_base_data.standart_id FROM lis_inspection_sample,lis_base_data WHERE lis_inspection_sample.deliver_hospital = lis_base_data.chinese_name and lis_inspection_sample.inspection_id = @inspection_id

SELECT @print_count = ISNULL(print_count,0) FROM lis_inspection_sample WHERE inspection_id = @inspection_id
select @print_count = @print_count + 1
UPDATE lis_inspection_sample SET print_person =@print_person , print_time = getdate(),print_count = @print_count WHERE inspection_id = @inspection_id
--update lis_inspection_sample SET inspection_state ='reported' WHERE inspection_id =@inspection_id --,print_time = getdate()
--UPDATE lis_inspection_sample SET inspection_state = 'reported' ,print_person =@print_person , print_time = getdate(),print_count = @print_count WHERE inspection_id = @inspection_id
IF @standart_ID = 'N'
BEGIN
UPDATE lis_inspection_sample SET inspection_state = 'reported' WHERE inspection_id = @inspection_id
END


用户a也没有对表lis_inspection_sample任何操作权限,但我可以用这个存储过程

[律己修心]

1.我的确没有对用户a对'lis_base_data'的select权限


2.用户a也没有对表lis_inspection_sample任何操作权限,但我可以用这个存储过程


这两个表在一个数据库里吗？



a用户无法访问'lis_base_data'所在的数据库，就没有权限SELECT

[ws_hgo]

kankan

[labway]

是在统一个数据库里