How to use cursor value in Select Statement within Dynamic SQL (SQL Server)

Question

I am trying to aggregate SQL Server Users through LinkdedServers, but am unable to select the database name in my query. I tried using db_name() as ''Database'' In my select statement, but it uses the current database context, and not the database that I am selecting from. I realize that this happens because I am using the "fully qualified" database name, so the database context never actually changes. I am also unable to pull in the cursor value as part of my select statement.

Does anyone have any ideas as to how I can get the database name of the database I am selecting from?

Here is my code:

DECLARE @DatabaseName VARCHAR(30)

DECLARE c1 CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY FOR
SELECT 
    Name 
FROM 
    [LinkedServer].master.sys.databases
WHERE 
    Name NOT IN     (
                    'master',
                    'model',
                    'tempdb',
                    'msdb',
                    'distribution',
                    )


OPEN c1

FETCH NEXT FROM c1 
INTO @DatabaseName

WHILE @@FETCH_STATUS = 0
BEGIN

EXEC (
'
--INSERT INTO [Gather_Privileged_DBUsers_Sox]
SELECT  distinct (dp.name) as ''DatabaseUser'', 
        s.name as ''ServerName'',
        db_name() as ''Database'', 
        getdate() as ''date''
FROM    [LinkedServer].[' + @DatabaseName + '].sys.database_role_members drm 
        JOIN [LinkedServer].[' + @DatabaseName + '].sys.database_principals dp
            ON drm.member_principal_id = dp.principal_id
        JOIN [LinkedServer].[' + @DatabaseName + '].sys.database_principals dp2
            ON  drm.role_principal_id = dp2.principal_id
        JOIN [LinkedServer].[master].[sys].[servers] s on 1=1
WHERE 
        dp2.name in 
(''db_owner'',''db_accessadmin'',''db_securityadmin'',''db_ddladmin'')
        AND s.server_id = 0
        AND dp.name not in (''dbo'')
        AND dp.type != ''R''
')

FETCH NEXT FROM c1 
INTO @DatabaseName
END 
CLOSE c1;
DEALLOCATE c1;
GO    

Attempt to use the variable value:

SELECT  distinct (dp.name) as ''DatabaseUser'', 
        s.name as ''ServerName'', 
        ' + @DatabaseName + ' as ''Database'', 
        getdate() as ''date''

When I do this, I get the following error: Msg 207, Level 16, State 1, Line 5 Invalid column name 'myTestDatabase'.

How can I turn the variable into a string in this situation?

Answer 1

You should get out of the habit of wrapping column aliases in single quotes. They are NOT string literals and it makes your code much harder to read. It also causes lots of anguish with dynamic sql.

Here is an example of how you would capture the value in your varaible and build this string without those extra single quotes.

declare @SQL nvarchar(max) = 
'SELECT  distinct (dp.name) as DatabaseUser, 
        s.name as ServerName, 
        ''' + @DatabaseName + ''' as Database, 
        getdate() as [date]'