To homepage To homepage
Solved

Object security, allow execute of stored procedures on specific database schema

  • 17 April 2023
  • 4 replies
  • 59 views
bas.hopstaken
Rank
Userlevel 3
Badge +4

Hi community,

I created a role in the MDW and added Grant permission on one specific database schema. This schema contains some tables and some Stored Procedures. This role need to be able to select data in the tables and execute the stored procedures. I created my Object Security Setup using these settings:

 

 

When I login using SSMS (using the user in the role), I see the correct tables and I'm able to select data in the tables. But I'm not able to see the stored procedures. When I check the permissions on the ‘ExcelAddin’ database schema. Only the ‘select’ permission in granted. Is it possible to grant permission for ‘execute’ as well? Using TimeXtender?

icon

Best answer by rory.smith 17 April 2023, 15:20

View original
Bas Hopstaken (2Foqus)

4 replies

rory.smith
Rank
Userlevel 5
Badge +7

Hi @bas.hopstaken ,

 

as far as I know there is no GUI-way of doing that. I suppose you could use a pre- or post-script on the layer itself, but that would be something to be manually maintained. I am not entirely sure of what you are doing that requires stored procedures, so not sure if there is another way to reach the same objective.

bas.hopstaken
Rank
Userlevel 3
Badge +4

Hi @rory.smith ,

 

Thanks for the quick reply. I think I need to tackle it using a pre/post script. 

The Stored Procedures are used by Excel. An external company created a Excel report that is using the values in Excel as parameters for the stored procedures. The user connecting to the database from Excel was created in SSMS, this user have way to many permissions. So I prefer to do security settings within TimeXtender instead of SSMS. I created a separate Database Schema and added all tables and Stored Procedures to this schema. I thought giving access to this Database Schema will also give access to the Stored Procedures. But unfortunately this isn't working as I hoped. The Object Security Setup on a Role in TimeXtender is only showing Tables, Views and Schemas. Stored Procedures aren't visible in the settings.

Bas Hopstaken (2Foqus)
Christian Hauggaard
Rank
Userlevel 6
Badge +5

Thanks for your question @bas.hopstaken, as Rory mentions there is no way of currently doing this, but please submit an idea in the ideas section

bas.hopstaken
Rank
Userlevel 3
Badge +4

Hi @rory.smith , @Christian Hauggaard ,

 

Thanks for the replies. I will create a idea for this!

Bas Hopstaken (2Foqus)

Reply


Terms and ConditionsCookie settings