locked
linked servers and roles RRS feed

  • Question

  • Hi guys!

    I strongly need your help and advice!

    On server host with Server 2008 R2 Enterprise i have a shared folder. There`s an Excel file in it with access-matrix for SSRS report (by windows logins)

    In SSMS 2014 i create a linked server to the excel file and i can`t execute "select * from openquery (excellink,'select * from [sheet1$]')". It returns an 7303 Error message, unless i`m given a role in Administrators group in Server Manager(via remote desktop connection).

    We recently migrated from another machine to the current one (with all the users,roles etc), but it all worked like magic then without granting any extra super access.

    I compared permissions, roles and users at both machines and didn`t find any sound differences.

    There`s an idea to grant somehow access in the shared folder to user MSSQLSERVER or add this user to administrators, but i can`t find this user in any environment.

    What`s the problem? How to allow all users use the linked server in queries like before?

    Thanx!

    Thursday, October 27, 2016 2:31 PM

Answers

  • SOLVED.

    I granted read/write permissions for user EVERYONE to c:\users\user_name\appdata\local\temp

    • Marked as answer by TZ_2000 Friday, October 28, 2016 10:42 AM
    Friday, October 28, 2016 10:42 AM

All replies

  • When you create a linked server you can specify an account to use in the security tab. You need to ensure that whatever windows account you use here can access the share and the physical files and folders that the excel sheet is on.
    • Proposed as answer by Teige Gao Friday, October 28, 2016 5:55 AM
    Thursday, October 27, 2016 2:47 PM
  • This shared folder has "Everyone" in shares with read/write full permissions.

    Is that it?

    Friday, October 28, 2016 7:00 AM
  • SOLVED.

    I granted read/write permissions for user EVERYONE to c:\users\user_name\appdata\local\temp

    • Marked as answer by TZ_2000 Friday, October 28, 2016 10:42 AM
    Friday, October 28, 2016 10:42 AM