ZOUHAIER KHARROUBI

-- =============================================

-- Author:     KHARROUBI Zouhaier

-- Create date: 17/09/2021

-- Description: Afficher tous les comptes AD qui démarrent les services SQL Server

-- =============================================

BEGIN

       -- SET NOCOUNT ON added to prevent extra result sets from

       -- interfering with SELECT statements.

       SET NOCOUNT ON;

       IF EXISTS(SELECT * FROM tempdb..sysobjects WHERE id = object_id(N'[tempdb]..[#Serveurs_LiesTemp]'))

             BEGIN

                    DROP TABLE #Serveurs_LiesTemp

             END

       IF EXISTS(SELECT * FROM tempdb..sysobjects WHERE id = object_id(N'[tempdb]..[#Serveurs_Lies]'))

             BEGIN

                    DROP TABLE #Serveurs_Lies

             END

       IF EXISTS(SELECT * FROM tempdb..sysobjects WHERE id = object_id(N'[tempdb]..[#Resultat]'))

             BEGIN

                    DROP TABLE #Resultat

             END

       CREATE TABLE #Serveurs_LiesTemp

       (

       SRV_NAME                         VARCHAR(150),

       SRV_PROVIDERNAME           VARCHAR(150),

       SRV_PRODUCT                      VARCHAR(150),

       SRV_DATASOURCE                   VARCHAR(150),

       SRV_PROVIDERSTRING         VARCHAR(150),

       SRV_LOCATION               VARCHAR(150),

       SRV_CAT                                 VARCHAR(150)

       )                         

       CREATE TABLE #Serveurs_Lies

       (

       Num_Lig                                 INT IDENTITY (1,1),

       SRV_NAME                         VARCHAR(150),

       SRV_DATASOURCE                   VARCHAR(150),

       ServerName                       VARCHAR(150) NULL

       )

       CREATE NONCLUSTERED INDEX [IDX_#Serveurs_Lies_ServerName] ON #Serveurs_Lies

       (

             ServerName ASC

       )

       CREATE TABLE #Resultat

       (

       Num_Lig                                                    INT IDENTITY (1,1),

       Serveur_Lie                                          VARCHAR(150) NULL,

       InstanceSQLServer                             VARCHAR(150)    NULL,

       servicename                                          VARCHAR(150)    NULL,

    service_account                                  VARCHAR(150)    NULL,

       Requete                                                     NVARCHAR(MAX)   NULL,

       message_erreur_exec                           VARCHAR(max) NULL

       )

       DECLARE      @Num_Lig                        AS INT

                    ,@Nbre_Lig                       AS INT

                    ,@Serveur_Lie              AS VARCHAR(150)

                    ,@ServerName               AS NVARCHAR(4000)

                    ,@Requete                        AS NVARCHAR(MAX)

                    ,@LocalHot                       AS VARCHAR(50)

                    ,@message_erreur_exec      AS VARCHAR(max)    

    -- Insert statements for procedure here

       SELECT @LocalHot           = @@SERVERNAME

       INSERT INTO #Serveurs_LiesTemp

       EXEC sp_linkedservers 

       INSERT INTO #Serveurs_Lies (SRV_NAME,SRV_DATASOURCE)

       SELECT DISTINCT SRV_NAME,SRV_DATASOURCE FROM #Serveurs_LiesTemp

       SELECT @Num_Lig=1,@Nbre_Lig=COUNT(Num_Lig) FROM #Serveurs_Lies

       SELECT @Num_Lig=1,@Nbre_Lig=COUNT(Num_Lig) FROM #Serveurs_Lies

WHILE  @Num_Lig <= @Nbre_Lig

       BEGIN

             BEGIN TRY

                    SELECT @Serveur_Lie =SRV_DATASOURCE FROM #Serveurs_Lies WHERE Num_Lig=@Num_Lig

                    SELECT @Requete     = CASE WHEN @LocalHot<>@Serveur_Lie THEN N'SELECT @ServerName_Out=ServerName FROM OPENQUERY(' + QUOTENAME(@Serveur_Lie) + N',''SELECT @@SERVERNAME AS ServerName'')' ELSE 'SELECT @ServerName_Out=@@SERVERNAME' END

                    EXECUTE sp_executesql @Requete,N'@ServerName_Out VARCHAR(150) OUTPUT',@ServerName_Out=@ServerName OUTPUT

                    IF NOT EXISTS (SELECT ServerName FROM #Serveurs_Lies WHERE ISNULL(ServerName,'')=@ServerName)

                          BEGIN

                                 UPDATE #Serveurs_Lies

                                       SET

                                       ServerName=@ServerName

                                 WHERE Num_Lig=@Num_Lig

                                 SELECT @Requete = CASE WHEN @LocalHot<>@Serveur_Lie THEN  N'SELECT * FROM OPENQUERY(' + QUOTENAME(@Serveur_Lie) + ',''' ELSE '' END

                                 SELECT @Requete = @Requete + N'SELECT DISTINCT ''''' + @Serveur_Lie + ''''', @@SERVERNAME AS InstanceSQLServer,[servicename],[service_account] FROM [master].[sys].[dm_server_services]'

                                 SELECT @Requete = CASE WHEN @LocalHot<>@Serveur_Lie THEN @Requete + N''')' ELSE REPLACE(@Requete,'''''','''') END

                                 PRINT @Requete

                                 INSERT INTO #Resultat (Serveur_Lie,InstanceSQLServer,servicename,service_account)

                                 EXECUTE sp_executesql @Requete   

                          END

             END TRY

             BEGIN CATCH

                    SELECT @Serveur_Lie AS Serveur_Lie, ERROR_MESSAGE() AS Message_Erreur

                    INSERT INTO #Resultat (Serveur_Lie,Requete,message_erreur_exec)

                    VALUES (@Serveur_Lie,@Requete,ERROR_MESSAGE())

             END CATCH

             SELECT  @Num_Lig =   @Num_Lig + 1

       END

SELECT * FROM #Resultat

DROP TABLE #Serveurs_LiesTemp

DROP TABLE #Serveurs_Lies

DROP TABLE #Resultat

END