Results 1 to 14 of 14
  1. #1
    Join Date
    Feb 2004
    Posts
    193

    Unanswered: Can a user be granted Read Only privileges for stored procedures

    Can a user be granted the ability to create stored procedures with read only capabilities? I wouldn't mind be able to UPDATE but simply need to read only.

    ddave

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I don't understand...

    You GRANT EXECUT to sprocs....

    Do you mean to the underlying tables?


    Personally...

    In our Prodction environment, we hand the scripts over to the prod dba...

    the run the script and it gets created as dbo...

    users are granted execute to the sprocs...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Feb 2004
    Posts
    193
    Hi Brett,

    In one of the companies I am working for I am requesting the ability to create stored procedures. The data is extremely sensitive however. It is for a financial company where access to this data would allow someone like myself to alter data, ie wire myself money. I don't want that to be a concern of course so I was wondering if I could run stored procedures with the ability to read but not alter the data. Is there such a thing? Thanks.

    ddave

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Yes,

    But it's at the table

    either

    GRANT SELECT ON dbTable TO yourID

    Or have the dba put you in a role...

    That's what I'd do...
    set up a role...call readonly or whatever..

    GRANT SELECT on all tables

    SELECT 'GRANT SELECT ON ' + TABLE_NAME + 'TO readonly'
    FROM INFORMATION_SCHEMA.TABLES

    And just put your id in that role...

    makes managing alot easier.....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    One slight problem, though. If you have a table that you can only read, you can still execute procedures that update/insert/delete that table. The select permissions that you are granted only really extend to the actions that you yourself are running (outside stored procedures).

    This of course only applies to objects with an unbroken chain of ownership. If you put yourself in a role that has been denied update, delete, insert on the specific tables, then you could create procedures in your own schema that do almost anything. But you yourself (and anyone running them) would be limited by their own permissions on the underlying tables. This is not a fun place to be for a livinig system that will have various coders running around tieing their applications, reports, and what-not into various bits of code that were left lying around.

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Well just before the database crashed....AGAIN...

    I was going to say I agree....

    Don't you have a dev environment?

    playing in production is not a good thing....

    oops

    Now what did I do with that backup?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Does the db_datareader role fit the bill?

    -PatP

  8. #8
    Join Date
    Feb 2004
    Posts
    193
    No, I don't have a dev environment. How do you get one? The company I work for is huge but this is one small department granting me the ability to access this data. I know that there is a lot for them and myself to learn.

    ddave

  9. #9
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Keep in mind that (at least for the most part) the permissions used by a stored procedure are the permissions of the user that creates the stored procedure. There are a few exceptions, but very, very few. The procedure's EXECUTE permission is the only permission that matters to the user that runs the stored procedure.

    This allows a "super user" like dbo to create a procedure that can do almost anything, then give other "underprivledged" users permission to execute that procedure.

    -PatP

  10. #10
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Do you have a dba?

    If I were you, I'd make SURE that I didn't get blamed for ANYTHING...

    and that means staying out of production, and be isolated in another environment...

    What do you have to do?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  11. #11
    Join Date
    Feb 2004
    Posts
    193
    So Pat,

    What you're telling me is that since my contact in this other department has the ability to EXECUTE stored procedures, it's either feast or famine. I will either be able to run sprocs AND perform DML commands or not based on whether he GRANTs me these permissions or not. Is this correct? Thanks.

    ddave

  12. #12
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Sorry, but SQL Server security isn't quite that simple.

    When a stored procedure is created, the database engine checks the permissions of the creator to perform all of the operations in the procedure. If the creator is lacking any of the permissions needed for any statement in the stored procedure, it won't compile successfully.

    When a stored procedure is executed, the database engine only checks to see if the current user has permission to execute the stored procedure.

    This allows a privileged user like dbo to create a stored procedure that can be executed by an "underprivileged" user that contains DML that the user could not execute directly. It allows the creator to "delegate" their privileges in a controlled way.

    There are a few exceptions to this, but the only exception that matters in many cases is dynamic SQL. SQL Server executes dynamic SQL in a different context, almost like the current user created a new spid for the dynamic SQL. That is why @variables can't be referenced in dynamic SQL, and it is also why the user needs to have permission to execute any DML statements that are executed dynamically.

    -PatP

  13. #13
    Join Date
    Feb 2004
    Posts
    193
    Brett,

    By the question "What do you have to do?" I take you mean what is my job here? It is simply creating reports for loan data. There are several steps required but basically updating data from a temporary table that has its data destroyed every fifteen minutes, deduping, grouping, and sorting and that sort of fun stuff.

    I don't think there will be a place to hide. I will either be granted privileges to do this or not.

    ddave

  14. #14
    Join Date
    Apr 2004
    Location
    Kansas City, MO
    Posts
    734
    If you are working for a large company there has to be a DBA or at least an IS contact who is responsible for this. You need to have them give you db_datareader access. You can then create procedures and have THEM place them on the production server after reviewing them.

    And, tell them to get a development environment. If they are a large company, I'm sure they can afford it.
    MeanOldDBA
    derrickleggett@hotmail.com
    When life gives you a lemon, fire the DBA.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •