wyRW6XLp

1Declare
2    @username sysname,
3    @password varchar(255),
4    @SQL nvarchar(max),
5    @RowsToProcess int,
6    @CurrentRow int 
7
8set @username = 'youruser'
9set @password = 'theirpassword'
10
11
12    SET @SQL = 'CREATE LOGIN ' + @username + ' WITH PASSWORD = ''' + @password + ''''
13    EXECUTE(@SQL);
14
15CREATE TABLE #maintenancetemp (RowID int not null primary key identity(1,1), sqlcmd nvarchar(max) )  
16
17insert into #maintenancetemp    
18SELECT 'USE ' + QUOTENAME(NAME) + '; 
19        IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N''' + @username + ''') CREATE USER ' + QUOTENAME(@username)
20       + ' FOR LOGIN ' + QUOTENAME(@username)
21       + ' WITH DEFAULT_SCHEMA=[dbo];
22    EXEC sys.sp_addrolemember ''db_datareader'',''' + @username + ''';
23    EXEC sys.sp_addrolemember ''db_datawriter'', ''' + @username + ''''
24FROM   sys.databases
25WHERE  database_id > 4
26       AND state_desc = 'ONLINE' 
27
28
29SET @RowsToProcess=@@ROWCOUNT
30
31SET @CurrentRow=0
32WHILE @CurrentRow<@RowsToProcess
33BEGIN
34    SET @CurrentRow=@CurrentRow+1
35    SELECT @SQL = sqlcmd FROM #maintenancetemp WHERE RowID=@CurrentRow
36        EXEC SP_EXECUTESQL @SQL
37        --print @SQL                
38END
39
40drop table #maintenancetemp