Récuperer Les Utilisateurs Orphelins

 1
 2DECLARE @sql as nvarchar(max);
 3SET @sql = '
 4USE[?];PRINT''Database: ''+db_name();DECLARE @username VARCHAR(25);DECLARE
 5GetOrphanUsers CURSOR FOR SELECT UserName=NAME FROM sysusers WHERE issqluser=1AND(sid IS
 6NOT NULL AND sid<>0x0)AND SUSER_SNAME(sid)IS NULL ORDER BY NAME;OPEN GetOrphanUsers;FETCH
 7NEXT FROM GetOrphanUsers INTO @username;WHILE @@FETCH_STATUS=0BEGIN IF @username=''dbo''
 8EXEC sp_changedbowner''sa'';ELSE BEGIN BEGIN TRY EXEC sp_change_users_login''update_one''
 9,@username,@username;END TRY BEGIN CATCH PRINT''Fail to fix database user: ''+@username;END CATCH
10;END FETCH NEXT FROM GetOrphanUsers INTO @username;END;CLOSE GetOrphanUsers;DEALLOCATE
11GetOrphanUsers;';
12EXEC sp_MSforeachdb @sql

 1
 2CREATE PROCEDURE dbo.spDBA_FixOrphanUsers
 3AS
 4BEGIN
 5DECLARE @username VARCHAR(25)
 6
 7DECLARE GetOrphanUsers CURSOR
 8FOR
 9SELECT UserName = NAME
10FROM sysusers
11WHERE issqluser = 1 AND (sid IS NOT NULL AND sid <> 0x0) AND SUSER_SNAME(sid) IS NULL
12ORDER BY NAME
13
14OPEN GetOrphanUsers
15
16FETCH NEXT
17FROM GetOrphanUsers
18INTO @username
19
20WHILE @@FETCH_STATUS = 0
21BEGIN
22IF @username = 'dbo'
23EXEC sp_changedbowner 'sa'
24ELSE
25EXEC sp_change_users_login 'update_one', @username, @username
26
27FETCH NEXT
28FROM GetOrphanUsers
29INTO @username
30END
31
32CLOSE GetOrphanUsers
33
34DEALLOCATE GetOrphanUsers
35END
36GO

 1
 2CREATE PROCEDURE dbo.spDBA_FixOrphanUsersPassWord
 3AS
 4BEGIN
 5 DECLARE @username VARCHAR(25)
 6 DECLARE @password VARCHAR(25)
 7
 8 DECLARE GetOrphanUsers CURSOR
 9 FOR
10 SELECT UserName = NAME
11 FROM sysusers
12 WHERE issqluser = 1 AND (sid IS NOT NULL AND sid <> 0x0) AND SUSER_SNAME(sid) IS NULL
13 ORDER BY NAME
14
15 OPEN GetOrphanUsers
16
17 FETCH NEXT
18 FROM GetOrphanUsers
19 INTO @username
20
21 SET @password = @username
22
23 WHILE @@FETCH_STATUS = 0
24 BEGIN
25  IF @username = 'dbo'
26   EXEC sp_changedbowner 'sa'
27  ELSE
28   EXEC sp_change_users_login 'Auto_Fix', @username, NULL, @password
29
30  FETCH NEXT
31  FROM GetOrphanUsers
32  INTO @username
33 END
34
35 CLOSE GetOrphanUsers
36
37 DEALLOCATE GetOrphanUsers
38END
39GO

 1
 2CREATE PROCEDURE dbo.spDBA_DropOrphanUsers
 3AS
 4BEGIN
 5 DECLARE @username VARCHAR(25)
 6
 7 DECLARE GetOrphanUsers CURSOR
 8 FOR
 9 SELECT UserName = NAME
10 FROM sysusers
11 WHERE issqluser = 1 AND (sid IS NOT NULL AND sid <> 0x0) AND SUSER_SNAME(sid) IS NULL
12 ORDER BY NAME
13
14 OPEN GetOrphanUsers
15
16 FETCH NEXT
17 FROM GetOrphanUsers
18 INTO @username
19
20 WHILE @@FETCH_STATUS = 0
21 BEGIN
22  IF @username = 'dbo'
23   EXEC sp_changedbowner 'sa'
24  ELSE
25   EXEC sp_dropuser @username
26
27  FETCH NEXT
28  FROM GetOrphanUsers
29  INTO @username
30 END
31
32 CLOSE GetOrphanUsers
33
34 DEALLOCATE GetOrphanUsers
35END