请问邹老大的此存储过程如何修改不用sa的账号？

Question

create proc p_exporttb
@sqlstr varchar(8000),   --查询语句,如果查询语句中使用了order by ,请加上top 100 percent
@path nvarchar(1000),   --文件存放目录
@fname nvarchar(250),   --文件名
@sheetname varchar(250)=''  --要创建的工作表名,默认为文件名
as 
declare @err int,@src nvarchar(255),@desc nvarchar(255),@out int
declare @obj int,@constr nvarchar(1000),@sql varchar(8000),@fdlist varchar(8000)--参数检测
if isnull(@fname,'')='' set @fname='temp.xls'
if isnull(@sheetname,'')='' set @sheetname=replace(@fname,'.','#')--检查文件是否已经存在
if right(@path,1)<>'\' set @path=@path+'\'
create table #tb(a bit,b bit,c bit)
set @sql=@path+@fname
insert into #tb exec master..xp_fileexist @sql--数据库创建语句
set @sql=@path+@fname
if exists(select 1 from #tb where a=1)
 set @constr='DRIVER={Microsoft Excel Driver (*.xls)};DSN='''';READONLY=FALSE'
       +';CREATE_DB="'+@sql+'";DBQ='+@sql
else
 set @constr='Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties="Excel 8.0;HDR=YES'
    +';DATABASE='+@sql+'"'--连接数据库
exec @err=sp_oacreate 'adodb.connection',@obj out
if @err<>0 goto lberrexec @err=sp_oamethod @obj,'open',null,@constr
if @err<>0 goto lberr--创建表的SQL
declare @tbname sysname
set @tbname='##tmp_'+convert(varchar(38),newid())
set @sql='select * into ['+@tbname+'] from('+@sqlstr+') a'
exec(@sql)select @sql='',@fdlist=''
select @fdlist=@fdlist+',['+a.name+']'
 ,@sql=@sql+',['+a.name+'] '
  +case 
   when b.name like '%char' 
   then case when a.length>255 then 'memo'
    else 'text('+cast(a.length as varchar)+')' end
   when b.name like '%int' or b.name='bit' then 'int'
   when b.name like '%datetime' then 'datetime'
   when b.name like '%money' then 'money'
   when b.name like '%text' then 'memo'
   else b.name end
FROM tempdb..syscolumns a left join tempdb..systypes b on a.xtype=b.xusertype
where b.name not in('image','uniqueidentifier','sql_variant','varbinary','binary','timestamp')
 and a.id=(select id from tempdb..sysobjects where name=@tbname)if @@rowcount=0 returnselect @sql='create table ['+@sheetname
 +']('+substring(@sql,2,8000)+')'
 ,@fdlist=substring(@fdlist,2,8000)exec @err=sp_oamethod @obj,'execute',@out out,@sql
if @err<>0 goto lberrexec @err=sp_oadestroy @obj--导入数据
set @sql='openrowset(''MICROSOFT.JET.OLEDB.4.0'',''Excel 8.0;HDR=YES
   ;DATABASE='+@path+@fname+''',['+@sheetname+'$])'exec('insert into '+@sql+'('+@fdlist+') select '+@fdlist+' from ['+@tbname+']')set @sql='drop table ['+@tbname+']'
exec(@sql)
returnlberr:
 exec sp_oageterrorinfo 0,@src out,@desc out
lbexit:
 select cast(@err as varbinary(4)) as 错误号
  ,@src as 错误源,@desc as 错误描述
 select @sql,@constr,@fdlist
GO这个存储过程总的来说很不错，可是有个地方就是，必须用sa的账号才能使用。
要不然会报错。请问如何修改不用sa的账号？普通账号就能使用？
tks

Answer 1

我知道里面用到了tempdb和master库，各位高手看看，如何修改？能改么？

Answer 2

建个用户，授权它服务器焦色sysadmin，应该可以用吧

Answer 3

建个用户，把sysadmin的权限给他。或者给他在tempdb和master两个表中的所有相关权限，即可。

Answer 4

看来普通帐号也能登录的话,得建立许多用户创建角色，用户，权限/*--示例说明
        示例在数据库pubs中创建一个拥有表jobs的所有权限、拥有表titles的SELECT权限的角色r_test
    随后创建了一个登录l_test，然后在数据库pubs中为登录l_test创建了用户账户u_test
    同时将用户账户u_test添加到角色r_test中，使其通过权限继承获取了与角色r_test一样的权限
    最后使用DENY语句拒绝了用户账户u_test对表titles的SELECT权限。
    经过这样的处理，使用l_test登录SQL Server实例后，它只具有表jobs的所有权限。
--*/USE pubs--创建角色 r_test
EXEC sp_addrole 'r_test'--授予 r_test 对 jobs 表的所有权限
GRANT ALL ON jobs TO r_test
--授予角色 r_test 对 titles 表的 SELECT 权限
GRANT SELECT ON titles TO r_test--添加登录 l_test,设置密码为pwd,默认数据库为pubs
EXEC sp_addlogin 'l_test','pwd','pubs'--为登录 l_test 在数据库 pubs 中添加安全账户 u_test
EXEC sp_grantdbaccess 'l_test','u_test'--添加 u_test 为角色 r_test 的成员
EXEC sp_addrolemember 'r_test','u_test'--拒绝安全账户 u_test 对 titles 表的 SELECT 权限
DENY SELECT ON titles TO u_test/*--完成上述步骤后,用 l_test 登录,可以对jobs表进行所有操作,但无法对titles表查询,虽然角色 r_test 有titles表的select权限,但已经在安全账户中明确拒绝了对titles的select权限,所以l_test无titles表的select权限--*/--从数据库 pubs 中删除安全账户
EXEC sp_revokedbaccess 'u_test'--删除登录 l_test
EXEC sp_droplogin 'l_test'--删除角色 r_test
EXEC sp_droprole 'r_test'
本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/htl258/archive/2009/04/03/4038870.aspx
参考一下

Answer 5

这个存储过程要求的权限比较大，最简单的办法还是付给一个普通用户：sysadmin的权限。

Answer 6

试试给普通用户sysadmin的权限，不行就只能用sa了

Answer 7

在 sql 2005 中可以使用上下文切换，让普通用户成功的执行此存储过程。如，
CREATE PROCEDURE p_exporttb WITH EXECUTE AS OWNER ... 创建存储过程，将存储过程的所有者设置为 SYSADMIN 服务器角色成员，只要用户对此存储过程具有 EXECUTE 权限即可成功执行。
也可以使用存储过程签名实现，具体方法可参考 BOOK ONLINE。

Answer 8

systemadministrator的权限会不会太大了？
我就担心systemadministrator的权限太大。另外systemadministrator不就是sa吗？

Answer 9

谢谢大家。我现在想问下，普通账户加上ystemadministrator权限之后和sa有什么区别？

Answer 10

你先给他最大的权限，看能不能执行再说，如果给了他systemadministrator
都不行，那你就不要在枉费工夫了，如果可以，你在慢慢的调权限。知道合适为止

Answer 11

建立一个登录，然后赋予它相应的权限就可以了。不能给systemadministrator角色，相当于另一个sa！
可以先授予相应的数据库访问权和语句执行权，不断调试，慢慢增加权限，直至能正常执行为止。

Answer 12

SQL2000的除了system administrator权限之外都不可行。
给普通账户加上master和tempdb的所有权，也报错。
另外tempdb重启之后似乎权限会消失，也就是说会重置？

Answer 13

准确的说：加上master和tempdb的权限之后的报错：已拒绝对 OLE DB 提供程序 'MICROSOFT.JET.OLEDB.4.0' 的特殊访问。必须通过链接服务器来访问此提供程序。 

Answer 14

可以尝试一下“所有权链”：
确保存储过程中引用对象的所有权链没有被破坏；
打开服务器的“跨数据库所有权链”选项；
为需要执行该存储过程的用户分配 execute 权限；
在存储过程需要访问的数据库中为该用户创建用户帐户。为了避免在 tempdb 数据库中创建的账户，在每次启动后丢失，可以在 model 数据库中创建用户帐户。这样用户只需具有存储过程的 execute 权限，就可以成功的执行存储过程。

Answer 15

看来普通帐号也能登录的话,得建立许多用户创建角色，用户，权限
--创建角色 r_test
EXEC sp_addrole 'r_test'--授予 r_test 对 jobs 表的所有权限
GRANT ALL ON jobs TO r_test
--授予角色 r_test 对 titles 表的 SELECT 权限
GRANT SELECT ON titles TO r_testSQL77的值得参考