Posted on Leave a comment

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

Posted on Leave a comment

TSQL to control SQL Server Agent

To Start the sql server agent,
EXEC master.dbo.xp_ServiceControl ‘START’, ‘SQLServerAgent’

To Stop the sql server agent,
EXEC master.dbo.xp_ServiceControl ‘STOP’, ‘SQLServerAgent’

To Check the status of the sql server agent,
EXEC master.dbo.xp_ServiceControl ‘QueryState’, ‘SQLServerAgent’