Hello,
I recently ran a script to create a view, gave access to this view. Linked the MS query to this view. The report runs fine for SA but not for other users. What security does the users need to be able to access this excel report pulling data from sql?
Thanks for you help!
*This post is locked for comments
You are most welcome Shelly
This works perfectly! Thank you! =)
If you can implement Windows Authentication to your SQL Server, you can try using stored procedure to implement the same view, and grant execute for your other windows credential to the stored procedure.
Just make sure you create the stored procedure using your dbowner credential, then ownership chaining will take care of the permission
Why don't you try to connect the SQL server database with using window authentication?
Better you can provide SQL access to your windows users to SQL server database and the connect the database from EXCEL with using windows authentication mode instead of SQL server authentication.
Hope this helps!!!
Another option is to create an SQL user on the database level, which has appropriate access for the specific tables and view you created. Of course, this is definitely a better option to ensure that your "sa" password is not used and saved on separate Excel sheets across different computers.
Your feedback is highly appreciated,
In this case, you need to use an SQL user with proper access on the view and its associated tables. Dynamics GP user will not work of course since it is a different thing
You can consider saving the "sa" password on the Excel file for the users so that opening the file will not ask for the user name and password anymore, follow the steps below to save the password:
1- On the Excel file, open the "Data" tab, click on connections
2- One the connection string window is opening, click on the specific connection string and then "Properties"
3- On the properties tab, open the "definition" tab and check the "Save Password" option
Yes exactly!
Hello again ShellyAnn
Are you talking about a normal Excel file being linked to SQL view ? Which prompts asking for authentication as shown below:
Your feedback is highly appreciated,
Share
Report
All responses (
Answers (
32,738
