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
Month: June 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
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’