Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Rocky_Lotus_Newbie
Starting Member
2 Posts |
 Posted - 2012-08-09 : 21:19:57
|
| Hello,I am having an issue as to how I need to set up security for the following scenario.We have a stored procedure that's called from the application side (the stored proc is not scheduled as a job) adhoc. When it runs, it tries to bulk insert data from text files residing on a remote NAS share on a linux box to sql tables (new tables are created every time during the same stored procedure's execution). The application uses sql login to connect to databases, I have given bulk admin privileges besides datareader/ datawriter permissions and execute permissions on the stored proc to the sql login.I do not want to give create table permissions to the sql login, so requested the dev team to modify the stored proc as "alter procedure abcd with execute as 'dbo' ". In this case, the stored procedure is failing with the error message 'you do not have permission to use the bulk load statement' which is weird because the sql login already has bulkadmin privileges. We have a similar setup but the text files reside on a share on windows, and it's working by altering the stored proc as mentioned ("alter procedure abcd with execute as 'dbo' "). I may be missing something here but I couldn't figure out what it is. I have compared the permissions to the sql login in both the environments and they are the same.When I give the create table and alter schema schema_name to sql_login permissions on the database (with out modifying the stored proc as alter procedure abcd with execute as 'dbo) , the bulk load process is happening the way it's supposed to. But I don't want to go this route as giving the said permissions will also grant drop permissions and other unwanted permissions. Can anyone suggest any alternatives/ resolution to my problem? Thanks. |
|
|
|
|
|
SQL Server 2008 R2 security issue