Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2007
    Location
    Weatherford TX (Between Fort Worth and Abilene)
    Posts
    57

    Unanswered: db_datareader & Execute permissions on procs

    I have a group of users that I have given db_datareader permissions to in an SQL Server 2000 database. I am also creating web pages on an intranet site that pulls data from the table. If I just use a select statement to pull the data from the table, the users don’t have a problem. If I use a stored procedure with the exact same sql statement, the users get an error until I grant them execute permissions on the stored procedure.

    I have heard that store procedures is the best way to handle data operations but having to make sure I assign execute permissions every time I create a stored procedures can be a pain. The only way I know of to make sure that they had permissions would be to make them a member of db_Owner which is definitely not an option.

    Is this just the way it is, or is there some way to automatically grant them execute permissions on stored procedures that are nothing more than select statements and don’t violate db_datareader permissions?

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I'm afraid that is the point of procs (from a security pov). You can allow execute on the sproc whilst not allowing them to access the underlying tables. Such granularity means you have to explicitly grant execute permissions on the stored procedures for the users to be able to use them.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Nov 2003
    Location
    London
    Posts
    169
    Hi there,

    Yep as pootle says, you need to add exec permissions to SPs regardless of whether they have datareader or datawriter.
    If you're not doing it already I would set up and NT group or custom database role then you wont need to add the permissions to each user all the time, just to the group or groups

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Alternatively, if you are using SQL 2005, you can grant SCHEMA permissions to a user or role.
    Code:
    grant execute on schema ::dbo to [public]
    However, more granular permissions are considered at least a better, if not best, practice.

  5. #5
    Join Date
    Apr 2007
    Location
    Weatherford TX (Between Fort Worth and Abilene)
    Posts
    57
    thanks to all of you for your replies.

Posting Permissions

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