Wednesday, June 22, 2016

Change DB Owner to SA

SELECT SUSER_SNAME(A.owner_sid) "current_owner"
     , A.name "database_name"
     , 'ALTER AUTHORIZATION ON DATABASE::[' + A.name + '] TO [sa]; '
     + 'USE [' + name + ']; CREATE USER [' + SUSER_SNAME(A.owner_sid) + '] FOR LOGIN [' + SUSER_SNAME(A.owner_sid) + '] WITH DEFAULT_SCHEMA=[dbo]; '
     + 'ALTER ROLE [db_owner] ADD MEMBER [' + SUSER_SNAME(A.owner_sid) + '];' 
--   + 'EXEC sp_addrolemember N''db_owner'', N''' + SUSER_SNAME(A.owner_sid) + ''';' 
       "cmd"
  FROM sys.databases A
 WHERE SUSER_SNAME(A.owner_sid) NOT IN ( 'sa')
   AND A.[state] = 0
 ORDER BY A.name

No comments:

Post a Comment