1 - Creating an MSQuery User

2 - Creating an MS Query group/Role

3 - Executing an MSQuery and returning results to MSExcel

• Open

  

In SSMS - map the user to the Synergetic database (no role memberships)

In Synergetic create a MSQuery group in Group/User Security

Synergetic

In Group User Security select the newly created group

Select Resources → SQL Server Objects

Select Create SQL Group Permissions

If it displays DELETE SQL Group then Create SQL Group/Permissions has already been done

Select for the objects (views and/or tables) you want this MSQuery group to have access to ie.

SSMS

Note : these steps to be actioned by the Synergetic Database administrator.

Select Database Name → Security → Roles → Database Roles

A new role will be created called Syn_{GroupName_from_Synergetic}_dbo   (* will be either '..._dbo' or '..._finance' depending on schema)

Select that role and properties

You will see the objects that role has access to which should match Synergetic group user security (zspSynergeticReservered may be auto assigned)

In SSMS select database→Security → Users → (msquery user)

select properties of user → Membership

Add user to the MSQuery Role

OR

If the user is a Synergetic User you can add them to the group in Synergetic

Result - you have now created a user, a group (DB role) and assigned that user to that Role

As a test log into SSMS as that user

Select Database → Tables and Views

You should only see the tables and views assigned to that role earlier (note Constituencies and pvSynDatabases are public so auto assigned)

Now the Query part

Run MS Excel and select a Blank Workbook

Select Data Menu

Select Get Data → From Other Sources → From Microsoft Query

Select Data Source = Synergetic - it uses the same datasource you use to connect to SynMain so for most users this is Synergetic (this comes from your ODBC data sources)

select OK

For SQL Server Login

Enter the Login ID and Password you created in First Step

The database should be prepopulated (from the datasource)

If you wish to login as your Windows account then select Use Trusted Connection

In Choose Columns Wizard - Select Options

Filter on the relevant tables Show Tables , Views and Owner = dbo (or finance)

Select the tables and columns you wish to display

Set your filters (Where clauses)

and Sort Order (if required)

Return to Excel will display the result back into MSExcel

View data or edit query in Microsoft Query allows to to continue manipulating the data

At the top menu select SQL to show the SQL query

Select the Return Data - to return to Excel (Cell A1 is a good starting point)

Data is now in Excel

Tips to tweak the connection string

This is useful if you have received an Excel Spreadsheet containing an MSQuery. This will be preconfigured to link to the schools database. You may wish to run this query on another database or datasource

Open the Query in MSExcel (you may get errors due to invalid database name/connection string)

To point to a new datasource

Select Data → Properties

Select the connection Properties Icon(Top RHS)

Select Definition

Connection String - change DATABASE= to the Local database

Command Text - this may also contain the database name. It is ok to remove the ‘database.dbo’ section

Select OK to save