以下的存储过程是我获得一个表的最大号的,在sa下调用成功,在lab用户下调用就失败,lab属于public,且我已经让lab获得了执行存储过程的权限了,各位朋友帮帮我吧.--取得最大号
CREATE PROCEDURE usp_lab_get_max_no
(@tablename char(50),
@max_no char(20) output )
AS
Begin Tran T1Declare @ini_max varchar(10),
@max varchar(20),
@max_int varchar(20),
@serial_no varchar(20)
If Not Exists(Select max_no from sys_coding where table_name = @tablename )
Insert into sys_coding(table_name,max_no)
VALUES (@tablename,@ini_max) Begin Tran T2select @max_int = max_no from sys_coding Where table_name = @tablename
If isnumeric(@max_int) = 1 And charindex('e',@max_int) < 1
Select @max = Ltrim(Rtrim(Convert(varchar(16),Convert(bigint,@max_int) + 1)))
Else
Begin
If @tablename = 'lis_change_log'
Begin
select top 1 @serial_no = serial_no from lis_change_log Order by change_time Desc
--1e+006
If charindex('e+',@serial_no) > 0
Select @max = Substring(@serial_no,1,charindex('e',@serial_no) - 1) + REPLACE(space(Convert(int,Substring(@serial_no,charindex('e',@serial_no) +2,Len(@serial_no) - charindex('e',@serial_no) - 1)) - 1) ,' ','0') + '1'
Else
Select @max = '1000002'
End
End
If @max is Null
Select @max = 'error'
If isnumeric(@max) <> 1
Select @max = 'error'Update sys_coding set max_no = @max Where table_name = @tablename
Commit Tran T2Select @max_no = max_no from sys_coding Where table_name = @tablename
Commit Tran T1Return
GO
CREATE PROCEDURE usp_lab_get_max_no
(@tablename char(50),
@max_no char(20) output )
AS
Begin Tran T1Declare @ini_max varchar(10),
@max varchar(20),
@max_int varchar(20),
@serial_no varchar(20)
If Not Exists(Select max_no from sys_coding where table_name = @tablename )
Insert into sys_coding(table_name,max_no)
VALUES (@tablename,@ini_max) Begin Tran T2select @max_int = max_no from sys_coding Where table_name = @tablename
If isnumeric(@max_int) = 1 And charindex('e',@max_int) < 1
Select @max = Ltrim(Rtrim(Convert(varchar(16),Convert(bigint,@max_int) + 1)))
Else
Begin
If @tablename = 'lis_change_log'
Begin
select top 1 @serial_no = serial_no from lis_change_log Order by change_time Desc
--1e+006
If charindex('e+',@serial_no) > 0
Select @max = Substring(@serial_no,1,charindex('e',@serial_no) - 1) + REPLACE(space(Convert(int,Substring(@serial_no,charindex('e',@serial_no) +2,Len(@serial_no) - charindex('e',@serial_no) - 1)) - 1) ,' ','0') + '1'
Else
Select @max = '1000002'
End
End
If @max is Null
Select @max = 'error'
If isnumeric(@max) <> 1
Select @max = 'error'Update sys_coding set max_no = @max Where table_name = @tablename
Commit Tran T2Select @max_no = max_no from sys_coding Where table_name = @tablename
Commit Tran T1Return
GO
Update sys_coding set max_no = @max Where table_name = @tablename
以上两句需要权限
这是存储过程在权限设置方面和其它T-SQL语句不通的地方。
LZ再好好检查一下你的lab帐号的确对这个usp_lab_get_max_no 有执行权限。GRANT EXECUTE ON usp_lab_get_max_no TO lab另外,lab至少还要有connect的权限
关于查询分析器的使用:打开查询分析器,用lab用户登录后,直接在编辑窗口中执行:
exec usp_lab_get_max_no ..试试看
----------------------------
lab的权限太少了,这些对象都没有权限去访问。你平时都用sa惯了,所以没有察觉。
你就用lab连到查询分析器,运行sp就可以。