Needed to create a SQL script based on coloum in the domain table in a SystDB Database

[Sonny]

I have a system which has 200+ databases and needed to run the following script automatically on each one of them.
The 200 databases names can be found in the databasename coloum in the domain table in one of the databases which is the system database.
So if I run the following script i get all the databases that I need to run the scripts below on.

DEMOSYSTEM DB
select databasename from domain.

This gives me the names of the databases
eg.
DB1
DB2
DB3 etc etc.

The scripts then that I need to run on DB1, DB2, DB3 is as below

GO
CREATE USER [Master60SP] FOR LOGIN [Master60SP]
GO
ALTER USER [Master60SP] WITH DEFAULT_SCHEMA=[Master60SP]
GO
CREATE SCHEMA [Master60SP] AUTHORIZATION [Master60SP]
GO
EXEC sp_addrolemember N'db_datareader', N'Master60SP'
GO
EXEC sp_addrolemember N'MSDynamicsSL', N'Master60SP'

How can I create a script that will run the scripts as above on all the databases whose names are in the domain tables/Coloum name Databasename

[Erland Sommarskog]

Sonny (hairn...@gmail.com) writes:
> I have a system which has 200+ databases and needed to run the following
> script automatically on each one of them. The 200 databases names can be
> found in the databasename coloum in the domain table in one of the
> databases which is the system database. So if I run the following script
> i get all the databases that I need to run the scripts below on.

What other languages beside T-SQL do you master? About any other of them
would be better suited for the task.

But you can do (note that this is untested, and is likely to include typos):

DECLARE @sp_executesql nvarchar(200),
@sp_addrolemember nvarchar(200)

DECLARE cur CURSOR STATIC LOCAL FOR
SELECT quotename(databasename) + '..sp_executesql',
quotename(databasename) + '..sp_addrolemember'
FROM domain

OPEN cur

WHILE 1 = 1
BEGIN
FETCH cur INTO @sp_executesql, @sp_addrolemember
IF @@fetch_status <> 0
BREAK

EXEC @sp_executesql N'CREATE USER [Master60SP] FOR LOGIN [Master60SP]'
EXEC @sp_executesql N'ALTER USER [Master60SP] WITH
DEFAULT_SCHEMA=[Master60SP]'
EXEC @sp_executesql N'CREATE SCHEMA [Master60SP] AUTHORIZATION
[Master60SP]'
EXEC @sp_addrolemember N'db_datareader', N'Master60SP'
EXEC @sp_addrolemember N'MSDynamicsSL', N'Master60SP'
END

DEALLOCATE cur

The key here is that you can specify the procedure to execute through a
variable. And when you call a system procedure in three-part notation, it
executes in the context of that database.



--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx