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
Wednesday, June 22, 2016
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'
Subscribe to:
Posts (Atom)