This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(147.20000000000002, 82%, 24%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ETS%3C/text%3E%3C/svg%3E)
Only 3 stored procedures are presented to me in the SSRS designer when I am trying to create a dataset.
There should be 30+ stored procedures to choose from.
Why is this happening?
... I used 'azure-open-datasets' as a hashtag because I did not see a better choice.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(297.6, 97%, 38%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EAS%3C/text%3E%3C/svg%3E)
Hi @Tom Stone , Could you please try logging to SQL server through SSMS and check if those stored procedures are visible from it? Also are you using Azure Sql Database or on-premise SQL Server.
Hello @AnuragSharma-MSFT ... we use on-prem SQL Server, and yes I see all 30+ sprocs in SSMS
From @Tom Phillips ...
USE master
GO
GRANT VIEW ANY DEFINITION TO PUBLIC
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(256, 32%, 34%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EMT%3C/text%3E%3C/svg%3E)
This appears to be a duplicate of this.
sorry about that cooldadtx.
do you have any insight into this issue cooldadtx?
I don't use Azure open datasets so I cannot answer why you are seeing a filtered list.
@Michael Taylor ... we use on-prem SQL Server, not Azure open datasets
I assume you mean in the query designer where you can select tables/views/sprocs. In that case the only time I see issues is when permissions get in the way of accessing the schema or sproc. Of course this is easy to verify by ensuring the dataset's data source configuration is using an account that has dbo access. If this is integrated security then the user running the designer would need those permissions.
If you see differences between SSMS and the report designer then it leads me to a difference in the connection string between SSMS and the data source in the dataset.
Hi @Tom Stone ,
When you select a different data source, the corresponding stored procedure will appear.
Please check whether your 30+ stored procedures are all using the same data source.
Best Regards,
Joy
If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread. 3: https://learn.microsoft.com/en-us/answers/articles/67444/email-notifications.html
There seems to be one anomaly that might be the root cause of this issue.
The 2 stored procedures that are listed both have special permissions set: "Execute" is granted to public. See image.
This, I believe, was done a long time ago by an inexperienced developer.
However, I removed these permissions and the dataset stored procedure list is still limited to 2 sprocs, and 1 table-valued function.
The 1 table-valued function does not have any special permissions set.
I'm sorry I don't understand what else are you talking about? What permissions are you referring to?
Do your 30+ stored procedures all use the same data source？
@Joyzhao-MSFT ... explicit permissions for a role to be able to execute a stored procedure.
None of our other databases have these explicit permissions set for any stored procedure, and we do not have the same problems with them.
So one would think I could just remove these execute permissions for these 2 sprocs and the problem would go away; but it does not go away.
Yes .. to ... Do your 30+ stored procedures all use the same data source？
Are all the sprocs in the same schema? dbo?
In the screenshot you are logging in as HBIRES\ToStone. Is that the same account that you are using when you configured the data source for the report in the designer? If not then view the permissions for the account under which the data source is configured to run as.
Note that your screenshot is looking at the explicit permissions on a single sproc while Joyzhao is showing the database-level permissions for the account.
If worse comes to worse then temporarily use a dbo account in the data source for the report designer to see if all sprocs show up. If so then the issue is with the account you're using when you connect using the data source connection string in the report.
@Michael Taylor ... a read-only account is used to configure the data source.
I did create a separate data source with a separate user account with dbo privs and the problem went away.
I agree with you that the problem is with the read-only account, but I do not know how to fix it.
Personally what I'd do is configure the data source in the designer to use a dev account that has enough privileges to view the sproc definitions and execute them. You're going to need this anyway. Personally we use Windows auth for this on our network and our devs have sufficient privileges in non-prod.
When the report gets deployed to SSRS you're likely going to change the data source anyway from report manager (or using whatever deployment tool you use) and at that point you can use a more restricted account although that account is still going to need exec privileges. We use a dedicated SQL report account on our servers so we have clear view of permissions. We also use a shared data source for most of our reports so the DBA can set it up once and our reports automatically use the server's configuration.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(67.2, 1%, 16%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ETP%3C/text%3E%3C/svg%3E)
Permissions to execute the proc, do not control visibility to the procs.
Your SSRS connection is almost certainly not connected to the server and/or database you think you are connecting too. Please check your connection string.
@Tom Phillips ... The SSRS connection is connected to the correct database and server.
When I use the read-only SQL account with this data source I have the problem.
When I give this SQL account dbo privs and use the same data source the problem goes away.
Depending on how SSRS is getting the information, the user must have VIEW DEFINITION rights to see the proc at all. The user must also be a member of the public role.
THAT did IT!!!!
Thanks Tom Phillips!!