June 27, 2004 at 10:35 pm
I have a Security question regarding BULK INSERT...
Is it possible for a bulkadmin to write a stored procedure that includes BULK INSERT and then grant EXEC permissions to a normal user? (e.g. one who is neither BulkAdmin or SysAdmin.)
Put another way, is there any way to let a normal user execute stored procedures that have BULK INSERT in them without making that 'normal' user a member of BulkAdmin or SysAdmin?
An addendum...Nobody's been able to help here, so I ended up setting up an extra connection from the client app, a short-lived connection to do the bulk insert as the bulk-admin user. It solved my current problem, but not my understanding of the underlying issue. Any help would still be greatly appreciated!
June 30, 2004 at 8:00 am
This was removed by the editor as SPAM
March 19, 2010 at 8:25 am
I would really like to know how we can do this without giving BulkAdmin previlages but i would also eager to knoe if there are any disadvantages giving BulkAdmin access to regular users/non admins
March 19, 2010 at 12:39 pm
In SQL Server 2000 and below, no, there's no way to do so. In SQL Server 2005 you can use the EXECUTE AS clause as part of the CREATE PROC statement to determine what login or user to run in the context of. So the end user can execute the stored procedure and while the stored procedure is executing, the end user is technically running as that other account context (but only within the stored procedure). This is new to SQL Server 2005, which is why I say no if you're still on SQL Server 2000.
A work around is to have a stored procedure which inserts a row into a table that is periodically checked by a SQL Server Agent job. The job sees the row and knows what to do with it, basically implementing the BULK INSERT task. This is a possible way to solve the problem in SQL Server 2000.
K. Brian Kelley
@kbriankelley
March 19, 2010 at 1:33 pm
Funny how posts can come to life years later! Thanks for the reply Brian!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply