J’ai créer une base de donnée manuellement, mais lors de la création j’ai fait une erreur j’ai créer un user a la db sans le retaché dans dans le moteur.
CREATE USER [WdsUSer] WITHOUT LOGIN WITH DEFAULT_SCHEMA=[WdsUSer]
Puis j’ai remarqué que j’avais oublié de le rajouté dans le moteur
CREATE LOGIN [WdsUser] WITH PASSWORD=N'XXXXXXX', DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
J’ai constater une erreur classique d’un user orphelin.
J’ai essayé d’appliqué
sp_change_users_login 'Auto_Fix', WdsUSer
The number of orphaned users fixed by updating users was 0.
The number of orphaned users fixed by adding new logins and then updating users was 0.
Ca ne marche pas
Alors
sp_change_users_login 'Update_One', WdsUser, WdsUSer
Msg 15291, Level 16, State 1, Procedure sp_change_users_login, Line 114
Terminating this procedure. The User name 'WdsUser' is absent or invalid.
Alors je vais voir dans les DMV
1 2 3 4 5
| select u.name from master..syslogins l right join
sysusers u on l.sid = u.sid
where l.sid is null and issqlrole <> 1 and isapprole <> 1
and (u.name <> 'INFORMATION_SCHEMA' and u.name <> 'guest'
and u.name <> 'system_function_schema') |
Je trouve bien mon user
Et j’ai essayé d’exécuter les scripts
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
| --Run this script under your Database context
--by using the following statements
USE cdbTopExEmea
Go
--Variable to hold current UserName to process
DECLARE @UserName nvarchar(255)
--Create Cursor for the orphan Users
DECLARE OrphanUser_Cursor cursor for
SELECT UserName = name
FROM sysusers
WHERE
(issqluser = 1)
AND (sid is not null and sid <> 0x0)
AND (suser_sname(sid) is null)
ORDER BY name
OPEN OrphanUser_Cursor
FETCH NEXT FROM OrphanUser_Cursor INTO @UserName
WHILE (@@fetch_status = 0)
BEGIN
PRINT @UserName + ' user name being resynced'
--Re-sync the currently selected orphan user
EXEC sp_change_users_login 'Update_one', @UserName, @UserName
--Get Next Orphan User
FETCH NEXT FROM OrphanUser_Cursor INTO @UserName
END
--Clean up
CLOSE OrphanUser_Cursor
DEALLOCATE OrphanUser_Cursor |
Toujours le même message .
1 2
| Msg 15291, Level 16, State 1, Procedure sp_change_users_login, Line 114
Terminating this procedure. The User name 'WdsUser' is absent or invalid. |
Merci de votre et de vos idées.
Partager