Grant Execute Privilege on certain stored procedures

• niktek05

  SSC Rookie

  Points: 38

  More actions

  May 30, 2018 at 4:14 pm

  #353168

  Hi gurus,

  Is there a way to grant execute privilege to certain stored procedures within a database in sql server to a db user?
  I have 5 SPs (A, B, C, D, E) in my database.

  The store procedure "A" & "C" has SELECT statement inside it.
  The store procedure "B", "D" & "E" have INSERT/UPDATE statements inside them.

  DB users:
  1) NVReader
  2) NVWriter

  I want to allow the user "NVReader" to have SELECT privilege on all the tables within the DB and also have execute privilege just for A and C SPs. I know I could just do "Grant Execute On A to NVReader;" & "Grant Execute On C to NVReader;", but I don't want to cherry-pick each SP to grant execute privilege since I'm going to add more SPs to the DB as part of the development process. I'm guessing there's a better approach to this problem.

  Thank you
  Nik

• Andrew P

  SSCarpal Tunnel

  Points: 4649

  More actions

  May 30, 2018 at 7:34 pm

  #1992417

  Database schemas sound like a good solution, these allow you to assign permissions to a group of objects within the database. A downside is the objects need to be created within the schema, and referred to by [schema name].[database name], which I think is a reasonably large restriction if the procedures you want NVReader to be able to execute don't lend themselves to being separated from the others. I'm unsure, but I think many people would see this kind of semi-arbitrary separation caused by schemas as not an ideal.

  Example code is below, where I have assumed B, C, D are procedures that "Save" data to the database (I haven't added parameters to these procedures, you may need to use your imagination here).

  -- Create the "Save" schema
CREATE SCHEMA [Save];

  GO

  -- Create some procedures within the schema
CREATE PROCEDURE [Save]. AS
BEGIN
PRINT 'Do something here...'
END;

  GO

  CREATE PROCEDURE [Save].[D] AS
BEGIN
PRINT 'Do something else here...'
END;

  GO

  CREATE PROCEDURE [Save].[E] AS
BEGIN
PRINT 'Do something else here...'
END;

  GO

  -- Grant EXEC permission on all of the procedures in the "Save" schema to the user "guest"
GRANT EXEC ON schema::[Save] TO

  ;

  -- Execute the procedure like this
EXEC [Save].;


  You can also create a schema under Database > Security > Schemas in SSMS.

  Andrew P.