Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2002
    Posts
    8

    Unanswered: stored procedure permissions issue

    Hi,

    I am testing a method for users to only have the ability to execute only stored procedures that return data and not be able to execute procs that modify data. For testing purposes I have created a 'select' procedure and an 'insert' procedure. I created a user with db_datareader and execute permissions on the two procs and I was still able to execute the 'insert' proc as this user. I also attempted to deny insert permissions on the referenced table to no avail.

    Short of establishing a role and granting execute permission to the appropriate procedures, is there a simpler way to do this?

    Thank You

    Michael

  2. #2
    Join Date
    Dec 2001
    Location
    Toronto, Canada
    Posts
    335
    That is how GRANT EXECUTE works. This is what we do at our site. All users have SELECT permissions (we use a role) and EXECUTE permission on all procedures, only the procedures can INSERT/UPDATE/DELETE. So the Role the users are assigned too can not modify data only read (SELECT), however they can modify data throught a controlled stored procedure. Once you give a user EXECUTE permission you then give them the permission to modify the underlying table, only through that procedure. They still can not write there own UPDATE/INSERT/DELETE statements.

Posting Permissions

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