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:03
|
| 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 have compared the permissions to the sql login in both the environments and they are the same. I may be missing something here but I couldn't figure out what it is.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. |
|
|
Bustaz Kool
Master Smack Fu Yak Hacker
1834 Posts |
Posted - 2012-08-10 : 17:46:11
|
| I don't know if any if these are germane but from BOL...(ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.en/s10de_6tsql/html/be3984e1-5ab3-4226-a539-a9f58e1e01e2.htm)ALTER TABLE permission is required if one or more of the following is true:Constraints exist and the CHECK_CONSTRAINTS option is not specified. Note: Disabling constraints is the default behavior. To check constraints explicitly, use the CHECK_CONSTRAINTS option. Triggers exist and the FIRE_TRIGGER option is not specified. Note: By default, triggers are not fired. To fire triggers explicitly, use the FIRE_TRIGGER option. You use the KEEPIDENTITY option to import identity value from data file.=================================================Show me a sane man and I will cure him for you. -Carl Jung, psychiatrist (1875-1961) |
 |
|
|
|
|
|
|
|
SQL Server 2008 R2 security issue