June 25, 2004 at 3:46 pm
Ok, how about this:
I created this stored procedure:
CREATE PROC bulkuser.a_Deleteme
AS
SET NOCOUNT ON
EXEC ('BULK INSERT cusp_bulk.tmpStringImport FROM ''\\w4997\Data\atrs01.txt''')
RETURN
And this table:
CREATE TABLE bulkuser.tmpStringImport (Datarow Varchar(4000))
Then I granted the public role EXEC permissions on bulkuser.a_Deleteme
when any user except bulkuser tries to execute the stored procedure I get this message (it runs fine for bulkuser btw):
Server: Msg 4834, Level 16, State 1, Line 1
You do not have permission to use the BULK INSERT statement.
Shouldn't the stored procedure run with bulkuser's permissions no matter who runs it since public has the EXEC permission???
June 25, 2004 at 5:01 pm
Only if that user exists in that database
From Books Online (BOL):
When a permission is granted to a SQL Server user or Windows NT user account, the specified security_account is the only account affected by the permission. If a permission is granted to a SQL Server role or a Windows NT group, the permission affects all users in the current database who are members of the group or role. If there are permission conflicts between a group or role and its members, the most restrictive permission (DENY) takes precedence. security_account must exist in the current database; permissions cannot be granted to a user, role, or group in another database, unless the user has already been created or given access to the current database.
Two special security accounts can be used with GRANT. Permissions granted to the public role are applied to all users in the database. Permissions granted to the guest user are used by all users who do not have a user account in the database.
Michelle
June 25, 2004 at 5:04 pm
Also from BOL:
Only members of the sysadmin and bulkadmin fixed server roles can execute BULK INSERT.
...
Is the name of the table or view owner. owner is optional if the user performing the bulk copy operation owns the specified table or view. If owner is not specified and the user performing the bulk copy operation does not own the specified table or view, Microsoft® SQL Server™ returns an error message, and the bulk copy operation is canceled.
Michelle
June 27, 2004 at 10:18 pm
Thank you for your responses Michelle.
I'm still not clear on this at all though. I thought that the owner of a stored procedure could grant EXEC permissions to another user or role, and that once granted, ANY user should be able to run the stored procedure. Isn't that supposed to be one of the ways to control security in SQL Server?
If I understand what you're telling me, though, it means that each user that needs to execute this stored procedure has to be a BULKADMIN role member themselves? That there's no transitive security?
June 28, 2004 at 7:50 am
An owner of a stored proc can grant rights, but it can only grant rights to logins that already have access to the database in which the stored proc resides.
from BOL:
Inside a stored procedure, object names used with statements (for example, SELECT or INSERT) that are not user-qualified default to the owner of the stored procedure. If a user who creates a stored procedure does not qualify the name of the tables referenced in SELECT, INSERT, UPDATE, or DELETE statements within the stored procedure, access to those tables through the stored procedure is restricted by default to the creator of the procedure.
Object names used with the statements ALTER TABLE, CREATE TABLE, DROP TABLE, TRUNCATE TABLE, CREATE INDEX, DROP INDEX, UPDATE STATISTICS, and DBCC must be qualified with the name of the object owner if other users are to use of the stored procedure. For example, Mary, who owns table marytab, must qualify the name of her table when it is used with one of these statements if she wants other users to be able to execute the stored procedure in which the table is used.
This rule is necessary because object names are resolved when the stored procedure is run. If marytab is not qualified and John tries to execute the procedure, SQL Server looks for a table called marytab owned by John.
BULK Insert is a special process so it will have slightly different rules than a standard stored proc.
Michelle
June 28, 2004 at 9:34 am
All of the users in question are normal db users. I'm trying to let them do BULK INSERT under special, controlled circumstances.
That's exactly what my question is. HOW are the rules different with bulk insert?
It appears from testing that the bulk admin rights are not transferred to users who are granted rights to run the stored procedure.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply