Reverse de logins SQL Server 2000

Introduction

Sous SQL Server, syslogins n’est pas une table système comme sous Sybase, il s’agit en fait d’une vue sur la table sysxlogins.

Cet article présente quelques particularités sur la table sysxlogins et des méthodes pour générer un reverse des logins dans un serveur SQL Server avec les procédures stockées sp__dba_hexadecimal et sp__dba_revlogin.

Table sysxlogins

Brièvement, la table sysxlogins (comparativement à la table syslogins sous Sybase) comporte les particularités ci-dessous :

Colonne	Type	Description
`srvid`	`smallint`	Identifiant du serveur rattaché au login `NULL` : login du serveur > 1 : login pour un serveur distant
`sid`	`varbinary(85)`	Security Identifier (identifiant de sécurité), c’est l’équivalent de `suid` sous Sybase, toutefois sous SQL Server, cet identifiant est de type `varbinary`.
`xstatus`	`smallint`	Informations de statut sur les logins (authentification NT ou SQL Server, etc.).
`password`	`varbinary(256)`

À la différence de Sybase, la correspondance entre les logins (syslogins) et les utilisateurs dans les bases de données (sysusers) est réalisée avec la colonne SID de type varbinary(85).

Authentification

Deux méthodes d’authentification sont disponibles sous SQL Server : l’authentification NT Windows - AD (Active Directory) / LDAP et l’authentification SQL Server.

Il existe une méthode simple pour déterminer si il s’agit d’une authentification Windows ou SQL Server :

pour déterminer les logins avec authentification Windows sous SQL Server :

select name from sysxlogins where xstatus & 4 = 4
go

name
-------------------------------------------------------
CGC\OLAP Administrators
CGC\SQLServerAdmin

pour déterminer les logins SQL Server :

select name from sysxlogins where xstatus & 4 != 4
go

name
-------------------------------------------------------
Northwind_dbo
Northwind_reader
Northwind_writer
pubs_dbo
pubs_writer
sa

Authentification Windows/AD : sp_grantlogin, sp_denylogin, sp_revokelogin

Les procédures sp_grantlogin et sp_denylogin ajoutent un login pour un compte utilisateur ou un groupe.

Syntaxes:

exec sp_grantlogin [@loginame =] 'login'
exec sp_denylogin [@loginame =] 'login'

Le login doit être un compte ou un groupe au format Domain\User. La procédure sp_grantlogin créé le login et autorise l’accès alors que la procédure sp_denylogin créé le login mais n’autorise pas l’accès au serveur SQL.

Exemple :

exec sp_grantlogin @loginame='BUILTIN\Administrateurs'
exec sp_denylogin @loginame='BUILTIN\Administrateurs'

Il est possible de déterminer, en requêtant xstatus dans la table sysxlogins, les logins associés à des comptes ou groupes pour lesquels l’accès au serveur est autorisé ou pas :

pour déterminer les comptes ou groupes Windows autorisés à accéder au serveur :

select name from sysxlogins where xstatus & 4 = 4 and xstatus & 1 != 1
go

name
-------------------------------------------------------------
CGC\SQLServerAdmin

pour déterminer les comptes ou groupes Windowq créés mais non autorisés à accéder au serveur :

select name from sysxlogins where xstatus & 4 = 4 and xstatus & 1 = 1
go

name
-------------------------------------------------------------
CGC\OLAP Administrators

La colonne xstatus de la table sysxlogins est également en mesure d’indiquer si il s’agit d’un compte utilisateur ou d’un groupe Windows :

pour déterminer si il s’agit d’un compte Windows :

select name from sysxlogins where xstatus & 4 = 4 and xstatus & 12 = 12
go

name
-------------------------------------------------------------
CGC\SQLServerAdmin

pour déterminer si il s’agit d’un groupe Windows :

select name from sysxlogins where xstatus & 4 = 4 and xstatus & 12 = 4
go

name
-------------------------------------------------------------
CGC\OLAP Administrators

Le mot de passe est nécessairement celui du compte et ne peut en aucun être modifié avec la procédure sp_password, il doit être modifié par le système de sécurité Windows ou dans l’annuaire Active Directory/LDAP.

La procédure sp_revokelogin est utilisée pour supprimer un login associé à un compte ou groupe Windows :

exec sp_revokelogin [@loginame =] 'login'

La base de données par défaut des logins associés à des comptes Windows est la base master et le langage par défaut est celui du serveur, pour modifier ces paramètres, les procédures sp_defaultdb et sp_defaultlanguage doivent être appliquées.

exec sp_defaultdb [@loginame =] 'login', [@defdb =]'database'
exec sp_defaultlanguage [@loginame =] 'login', [@language =]'language'

Exemple :

exec sp_defaultdb @loginame='BUILTIN\Administrateurs', @defdb='master'
exec sp_defaultlanguage @loginame='BUILTIN\Administrateurs', @language='us_english'

Authentification SQL Server : sp_addlogin, sp_droplogin

Les logins propres à SQL Server (authentification SQL Server) sont ajoutés avec les procédures stockées sp_addlogin et sp_droplogin.

Il y a seulement 2 particularités à noter sur la procédure stockée sp_addlogin sous SQL Server (comparativement à Sybase) : en effet la procédure sp_addlogin accepte en paramètres le SID (System Identifier) et le mot de passe crypté, ceci facilite grandement les tâches d’administration pour resynchroniser les logins entre deux serveurs SQL Server.

exec sp_addlogin [ @loginame = ] 'login'
  [ , [ @passwd = ] 'password' ]
  [ , [ @defdb = ] 'database' ]
  [ , [ @deflanguage = ] 'language' ]
  [ , [ @sid = ] sid ]
  [ , [ @encryptopt = ] 'encryption_option' ]

Forcer le SID à la création d’un login

Avec la syntaxe MS SQL Server, le SID (Security Identifier number) peut être forcé lors de la création du login.

Exemple :

exec master..sp_addlogin 'Northwind_reader', @pwd='Northwind_pwd',
       @defdb ='Northwind' , @deflanguage = 'us_english' ,
       @sid = 0x68DE5743C9540144989407CE9AA5C30F

Forcer un mot de passe crypté à la création d’un login

Le dernier paramètre @encryptopt de la procédure stockée sp_addlogin permet d’indiquer à SQL Server lors de la création du login si le mot de passe est déjà crypté et doit être conservé tel quel ou non. Trois valeurs sont possibles pour ce paramètre @encryptopt.

`NULL`	Le mot de passe doit être encrypté, c’est la valeur par défaut de ce paramètre
`skip_encryption`	Le mot de passe est déjà encrypté. SQL Server doit stocker la valeur dans `sysxlogins` sans effectuer d’encryptage
`skip_encryption_old`	Le mot de passe fourni a été encrypté avec une version antérieure à SQL Server 7. SQL Server doit stocker la valeur sans effectuer d’encryptage. Cette option doit être fournie pour les logins ayant subi des upgrades SQL Server 6.5 vers SQL Server 7 ou 2000

Lorsqu’il s’agit d’un login qui a subi un upgrade de SQL Server 6.5 vers 7.0 ou 8.0 (2000), l’option 'skip_encryption_old' est spécifiée (le mode de cryptage de la fonction pwdencrypt et l’utilisation de la fonction pwdcompare sont en effet modifiés entre la version 6.5 et la version 7.0) .

La requête ci-dessous retourne les logins issus d’un serveur ayant subi un upgrade depuis la version 6.5 :

select name from sysxlogins where xstatus & 2048 = 2048
go

Exemple :

declare @pwd sysname
set @pwd = CONVERT (varbinary(256),
0x0100F1068A1A97557737A25B33C9D64204DDD8C4D306C463E8190E513D6E39F6388493BD18BF6D4716D7E35F6A38
)
exec master..sp_addlogin 'pubs_dbo', @pwd, @defdb ='pubs' ,
@deflanguage = 'us_english' ,
@sid = 0x2E2225C98DF33A49BEE81D255A34E496, @encryptopt = 'skip_encryption'

Reverse de logins avec sp__dba_revlogin

La procédure stockée sp__dba_revlogin réalise le reverse de logins (sauf pour le login sa).

Usage :

exec sp_dba__revlogin

Cette procédure stockée a besoin de la procédure sp__dba_hexadecimal qui convertit du varbinary(256) en varchar(256).

Exemple :

exec sp_dba__revlogin

    DECLARE @pwd sysname
    -- Generating creation for login: CGC\OLAP Administrators
    PRINT 'Creating login [CGC\OLAP Administrators]'
    EXEC master..sp_grantlogin 'CGC\OLAP Administrators'
    EXEC master..sp_defaultdb @loginame='CGC\OLAP Administrators', @defdb='master'
    EXEC master..sp_defaultlanguage @loginame='CGC\OLAP Administrators', @language='us_english'
    -- Generating creation for login: CGC\SQLServerAdmin
    PRINT 'Creating login [CGC\SQLServerAdmin]'
    EXEC master..sp_grantlogin 'CGC\SQLServerAdmin'
    EXEC master..sp_defaultdb @loginame='CGC\SQLServerAdmin', @defdb='master'
    EXEC master..sp_defaultlanguage @loginame='CGC\SQLServerAdmin', @language='us_english'
    -- Generating creation for login: Northwind_dbo
    PRINT 'Creating login [Northwind_dbo]'
    SET @pwd = CONVERT (varbinary(256), 0x0100A52BDE15615A1916E8751F4982FEDD01A7D9F81607FD228ED18070E2412292BFE41231984B9B156BB129DCD1)
    EXEC master..sp_addlogin 'Northwind_dbo', @pwd, @defdb ='Northwind' , @deflanguage = 'us_english' ,
                             @sid = 0xE6940D5E39A94E4F8533BC55968C492E, @encryptopt = 'skip_encryption'
    -- Generating creation for login: northwind_reader
    PRINT 'Creating login [northwind_reader]'
    SET @pwd = CONVERT (varbinary(256), 0x01000B4238158A71E34C9A5C72B9AA0699B555B70AC211DED5337E32614F878B0E178C7ABCE3818428B0FBC9ACBC)
    EXEC master..sp_addlogin 'northwind_reader', @pwd, @defdb ='Northwind' , @deflanguage = 'us_english' ,
                             @sid = 0x33CFB95F2391BF46B1A9842D0AE6A458, @encryptopt = 'skip_encryption'

Le script généré peut ainsi être appliqué sur un nouveau serveur SQL Server permettant ainsi de recréer des logins avec les bases de données, les langages, les SIDs et les mots de passe.

    Creating login [CGC\OLAP Administrators]
    Granted login access to 'CGC\OLAP Administrators'.
    Default database changed.
    CGC\OLAP Administrators's default language is changed to us_english.
    Creating login [CGC\SQLServerAdmin]
    Granted login access to 'CGC\SQLServerAdmin'.
    Default database changed.
    CGC\SQLServerAdmin's default language is changed to us_english.
    Creating login [Northwind_dbo]
    New login created.
    Creating login [northwind_reader]
    New login created.

Code source sp__dba_hexadecimal

    CREATE PROCEDURE sp__dba_hexadecimal
    @binvalue varbinary(256),
    @hexvalue varchar(256) OUTPUT
    AS
    /**
    * Génère un script de conversion du varbinary(256) en varchar(256), valeur hexadécimale
    *
    *
    * Usage : sp__dba_hexadecimal
    * Date : 11.2004
    * Author :
    *
    * Version : 1.0 SQL Server 7 & 8 (2000)
    */
    DECLARE @charvalue varchar(256)
    DECLARE @i int
    DECLARE @length int
    DECLARE @hexstring char(16)
    SELECT @charvalue = '0x'
    SELECT @i = 1
    SELECT @length = DATALENGTH (@binvalue)
    SELECT @hexstring = '0123456789ABCDEF'
    
    WHILE (@i <= @length)
      BEGIN
       DECLARE @tempint int
       DECLARE @firstint int
       DECLARE @secondint int
       SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1))
       SELECT @firstint = FLOOR(@tempint/16)
       SELECT @secondint = @tempint - (@firstint*16)
       SELECT @charvalue = @charvalue +
              SUBSTRING(@hexstring, @firstint+1, 1) +
              SUBSTRING(@hexstring, @secondint+1, 1)
       SELECT @i = @i + 1
      END
   SELECT @hexvalue = @charvalue
    GO

Code source sp__dba_revlogin

    CREATE PROCEDURE sp__dba_revlogin @login_name sysname = NULL AS
    /**
    * Génère un script automatique de reverse des logins
    *
    * Usage : sp__dba_revlogin @login_name
    * Date : 11.2004
    * Author : Stephane PAQUOT
    *
    * Version : 1.0 SQL Server 7 & 8 (2000)
    */
    DECLARE @name sysname
    DECLARE @xstatus int
    DECLARE @binpwd varbinary (256)
    DECLARE @txtpwd sysname
    DECLARE @defdbname sysname
    DECLARE @deflanguage sysname
    DECLARE @tmpstr varchar (256)
    DECLARE @SID_varbinary varbinary(85)
    DECLARE @SID_string varchar(256)
    
    /** Declaration du curseur : tous les logins ou aucun */
    IF (@login_name IS NULL)
    
      DECLARE c_login_curs CURSOR FOR
      
      /** Recuperation de tous les logins (logins non remote ) */
      SELECT a.sid, a.name, a.xstatus, a.password, b.name, a.language
      FROM master..sysxlogins a, master..sysdatabases b
      WHERE srvid IS NULL
      AND a.name <> 'sa'
      AND a.dbid = b.dbid
    
    ELSE
    
      DECLARE c_login_curs CURSOR FOR
    
      SELECT a.sid, a.name, a.xstatus, a.password, b.name, a.language
      FROM master..sysxlogins a, master..sysdatabases b
      WHERE srvid IS NULL
      AND a.name = @login_name
      AND a.dbid = b.dbid
      
    OPEN c_login_curs
    FETCH NEXT FROM c_login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd, @defdbname, @deflanguage
    
    IF (@@fetch_status = -1)
    BEGIN
      PRINT 'No login(s) found.'
      CLOSE c_login_curs
      DEALLOCATE c_login_curs
      RETURN -1
    END
    
    PRINT 'DECLARE @pwd sysname'
    
    WHILE (@@fetch_status <> -1)
      BEGIN
        IF (@@fetch_status <> -2)
        BEGIN
          PRINT ''
          SET @tmpstr = '-- Generating creation for login: ' + @name
          PRINT @tmpstr
          SET @tmpstr = 'PRINT ''Creating login [' + @name + ']'
          PRINT @tmpstr
   
          /** NT auth. */
          IF (@xstatus & 4) = 4
          BEGIN
            IF (@xstatus & 1) = 1
              BEGIN -- NT login is denied access
                SET @tmpstr = 'EXEC master..sp_denylogin ''' + @name + ''''
                PRINT @tmpstr
              END
          ELSE
              BEGIN -- NT login has access
                SET @tmpstr = 'EXEC master..sp_grantlogin ''' + @name + ''''
                PRINT @tmpstr
              END
    
          SET @tmpstr = 'EXEC master..sp_defaultdb @loginame=''' + @name + ''', @defdb=''' + @defdbname + ''''
          PRINT @tmpstr
          SET @tmpstr = 'EXEC master..sp_defaultlanguage @loginame=''' + @name + ''', @language='''
              + @deflanguage + ''''
          PRINT @tmpstr
        END
         
        /** SQL Server auth. */
        ELSE BEGIN
            IF (@binpwd IS NOT NULL)
              BEGIN -- Non-null password
                EXEC sp__dba_hexadecimal @binpwd, @txtpwd OUT
                IF (@xstatus & 2048) = 2048
                  SET @tmpstr = 'SET @pwd = CONVERT (varchar(256), ' + @txtpwd + ')'
                ELSE
                  SET @tmpstr = 'SET @pwd = CONVERT (varbinary(256), ' + @txtpwd + ')'
                  PRINT @tmpstr
                  EXEC sp__dba_hexadecimal @SID_varbinary,@SID_string OUT
                  SET @tmpstr = 'EXEC master..sp_addlogin ''' + @name
                      + ''', @pwd, @defdb =''' + @defdbname + ''' , @deflanguage = '''
                      + @deflanguage + ''' , @sid = ' + @SID_string + ', @encryptopt = '
              END
            ELSE BEGIN
            -- Null password
              EXEC sp__dba_hexadecimal @SID_varbinary,@SID_string OUT
              SET @tmpstr = 'EXEC master..sp_addlogin ''' + @name
                  + ''', NULL, @defdb =''' + @defdbname + ''' , @deflanguage = '''
                  + @deflanguage + ''' , @sid = ' + @SID_string + ', @encryptopt = '
            END
            IF (@xstatus & 2048) = 2048
            -- login upgraded from 6.5
              SET @tmpstr = @tmpstr + '''skip_encryption_old'''
            ELSE
              SET @tmpstr = @tmpstr + '''skip_encryption'''
              PRINT @tmpstr
            END
        END
        FETCH NEXT FROM c_login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd, @defdbname, @deflanguage
      END
   
    CLOSE c_login_curs
    DEALLOCATE c_login_curs
     
    RETURN 0
   GO