Giving SP access and restrict table access

• arooj300

  Hall of Fame

  Points: 3433

  More actions

  April 8, 2015 at 12:29 am

  #295792

  Hi All,

  I want give only Procedure and function access to the user and restrict table data access to the same user.

  Is there anyway to give such kind of access.

  Please advise.

  Thanks in advance.

  Arooj

• RTaylor2208

  SSChampion

  Points: 13298

  More actions

  April 8, 2015 at 6:53 am

  #1789505

  Yes, there are more complex ways of doing this when you factor in Roles \ Schemas \ Users \ Logins but you can simply do this.

  1. GRANT the execute permission on the procedures \ functions you want the user to be able to execute.

  2. Ensure the user has no permissions on the underlying tables

  MCITP SQL 2005, MCSA SQL 2012