Migration SQL Server 2000 à 2005

Bonjour
Dans le cadre d'une migration de base de données SQL server 2000 vers SQL Server 2005, j'ai trouvé un script pour exporter les logins existants contenant la ligne suivante :

Code:

SET @PWD_varbinary = CAST( LOGINPROPERTY( @name, 'PasswordHash' ) AS varbinary (256) )

. Malheureusement, la vérification du script avant exécution me donne un message d'erreur

Citation:

'LOGINPROPERTY' is not a recognized function name.

. Est-ce que quelqu'un peut me dire comment corriger celà ?
Merci

C'est une nouvelle fonction de SQL Server 2008.

A +

Merci pour la réponse.
Tout en remerciant également ceux qui ont travaillé à la réalisation de ce tutorial, je trouve qd même d'utiliser une fonctionnalité de SQL server 2008 car le titre de l'article parle bien de SQL Server 2005.
Est-ce que cette fonction a un équivalent en SQL Server 2005 ?

Pas que je sache.

A +

Vous pouvez tenter avec ceci :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
 
-------------------------------------------------------------------------------------------------------------------------------
-- SP_Rev_Login script
--
-- Author :
--
-- Description : This script create the sp_help_revlogin procedure in the master database.
--               The procedure return result is the list of the users account of the database where the procedure is run from.
--
-- Input : none
-- Output : Create the store procedure sp_help_revlogin                                                                        
-------------------------------------------------------------------------------------------------------------------------------
 
 
USE master
GO
 
-- Drop sp_hexadecimal if exist
 
IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL  
  DROP PROCEDURE sp_hexadecimal
GO
 
------------------------------------------------------------------
-- Create sp_hexadecimal procedure
-- Description : Convert a varbinary to hexadecimal in a varchar
-- Input : @binvalue 
-- Output : The convertion of the input parameter into hexadecimal
------------------------------------------------------------------
 
CREATE PROCEDURE sp_hexadecimal
    @binvalue varbinary(256),
    @hexvalue varchar(256) OUTPUT
AS
 
-- Variables declaration
DECLARE @charvalue varchar(256)
DECLARE @i int
DECLARE @length int
DECLARE @hexstring char(16)
 
-- Variables initialisation
SELECT @charvalue = '0x'
SELECT @i = 1
SELECT @length = DATALENGTH (@binvalue)
SELECT @hexstring = '0123456789ABCDEF' 
 
-- Parse the input parameter from 1 by 1 to the end...
WHILE (@i <= @length) 
BEGIN
  -- Variables of the while loop
  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
----------------
-- End procedure
----------------
GO
 
 
-- Drop sp_help_revlogin if exist
IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL
  DROP PROCEDURE sp_help_revlogin 
GO
 
-------------------------------------------------------------------------------------------------------------
-- sp_help_revlogin 
-- Description : This procedure generates a list user of the database, with their login, password and status, 
--               from which the procedure is run from.
--               This is use to transfer login after a migration for example.
-- Input : The login name of a specific user, or nothing to have a list af all the users.
-- Output : A script with the list of the user's and their rights
------------------------------------------------------------------------------------------------------------- 
CREATE PROCEDURE sp_help_revlogin @login_name sysname = NULL AS
DECLARE @name    sysname
DECLARE @xstatus int
DECLARE @binpwd  varbinary (256)
DECLARE @txtpwd  sysname
DECLARE @tmpstr  varchar (256)
DECLARE @SID_varbinary varbinary(85)
DECLARE @SID_string varchar(256)
 
-- Search for the information about a specific user
IF (@login_name IS NULL)
  DECLARE login_curs CURSOR FOR 
    SELECT sid, name, xstatus, password FROM master..sysxlogins 
    WHERE srvid IS NULL AND name <> 'sa'
ELSE
-- Search for information about all the users
  DECLARE login_curs CURSOR FOR 
    SELECT sid, name, xstatus, password FROM master..sysxlogins 
    WHERE srvid IS NULL AND name = @login_name
OPEN login_curs 
FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd
 
-- If there is no user or the specific user isn't found
IF (@@fetch_status = -1)
BEGIN
  PRINT 'No login(s) found.'
  CLOSE login_curs 
  DEALLOCATE login_curs 
  RETURN -1
END
SET @tmpstr = '/* sp_help_revlogin script ' 
PRINT @tmpstr
SET @tmpstr = '** Generated ' 
  + CONVERT (varchar, GETDATE()) + ' on ' + @@SERVERNAME + ' */'
PRINT @tmpstr
PRINT ''
PRINT 'DECLARE @pwd sysname'
-- Else while there is users' informations to list
WHILE (@@fetch_status <> -1)
BEGIN
  IF (@@fetch_status <> -2)
  BEGIN
    PRINT ''
    SET @tmpstr = '-- Login: ' + @name
    PRINT @tmpstr 
    IF (@xstatus & 4) = 4
    BEGIN -- NT authenticated account/group
      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
    END
    ELSE BEGIN -- SQL Server authentication
      IF (@binpwd IS NOT NULL)
      BEGIN -- Non-null password
        EXEC sp_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_hexadecimal @SID_varbinary,@SID_string OUT
        SET @tmpstr = 'EXEC master..sp_addlogin ''' + @name 
          + ''', @pwd, @sid = ' + @SID_string + ', @encryptopt = '
      END
      ELSE BEGIN 
        -- Null password
	EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT
        SET @tmpstr = 'EXEC master..sp_addlogin ''' + @name 
          + ''', NULL, @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 login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd
  END
CLOSE login_curs 
DEALLOCATE login_curs 
RETURN 0
GO

Executez la procedure sp_help_revlogin sur votre server 2000.
La sp vous générera un script en output.
Tournez ce script sur votre server 2005.

Infiniment merci à tous et particulièrement à toi Ptit_Dje. Ca a marché 5/5 :king: