┏━━━━┓
┃问题描述┃
┗━━━━┛
库db1中有一个存储过程如下，
---------------------------------
CREATE proc haha
as
create table #t(id int)
bulk insert #t from 'e:\aa.txt'
select * from #t
drop table #t
GO
----------------------------------
当我以非SA身份登陆，（在查询分析器中）执行该存储过程时，
报错如下，
---------------------------------------------------------------
服务器: 消息 8104，级别 16，状态 2，过程 haha，行 4
当前用户不是表 '#t' 的数据库或对象所有者。无法执行 SET 操作。
---------------------------------------------------------------
我用的登陆身份是库db1的db_owner，同时也属于服务器角色Bulk Insert Administrators
┏━━━━┓
┃请 问┃
┗━━━━┛
如何（在 ‘尽量’ 满足以下条件的前提下）解决如上权限问题？
条件一：由于安全原因，不能给该登陆以System Administrators身份；
条件二：数据源aa.txt内容较多，大概120w条数据左右，不想使用“表变量”来代替临时表；
条件三：不想使用普通的表来代替临时表。
=========================
附：我的排错经过
┏━━━━━━┓
┃搜索ＣＳＤＮ┃
┗━━━━━━┛
未发现关于本问题的内容。
┏━━━━━━┓
┃测试不同身份┃
┗━━━━━━┛
给该登陆以System Administrators身份后存储过程执行正常。
┏━━━━━━┓
┃搜索微软网站┃
┗━━━━━━┛
发现一篇文章，时间是2003年10月的，不知是否跟我的问题有关，而且也未完全看懂。
文章地址是http://support.microsoft.com/default.aspx?scid=kb;en-us;300701
内容大致如下，
------------------------------------------
SYMPTOMS
When you use the bulk copy program (BCP) to copy data into a temporary table created by a user who is not the database owner (DBO) in the tempdb database, the operation may fail with the following error:
Server Error 229
INSERT permission denied on object '##table_name', database 'tempdb', owner 'dbo'.
WORKAROUND
You can work around this problem in the following ways: • If the BCP process is repetitive, you can create a permanent table with the BCP login mapped to a user who has permissions on the table.
• You can give permissions to the users who intend to perform the BCP operation into the temporary table in tempdb.
STATUS
Microsoft has confirmed this to be a problem in SQL Server 7.0 and 2000.
------------------------------------------
┏━━━━━━━━┓
┃搜索ｇｏｏｇｌｅ┃
┗━━━━━━━━┛
发现一个老外问到相似问题，未见满意的解决办法。
地址为http://www.adminlife.com/247reference/msgs/36/184225.aspx
内容大致如下：
--------------------------------------------
I got a problem with Bulk Insert lately in one of our clients environment.
Basically, I have a stored procedure that creates a temporary table, and
then use the BULK INSERT INTO.. statement to populate the temp. table with
data records from a text file.
This simple stored procedure running every where but this client, and he the
one that running the program with a login name that does not have
administrator right. As soon as the stored procedure tries to do the BULK
INSERT, SQL throws an error message "The current user does not the database
or object owner of the table #TMP...."
What rights does the user need to have in order to do the BULK INSERT?
Best regards,
David
--------------------------------------------
---------------------------------------------------------------
以下是zjcxc的答复：
--用sa身份登录,授予你的非sa用户(db1的db_owner)对model数据库的db_owner角色
--停止并重新启动sql server服务,再执行你的存储过程就可以了.
--你的bulk insert是插入临时表的,所以用户要求是#t或者tempdb的所有者
---------------------------------------------------------------