Run a Script Against Multiple Companies by Creating SQL from SQL

This subject isn't the grand topic I had envisioned for my initial post but is a neat little trick I think many would find useful. So here goes the blog!

We recently had to create users and assign SQL permissions to over 200 Dynamics GP companies for an integration. The integration is external to the Dynamics GP client and accesses the SQL databases directly rather than through the client, so SQL security had to be setup. There are a couple of options:

• Click through SQL Management, creating users and assigning to the appropriate role (very tedious).
• Create a SQL script that sets up the security and run it against each company database (slightly less tedious).

Let’s rule out the first option right away. With the second option, the script might look something like this:

CREATE USER [MYDOMAIN\INTEGRATION USERS] FROM LOGIN ''MYDOMAIN\INTEGRATION USERS''

GO

EXEC sp_addrolemember [Integration Users], [MYDOMAIN\INTEGRATION USERS]

GO

Here we’re creating a user in the database and adding it to the appropriate role. However, we still have to run it against each company databases. Pick database from dropdown, click execute, wait, repeat.

If only there were an easy way to generate a script that would run against each database.

Well there is! With a simple SQL statement we can generate a script that will run against every company.

--  Don't forget to set Results to Text in your
--  SQL Management Studio query window.
--
--  Set NOCOUNT on so that row count doesn't show
--  up in the text results.
SET NOCOUNT ON;

SELECT
REPLACE(
'
USE {COMPANYDB}
GO

CREATE USER [MYDOMAIN\INTEGRATION USERS] FROM LOGIN [MYDOMAIN\INTEGRATION USERS]

GO

EXEC sp_addrolemember ''Integration Users'', ''MYDOMAIN\INTEGRATION USERS''

GO
',
'{COMPANYDB}', INTERID)
FROM DYNAMICS.dbo.SY01500

In the SQL statement above we’ve taken our security script, added a USE statement at the top to set the company and placed it inside a SQL statement. Note that the USE statement has tag {COMPANYDB} instead of a SQL database name. The SELECT statement replaces this with the INTERID from the SY01500 GP companies table in the DYNAMICS database.

This statement will create a single row for each company containing a single column with the resulting security script. When concatenated together you get the following script (this was run against a GP instance with two companies):

USE AAA
GO

CREATE USER [MYDOMAIN\INTEGRATION USERS] FROM LOGIN [MYDOMAIN\INTEGRATION USERS]

GO

EXEC sp_addrolemember 'Integration Users', 'MYDOMAIN\INTEGRATION USERS'

GO

USE TWO
GO

CREATE USER [MYDOMAIN\INTEGRATION USERS] FROM LOGIN [MYDOMAIN\INTEGRATION USERS]

GO

EXEC sp_addrolemember 'Integration Users', 'MYDOMAIN\INTEGRATION USERS'

GO

To get this to work in SQL Management Studio we need to send the results to text instead of the grid. This can be set in the Query/Results To menu as shown here

or clicking on the To Text button in the tool bar shown below:

Note that by default, the maximum number of characters show column per row when sending results to text is 256. This can be changed in Tools/Options screen, navigating to Query Results/SQL Server/Results to Text and updating the Maximum number of characters in each column to the maximum number of characters in your company script.

This trick could be used for any number of tasks that need to be run against every company such as creating custom stored procedures or performing mass backups and restores into test environments.