0

I am a developer and want my DBA to give a group of users permissions to execute stored procedures in a schema [Rptg] and view the results but not be able to view the t-sql code in the stored procedure. They will be using Azure Data Studio to connect and execute the stored procedure. Should we 1st create a role?

Dan Guzman
  • 43,250
  • 3
  • 46
  • 71
Marzipan
  • 13
  • 4

2 Answers2

2

you definitely should use roles. To be certain that role members cannot view the text of a proc you can add:

deny view permission on dbo.procx to role25;

to deny permission on a schema

DENY VIEW DEFINITION ON SCHEMA::Products  to role25;
benjamin moskovits
  • 5,261
  • 1
  • 12
  • 22
  • The OP is also asking how to allow the user to execute. If you add grant execute to your answer it would be a big improvement. They also are asking about an entire schema not at the stored proc level. – Sean Lange Mar 06 '19 at 20:48
  • 2
    `DENY` should be used sparingly; its interactions with `GRANT` on various levels tend to be hard to predict. A custom role will lack permission to see definitions by default -- these are not included or implied by permission to `EXECUTE`. – Jeroen Mostert Mar 06 '19 at 20:49
1

The example below grants a role execute permissions on the Rptg schema. Members of this role will not have VIEW DEFINION on these stored procedures by default as called out in the comment by @JeroenMostert.

CREATE ROLE RptgSchemaProcExecutor;
GRANT EXECUTE ON SCHEMA::Rptg TO RptgSchemaProcExecutor;

Here's a sample validation script.

CREATE USER ExampleUser WITHOUT LOGIN;
ALTER ROLE RptgSchemaProcExecutor
    ADD  MEMBER ExampleUser;
GO
EXECUTE AS USER = 'ExampleUser';
GO
--this will err with "There is no text for object 'Rptg.ExampleProc'."
EXECUTE sp_helptext  'Rptg.ExampleProc';
GO
REVERT;
GO
Dan Guzman
  • 43,250
  • 3
  • 46
  • 71