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

Thursday, June 16, 2016

Enable AD module for PowerShell in Windows Server 2012R2

To enable AD module for PowerShell, fire up Server Manager and go to Features->Remote Server Administration Tools -> Role Administration Tools -> AD DS and AD LDS Tools -> Active Directory module for Windows PowerShell


Friday, June 10, 2016

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'