none
MS Access and Sql Server Permissions RRS feed

  • Question

  • I have created an adp, and have created stored procedures for all of my forms. In the SQL Server database I have given my users permissions as db_datareader and db_datawriter, however they were unable to open any of my forms because they didn’t have execute permissions on my stored procedures.  I finally had to give them permissions as db_owner so they could use my forms. Do I have to give them db_owner permissions to execute my stored procedures?  I know I can go in and assign them execute permissions for my stored procedures, but there has to be a better way.  Does anyone know how to give users execute permissions on stored procedures without having to give them db_owner permissions or having to grant execute permissions for each stored procedure individually?

     

    I am still in the development phase of my project and having to assign users or groups with each stored procedure I create could really be a pain.  Can someone please help me?

    Wednesday, May 9, 2007 12:16 AM

Answers

  • In Sql 2000 or 2005 you could create a role like db_executor

     

    exec sp_addrole db_executor

    go

     

    then grant execute on the procs to the role

    then add users to the role.

     

    hth,

     

    -Steven Gott

    S/DET

    SQL Server

     

    Wednesday, May 9, 2007 12:20 AM
    Moderator