Advertisement
SQLSoldier

Untitled

Jul 27th, 2013
205
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
T-SQL 0.73 KB | None | 0 0
  1. Declare @MaxID int,
  2.     @CurrID int,
  3.     @SQL nvarchar(255),
  4.     @Debug bit = 0;
  5. Declare @DBs Table (DBID int identity(1, 1) primary key,
  6.     SQLText nvarchar(255));
  7.  
  8. Set NoCount On;
  9.  
  10. Insert Into @DBs (SQLText)
  11. Select N'ALTER AUTHORIZATION ON database::' + quotename(name) + N' To [sa];'
  12. From sys.databases
  13. Where owner_sid <> 0x01
  14. And name Not In (N'master', N'model', N'tempdb', N'distribution');
  15.  
  16. Select @MaxID = MAX(DBID),
  17.     @CurrID = 1
  18. From @DBs;
  19.  
  20. While @CurrID <= @MaxID
  21.   Begin
  22.     Select @SQL = SQLText
  23.     From @DBs
  24.     Where DBID = @CurrID;
  25.  
  26.     If @Debug = 1
  27.       Begin
  28.         Print @SQL;
  29.       End
  30.     Else
  31.       Begin
  32.         Print 'Running command: ' + @SQL;
  33.         Exec sp_executesql @SQL;
  34.       End
  35.  
  36.     Set @CurrID = @CurrID + 1;
  37.   End
  38.  
  39. Set NoCount Off;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement