Path: csiph.com!v102.xanadu-bbs.net!xanadu-bbs.net!feeder.erje.net!eu.feeder.erje.net!eternal-september.org!feeder.eternal-september.org!mx05.eternal-september.org!.POSTED!not-for-mail
From: Erland Sommarskog <esquel@sommarskog.se>
Newsgroups: comp.databases.ms-sqlserver
Subject: Re: Needed to create a SQL script based on coloum in the domain table in a SystDB Database
Date: Mon, 15 Apr 2013 07:31:31 +0000 (UTC)
Organization: Erland Sommarskog
Lines: 52
Message-ID: <XnsA1A3615F2BAECYazorman@127.0.0.1>
References: <9ccc371d-d385-4b7f-82b0-a583350219d4@googlegroups.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Injection-Date: Mon, 15 Apr 2013 07:31:31 +0000 (UTC)
Injection-Info: mx05.eternal-september.org; posting-host="5d404a5d864426cbf50ddbcd9433a6e4"; logging-data="8907"; mail-complaints-to="abuse@eternal-september.org";	posting-account="U2FsdGVkX1//2jfqNg2eRnQ9z3pfI3PN"
User-Agent: Xnews/2005.10.03 Mime-proxy/1.4.c.4 (Win32)
Cancel-Lock: sha1:ANfD53g5zXQVBNbtWO5/Nvxp40U=
Xref: csiph.com comp.databases.ms-sqlserver:1445

Sonny (hairnhenna@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, esquel@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