7

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.)

|improve this question
  • 1
    "They don't see the entity dbo.Proc1" That shouldn't happen unless you have explicitly denied VIEW DEFINITION. Otherwise VIEW DEFINITION is granted automatically when you grant any other permission on an object. – David Browne - Microsoft Jul 11 '18 at 15:48
  • Can they execute the procedure or they can't see it in SSMS or some other database GUI? Is it a new AD group? Has the user logged off and on or rebooted since being added? – Alen Jul 11 '18 at 15:49
  • 1
    It is not a new AD group, they definitely logged off and back on since they were added. They don't see the procedure. I did sort of intentionally leave out the fact they're trying to pull it into Excel (they don't use SSMS but I'm having them install it now to see verify if it's an Excel issue). So in Excel when they go to Data -> Get Data -> From Database -> From SQL Server Database, it asks them for the server name but doesn't ask credentials (assuming it authenticates via his domain account) and then in the list of databases, the dbo.Proc1 entity is not showing under DatabaseA. – J.D. Jul 11 '18 at 15:51
  • @DavidBrowne-Microsoft If that's true, should I see the "View Definition" permission having GRANT checked off in the Permission Properties of the dbo.Proc1 entity? Currently when I go back in to check the Permissions of dbo.Proc1, only the Execute permission is granted for this AD account. – J.D. Jul 11 '18 at 15:56
8

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

|improve this answer
  • You're right, this was the issue all along! Today I learned to always include all the details in my original question :). Thanks for your help! P.s. If you talk to any of the developers on the PowerQuery team, might be worth mentioning including procedures in the UI entity list would be helpful to the users ;). Maybe use PowerShell like SSMS utilizes to get all entities. – J.D. Jul 11 '18 at 18:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.