locked
SQL Server security settings to allow SSRS access via SharePoint Integrated mode RRS feed

  • Question

  • Hi,

    SQL Server 2008 R2 and SharePoint (MOSS) 2007.

    I have deployed reports to a SharePoint library and secured the library accordingly. I have a SQL Server side login (Server > Security > Logins) and my Server Roles are dbcreator, public, securityadmin, sysadmin. Now when I add additional users in SharePoint and set their access accordingly, I know I have to also create a login in SQL Server (which is where the source data and relevant SQL Server objects reside). I have done so and created the following setup:

    Server > Security > Logins > DOMAIN\NEWUSER

    Server Role > Public

    User Mapping > Specific Database as db_Datareader and Public (default)

    BUT, this does not work and the users still get access errors when running the reports. The only way I could get this to work was with the following setup:

    Server > Security > Logins > DOMAIN\NEWUSER

    Server Role > Public & Sysadmin

    User Mapping > None

    They can now access the report, but this seems very unsecure. Sysadmin does not seem like something I want to be granting them.

    What am I doing wrong?


    Friday, July 1, 2011 12:47 AM

Answers

  • I have answered my own question here. I have removed sysadmin, set up an appropriate database role (db_RptAccess), created database level users, allocated the database level users to the new role and set the secrables on the role to select or exec (depending on if its a table or a sproc)
    Friday, July 1, 2011 5:37 AM