可以在SQL游标中更改数据库吗？

Question

我有一个游标，在一个proc中，它从我们用来存储实用程序的db中运行。我需要游标在执行从/INTO @；部件获取NEXT之前动态地更改数据库，以便循环遍历正确数据库中的正确行。

CREATE OR ALTER PROCEDURE utils.post_cohesity_restore

AS
Begin
    /* PURPOSE: re-attaches orphaned users and grants permissions after a Cohesity restore */

    SET NOCOUNT ON;

    DECLARE
        @juris char(20),
        @sql char(400),
        @sql_A char(400),
        @database sysname,
        @users char(20);
    
    SELECT 
        @database = destination_database_name   
    FROM 
        [msdb].[dbo].[restorehistory]   
    WHERE 
        restore_date >= dateadd(DAY,-3,getDate()) 
        AND (LOWER(destination_database_name) LIKE '%clientdata%' 
        OR LOWER(destination_database_name) LIKE '%analysis%' 
        OR LOWER(destination_database_name) LIKE '%migration%')

    SELECT 
        @juris = LEFT(@database, 
        CASE 
            WHEN CHARINDEX('ClientData', @database) <> 0 
            THEN (CHARINDEX('ClientData', @database)-1)
            WHEN CHARINDEX('Migration', @database) <> 0 
            THEN (CHARINDEX('Migration', @database)-1)
            WHEN CHARINDEX('Analysis', @database) <> 0 
            THEN (CHARINDEX('Analysis', @database)-1) 
        END)

    DECLARE users_cursor CURSOR
    FOR SELECT 
            dp.[name] as username
        FROM sys.database_principals AS dp  
        LEFT JOIN sys.server_principals AS sp  
            ON dp.[name] = sp.[name]  
        WHERE 
            sp.sid <> dp.sid;

    OPEN users_cursor;

    FETCH NEXT FROM users_cursor INTO 
        @users;

    WHILE @@FETCH_STATUS = 0
        BEGIN
            SET @sql = ('USE ' + @database 
            + '; ALTER USER ' + rtrim(@users) + ' WITH LOGIN = ' + rtrim(@users))
            PRINT @sql; -- for testing/seeing the EXEC below
            EXEC (@sql);
            FETCH NEXT FROM users_cursor INTO 
                    @users;
        END;

    CLOSE users_cursor;

    DEALLOCATE users_cursor;
    
    SET @sql_A  = ('USE ' + @database 
            +  '; GRANT EXECUTE TO [analyst]; GRANT VIEW DEFINITION TO [analyst];' 
            + ' ALTER ROLE [ddladmin] ADD MEMBER [analyst];  ALTER ROLE [datawriter] ADD MEMBER [analyst];')
    
    PRINT @sql_A; -- for testing/seeing the EXEC below
    
    EXEC (@sql_A );

END

我需要切换到这个点附近填充@database的db，这样用户就可以最终形成该数据库。

DECLARE users_cursor CURSOR
    FOR SELECT 
            dp.[name] as username
        FROM sys.database_principals AS dp  
        LEFT JOIN sys.server_principals AS sp  
            ON dp.[name] = sp.[name]  
        WHERE 
            sp.sid <> dp.sid;

    OPEN users_cursor;

    FETCH NEXT FROM users_cursor INTO 
        @users;

有什么想法吗？甚至有可能吗？

Answer 1

最后，我在每个数据库中保存了上面已编辑的proc版本，然后创建了一个SSA作业，该作业(A)标识数据库，b)调用该数据库的proc版本。