locked
SQL Server security question RRS feed

  • Question

  • 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.


    Friday, August 10, 2012 1:12 AM

Answers

  • This happens because when you use EXECUTE AS you are sandboxed into the database and can't do things outside the database. Unless, the database is set as trustworthy - which can be a major security flaw.

    A much better strategy is to use certificate signing. I cover this in depth in an article on my web site, and one of the examples I use is exactly bulk load, see
    http://www.sommarskog.se/grantperm.html


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by Rocky_SQL_DBA Sunday, August 12, 2012 7:30 AM
    Friday, August 10, 2012 9:53 PM

All replies

  • IIRW BulkAdmin is Instance level privileges. Apply on all databases on that particular instance. By running that sp  you are under dbo user privileges, so dbo does not have such permissions. Is that possible to issue WITH EXECUTE AS  <login> that has an appropriate permissions?

    Best Regards,Uri Dimant SQL Server MVP,http://sqlblog.com/blogs/uri_dimant/


    Friday, August 10, 2012 6:41 AM
  • This happens because when you use EXECUTE AS you are sandboxed into the database and can't do things outside the database. Unless, the database is set as trustworthy - which can be a major security flaw.

    A much better strategy is to use certificate signing. I cover this in depth in an article on my web site, and one of the examples I use is exactly bulk load, see
    http://www.sommarskog.se/grantperm.html


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by Rocky_SQL_DBA Sunday, August 12, 2012 7:30 AM
    Friday, August 10, 2012 9:53 PM
  • Thanks Erland, your blog has helped me.
    Sunday, August 12, 2012 7:32 AM