DECLARE @DBName NVARCHAR(255)
DECLARE @SQL NVARCHAR(MAX)
DECLARE @DBUSER NVARCHAR(20) = 'AeriesNetUser'
DECLARE db_cursor CURSOR FOR
SELECT name
FROM sys.databases
WHERE name LIKE 'DST%'
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @DBName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQL = '
USE [' + @DBName + '];
IF EXISTS (
SELECT dp.name
FROM sys.database_principals dp
LEFT JOIN sys.server_principals sp ON dp.sid = sp.sid
WHERE dp.type IN (''S'', ''U'', ''G'')
AND dp.name = ''' + @DBUSER + '''
AND sp.sid IS NULL
)
BEGIN
PRINT ''!!!' + @DBUSER + ' is orphaned in ' + @DBName + ''';
Print '' Fixing...''
ALTER USER [' + @DBUSER + '] WITH LOGIN = [' + @DBUSER + '];
END
ELSE
BEGIN
PRINT '' Ok'';
END'
--print @sql
EXEC sp_executesql @SQL
FETCH NEXT FROM db_cursor INTO @DBName
END
CLOSE db_cursor
DEALLOCATE db_cursor
Fix orphaned AeriesNetUser Print
Modified on: Wed, Jul 24, 2024 at 6:34 PM
Did you find it helpful? Yes No
Send feedbackSorry we couldn't be helpful. Help us improve this article with your feedback.