My user name has changed from jdoe
to john.doe
(for example). The reference in the [dbo].[Users]
table shows my old user name, jdoe
.
However, with my new user name, john.doe
, the subscriptions fail to run and throw the error:
Failure sending mail: The user or group name 'ABCDE\jdoe' is not recognized.Mail will not be resent.
The [dbo].[Subscriptions].[OwnerID]
value references the [dbo].[Users].[UserID]
for jdoe.
Can I simply change the [dbo].[Users].[UserName]
value to my new username, leaving the [dbo].[Users].[UserID]
and [dbo].[Users].[Sid]
columns as is?
I would use a MERGE
statement for this. Check the transaction on a rollback to test with first. You'll also need UPDATE
permission on the dbo.Users
table.
SET XACT_ABORT ON
BEGIN TRANSACTION;
;WITH
users_list
AS
(
SELECT users_list.* FROM (VALUES
('DOMAIN\OldUser1', 'DOMAIN\New.User1')
, ('DOMAIN\OldUser2', 'DOMAIN\New.User2')
) users_list ([UserName], [NewUserName])
)
,
users_source -- don't add users that already exist to prevent duplicates
AS
(
SELECT
[UserName]
, [NewUserName]
FROM
users_list
WHERE
1=1
AND [NewUserName] NOT IN(SELECT [UserName] FROM [ReportServer].[dbo].[Users])
)
MERGE [ReportServer].[dbo].[Users] AS T
USING users_source AS S
ON T.[UserName] = S.[UserName]
WHEN MATCHED
THEN UPDATE SET
T.[UserName] = S.[NewUserName]
OUTPUT @@SERVERNAME AS [Server Name], DB_NAME() AS [Database Name], $action, inserted.*, deleted.*;
ROLLBACK TRANSACTION;
--COMMIT TRANSACTION;
GO