SHARE
TWEET

Untitled

SQLSoldier Jul 27th, 2013 79 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  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;
RAW Paste Data
Top