SE の雑記

;SQL Server 2012 Configuration File
[OPTIONS]
PCUSOURCE=".PCU"
CUSOURCE=".CU"

; Windows account(s) to provision as SQL Server system administrators.
SQLSYSADMINACCOUNTS="NT AUTHORITYSYSTEM"

<?xml version="1.0" encoding="UTF-16"?>
<Task version="1.4" xmlns="http://schemas.microsoft.com/windows/2004/02/mit/task">
  <RegistrationInfo />
  <Triggers>
    <BootTrigger>
      <Enabled>true</Enabled>
    </BootTrigger>
    <EventTrigger>
      <Enabled>true</Enabled>
      <Subscription>&lt;QueryList&gt;&lt;Query Id='0' Path='Application'&gt;&lt;Select Path='Application'&gt;*[System[Provider[@Name='Microsoft-Windows-Security-SPP'] and EventID=1003]]&lt;/Select&gt;&lt;/Query&gt;&lt;/QueryList&gt;</Subscription>
    </EventTrigger>
  </Triggers>
  <Principals>
    <Principal id="Author">
      <UserId>S-1-5-18</UserId>
      <RunLevel>HighestAvailable</RunLevel>
    </Principal>
  </Principals>
  <Settings>
    <MultipleInstancesPolicy>IgnoreNew</MultipleInstancesPolicy>
    <DisallowStartIfOnBatteries>true</DisallowStartIfOnBatteries>
    <StopIfGoingOnBatteries>true</StopIfGoingOnBatteries>
    <AllowHardTerminate>false</AllowHardTerminate>
    <StartWhenAvailable>false</StartWhenAvailable>
    <RunOnlyIfNetworkAvailable>false</RunOnlyIfNetworkAvailable>
    <IdleSettings>
      <StopOnIdleEnd>true</StopOnIdleEnd>
      <RestartOnIdle>false</RestartOnIdle>
    </IdleSettings>
    <AllowStartOnDemand>true</AllowStartOnDemand>
    <Enabled>true</Enabled>
    <Hidden>false</Hidden>
    <RunOnlyIfIdle>false</RunOnlyIfIdle>
    <DisallowStartOnRemoteAppSession>false</DisallowStartOnRemoteAppSession>
    <UseUnifiedSchedulingEngine>false</UseUnifiedSchedulingEngine>
    <WakeToRun>false</WakeToRun>
    <ExecutionTimeLimit>PT72H</ExecutionTimeLimit>
    <Priority>7</Priority>
  </Settings>
  <Actions Context="Author">
    <Exec>
      <Command>C:\Program Files\Microsoft SQL Server\130\Setup Bootstrap\SQLServer2016\setup.exe</Command>
      <Arguments>/ACTION=CONFIGUREIMAGE /USEMICROSOFTUPDATE /IACCEPTSQLSERVERLICENSETERMS /INSTANCENAME="MSSQLSERVER" /Q</Arguments>
    </Exec>
  </Actions>
</Task>

(01) 2013-09-22 10:10:01 Slp: Connection string: Data Source=\.pipeSQLLocalMSSQLSERVER;Initial Catalog=master;Integrated Security=True;Pooling=False;Connect Timeout=300;Network Library=dbnmpntw;Application Name=SqlSetup
(01) 2013-09-22 10:10:03 Slp: Connected successfully...
(01) 2013-09-22 10:10:03 Slp: Sco: Attempting to get machine administrator logon account name
(01) 2013-09-22 10:10:03 Slp: Sco: Attempting to get sid for machine domain SQLDEPLOY
(01) 2013-09-22 10:10:03 Slp: Sco: Attempting to get NT account from sid S-1-5-21-138499402-210688190-3290417106-500
(01) 2013-09-22 10:10:03 Slp: Sco: Attempting to get account from sid S-1-5-21-138499402-210688190-3290417106-500
(01) 2013-09-22 10:10:03 Slp: Sco: Attempting to get local system account name
(01) 2013-09-22 10:10:03 Slp: Sco: Attempting to get NT account from sid S-1-5-18
(01) 2013-09-22 10:10:03 Slp: Sco: Attempting to get account from sid S-1-5-18
(01) 2013-09-22 10:10:04 Slp: Sco: Attempting to execute script /************************************************************/
-- Secript to Rename SqlServer name as machine name.
--
/************************************************************/
SET NOCOUNT ON
DECLARE @MachineName VARCHAR(100), @InstanceName VARCHAR(100), @ServerName VARCHAR(100),  @local_machine_admin VARCHAR(100)
-- Get the SQL Server installed Machine Name
SET  @MachineName = CONVERT(VARCHAR(100), SERVERPROPERTY('machinename'));
-- Get the Sql Serve instance Name
SET  @InstanceName = CONVERT(VARCHAR(100),SERVERPROPERTY('instancename'));
--Get the SQL Server name
SET @ServerName = CONVERT(VARCHAR(100),(SELECT @@SERVERNAME));
--Get current logged-in user
SET @local_machine_admin = 'SQLDEPLOYadmini';
 IF (@ServerName != @MachineName)
 BEGIN
-- check the instance name exist or not.
IF ((@InstanceName is null) OR (@InstanceName = ''))
	BEGIN
		-- SQL Server instance name is empty
		-- Configure SQL Server new name as Machine
		-- Remove the Current Server name
		exec sp_dropserver @server=@ServerName,@droplogins='droplogins';
		-- Add New server name
		exec sp_addserver @server=@MachineName,@local='local';
		-- Print New Server name
		SELECT @MachineName;
	END
ELSE
	BEGIN
		-- SQL Server has an instance name
		-- Configure SQL Server new name as Machine with intstance name.
		-- Remove the Current Server name
		exec sp_dropserver @server=@ServerName,@droplogins='droplogins';;
		-- Add New server name
		SET @ServerName = @MachineName + '' + @InstanceName;
		exec sp_addserver @server=@ServerName,@local='local';
		-- Print New Server name
		SELECT @MachineName + '' + @InstanceName;
	END
END
BEGIN TRANSACTION
if not exists (select loginname from [master].[dbo].[syslogins] where UPPER([name]) = UPPER(@local_machine_admin))
BEGIN
---Add Current logged-in user to sysadmin role, Drop BuiltinAdministrator group
EXEC('CREATE LOGIN [' + @local_machine_admin + '] FROM WINDOWS WITH DEFAULT_DATABASE=[master]');
--Add sysadmin role to the created login
EXEC('ALTER SERVER ROLE [sysadmin] ADD MEMBER [' + @local_machine_admin +']')
END
if exists (select loginname from [master].[dbo].[syslogins] where UPPER([name]) = UPPER('NT AUTHORITYSYSTEM'))
and
 exists (select loginname from [master].[dbo].[syslogins] where UPPER([name]) = UPPER(@local_machine_admin))
BEGIN
DROP LOGIN [NT AUTHORITYSYSTEM]
END
COMMIT
--- Stop and start the SQL Server.

(01) 2013-09-22 20:23:44 Slp: Connection string: Data Source=\.pipeSQLLocalMSSQLSERVER;Initial Catalog=master;Integrated Security=True;Pooling=False;Connect Timeout=300;Network Library=dbnmpntw;Application Name=SqlSetup
(01) 2013-09-22 20:23:45 Slp: Connected successfully...
(01) 2013-09-22 20:23:45 Slp: Sco: Attempting to execute script /************************************************************/
-- Secript to Rename SqlServer name as machine name.
--
/************************************************************/
SET NOCOUNT ON
DECLARE @MachineName VARCHAR(100), @InstanceName VARCHAR(100), @ServerName VARCHAR(100),  @current_user VARCHAR(100)
-- Get the SQL Server installed Machine Name
SET  @MachineName = CONVERT(VARCHAR(100), SERVERPROPERTY('machinename'));
-- Get the Sql Serve instance Name
SET  @InstanceName = CONVERT(VARCHAR(100),SERVERPROPERTY('instancename'));
--Get the SQL Server name
SET @ServerName = CONVERT(VARCHAR(100),(SELECT @@SERVERNAME));
--Get current logged-in user
SET @current_user = SYSTEM_USER;
 IF (@ServerName != @MachineName)
 BEGIN
-- check the instance name exist or not.
IF ((@InstanceName is null) OR (@InstanceName = ''))
	BEGIN
		-- SQL Server instance name is empty
		-- Configure SQL Server new name as Machine
		-- Remove the Current Server name
		exec sp_dropserver @server=@ServerName,@droplogins='droplogins';
		-- Add New server name
		exec sp_addserver @server=@MachineName,@local='local';
		-- Print New Server name
		SELECT @MachineName;
	END
ELSE
	BEGIN
		-- SQL Server has an instance name
		-- Configure SQL Server new name as Machine with intstance name.
		-- Remove the Current Server name
		exec sp_dropserver @server=@ServerName,@droplogins='droplogins';;
		-- Add New server name
		SET @ServerName = @MachineName + '' + @InstanceName;
		exec sp_addserver @server=@ServerName,@local='local';
		-- Print New Server name
		SELECT @MachineName + '' + @InstanceName;
	END
END
BEGIN TRANSACTION
if not exists (select loginname from [master].[dbo].[syslogins] where UPPER([name]) = UPPER(@current_user))
BEGIN
---Add Current logged-in user to sysadmin role, Drop BuiltinAdministrator group
EXEC('CREATE LOGIN [' + @current_user + '] FROM WINDOWS WITH DEFAULT_DATABASE=[master]');
--Add sysadmin role to the created login
EXEC('ALTER SERVER ROLE [sysadmin] ADD MEMBER [' + @current_user +']')
END
if exists (select loginname from [master].[dbo].[syslogins] where UPPER([name]) = UPPER('BUILTINAdministrators'))
BEGIN
DROP LOGIN [BUILTINAdministrators]
END
COMMIT
--- Stop and start the SQL Server.