

/*
 Set Sql Server
*/

   Use [Master] 
   IF EXISTS (SELECT * FROM sys.server_principals WHERE name = N'PublicUser1')  
      DROP LOGIN [PublicUser1] 
                
   CREATE LOGIN PublicUser1 WITH PASSWORD ='Pr!n(3',   
   DEFAULT_DATABASE = Prince,        
   DEFAULT_LANGUAGE = Arabic, 
   CHECK_EXPIRATION = Off, 
   CHECK_POLICY = ON;   
   EXEC sp_addsrvrolemember 'PublicUser1', 'sysadmin'; 
   EXEC sp_addsrvrolemember 'PublicUser1', 'securityadmin'; 
   EXEC sp_addsrvrolemember 'PublicUser1', 'serveradmin'; 
   EXEC sp_addsrvrolemember 'PublicUser1', 'setupadmin'; 
   EXEC sp_addsrvrolemember 'PublicUser1', 'processadmin'; 
   EXEC sp_addsrvrolemember 'PublicUser1', 'diskadmin'; 
   EXEC sp_addsrvrolemember 'PublicUser1', 'dbcreator'; 
   EXEC sp_addsrvrolemember 'PublicUser1', 'bulkadmin'; 
   
   
 /*
 Set Permission
 */  
 
       Use [Prince]  
	   ALTER AUTHORIZATION ON SCHEMA::common TO dbo
	   GO
	 
	   ALTER AUTHORIZATION ON SCHEMA::Orginal TO dbo
	   GO
	 
	   ALTER AUTHORIZATION ON SCHEMA::VIP TO dbo
	   GO 
	   
	   ALTER AUTHORIZATION ON SCHEMA::Gold TO dbo
	   GO 
	   
	   DECLARE @FinancialYear  VARCHAR(2000),
	           @Query          VARCHAR(MAX)
	   
	   DECLARE POP CURSOR for
		   select FNY_Name from Common.FinancialYear 
	OPEN POP
	FETCH NEXT FROM POP INTO @FinancialYear
	WHILE @@FETCH_STATUS =0
	BEGIN     
        SET @Query = ''
        SET @Query = '
        ALTER AUTHORIZATION ON SCHEMA::['+@FinancialYear+'] TO dbo '
       -- print @Query
	   EXEC (@Query)
		 
	   FETCH NEXT FROM POP INTO @FinancialYear
    END
    CLOSE POP
    DEALLOCATE POP	  
	   
	 go 
	   
       IF  EXISTS (SELECT * FROM sys.database_principals WHERE name = N'PublicUser1') 
	       DROP USER [PublicUser1]               
       CREATE USER [PublicUser1] FOR LOGIN [PublicUser1] 
       ALTER USER [PublicUser1] WITH DEFAULT_SCHEMA=[dbo] 
       EXEC sp_addrolemember N'db_owner', N'PublicUser1' 
       USE Master; 
       GRANT ADMINISTER BULK OPERATIONS TO [PublicUser1];
       GRANT ALTER ANY CONNECTION TO [PublicUser1];
       GRANT ALTER ANY CREDENTIAL TO [PublicUser1];
       GRANT ALTER ANY DATABASE TO [PublicUser1];
       GRANT ALTER ANY ENDPOINT TO [PublicUser1];
       GRANT ALTER ANY EVENT NOTIFICATION TO [PublicUser1];
       GRANT ALTER ANY LINKED SERVER TO [PublicUser1];
       GRANT ALTER ANY LOGIN TO [PublicUser1];
       GRANT ALTER ANY SERVER AUDIT TO [PublicUser1];
       GRANT ALTER RESOURCES TO [PublicUser1];
       GRANT ALTER SERVER STATE TO [PublicUser1];
       GRANT ALTER SETTINGS TO [PublicUser1];
       GRANT ALTER TRACE TO [PublicUser1];
       GRANT AUTHENTICATE SERVER TO [PublicUser1];
       GRANT CONNECT SQL TO [PublicUser1];
       GRANT CONTROL SERVER TO [PublicUser1];
       GRANT CREATE ANY DATABASE TO [PublicUser1];
       GRANT CREATE DDL EVENT NOTIFICATION TO [PublicUser1];
       GRANT CREATE ENDPOINT TO [PublicUser1];
       GRANT CREATE TRACE EVENT NOTIFICATION TO [PublicUser1];
       GRANT EXTERNAL ACCESS ASSEMBLY TO [PublicUser1];
       GRANT SHUTDOWN TO [PublicUser1];
       GRANT UNSAFE ASSEMBLY TO [PublicUser1];
       GRANT VIEW ANY DATABASE TO [PublicUser1];
       GRANT VIEW ANY DEFINITION TO [PublicUser1];
       GRANT VIEW SERVER STATE TO [PublicUser1];
       
       
       
/*
Set Authentication Mode
*/       

       DECLARE @INSTANCEID VARCHAR(30) 
	   DECLARE @STRVERSION VARCHAR(30)               
       DECLARE @SQLVERSION VARCHAR(30) 
       DECLARE @CMD VARCHAR(2000) 
       SET @SQLVERSION = (SELECT CONVERT(VARCHAR(20),(SERVERPROPERTY('productversion')))) 
       SET @INSTANCEID = ((SELECT CAST(SERVERPROPERTY('InstanceName') AS VARCHAR))) 
       IF (SELECT REPLACE(LEFT(@SQLVERSION,2),'.','')) = 10 
       BEGIN 
          SET @STRVERSION = 'MSSQL10' 
       END 
       ELSE IF (SELECT REPLACE(LEFT(@SQLVERSION,2),'.','')) = 9 
       BEGIN 
          SET @STRVERSION = 'MSSQL' 
       END 
       IF @INSTANCEID IS NULL AND @STRVERSION = 'MSSQL' 
       BEGIN 
          SET @INSTANCEID = 1 
       END 
       ELSE IF @INSTANCEID IS NULL AND @STRVERSION = 'MSSQL10' 
       BEGIN 
          SET @INSTANCEID = 'MSSQLSERVER' 
       END 
       SET @CMD = 'xp_regwrite ' + 'N' + '''HKEY_LOCAL_MACHINE''' + ',' + ' N' + '''Software\Microsoft\Microsoft SQL Server\'+ @STRVERSION +'.'+ @INSTANCEID + '\MSSQLServer'''+','+' N'+'''LoginMode'''+', '+'REG_DWORD'+','+ ' 2' --2 is mixed auth. +
       EXEC(@CMD) 
       
       
/*
Set EnablingRemoteConnections
*/       

       Declare @inspath varchar(100),@regw varchar(100),@ke varchar(100) 
	   Declare @insname varchar(100),@ke1 varchar(100), @val varchar(5)               
       Declare @enable bit 
       if convert(varchar(25),serverproperty('instancename')) is null 
          set @insname='MSSQLSERVER' 
       else 
          set @insname=convert(varchar(25),serverproperty('instancename')) 
       EXEC master.dbo.xp_regread @rootkey='HKEY_LOCAL_MACHINE',  
                                  @key='Software\Microsoft\Microsoft SQL Server\Instance Names\SQL\',
                                  @value_name=@insname,
                                  @value=@inspath    
       set @regw='Software\Microsoft\Microsoft SQL Server\'+@inspath+'\MSSQLSERVER\SuperSocketNetLib\'  
       set @val='tcpnp' 
       set @ke=@regw+'tcp' 
       set @ke1=@regw+'np' 
       set @enable=1 
       EXEC master.dbo.xp_regwrite @rootkey='HKEY_LOCAL_MACHINE',@key=@ke1, @value_name='Enabled', @type='REG_DWORD',@value=1    
       EXEC master.dbo.xp_regwrite @rootkey='HKEY_LOCAL_MACHINE',@key=@ke, @value_name='Enabled', @type='REG_DWORD',@value=1 
       
       

