SQL Server 2008 – How to GRANT Permission to Execute Stored Procedure

permissionsroleSecuritysql serversql-server-2008

I have an AD group that is setup as a Windows Authenticated SQL Login on my SQL Server. On DatabaseA it has the db_datareader and public roles. So the users of this AD group only have read access to the entities in the database.

Inside of DatabaseA I have a stored procedure dbo.Proc1 on which I granted the execute permission for the same AD group.

When a user of that AD group connects to this server, they don't see the entity dbo.Proc1.

Is there any other permission I need to provide to the Windows Authenticated SQL Login (AD group) that I'm missing?

I do see, if I look at the properties of DatabaseA, that there's an execute permission at that level which has NOT been granted yet.

(Note: I only want the users of this AD group to have the ability to execute dbo.Proc1. I don't want them to be able to do anything that isn't considered a "read-only" type of action.)

Best Answer

in Excel when they go to Data -> Get Data -> From Database -> From SQL Server Database

That's PowerQuery. Just ran through that on Excel, and stored procedures don't appear, but Table-Valued Functions do. And you can call a stored procedure, but it won't show up in the query designer. You just enter 'exec dbo.Proc1' in the SQL Statement dialog:

enter image description here