December 23, 2015 at 7:27 am
Hello,
I'm having difficulty in getting the following piece of dynamic sql to execute in the correct database context for the DENY statement. It wants to execute in the current database instead of the database in the loop. Any help would be appreciated. BTW - I've gotten this to work by using sp_MSforeachdb, I just don't like the solution. Thanks. I have 20 or so databases (edited for brevity) that I need to deny access to certain columns.
DECLARE @SQL VARCHAR(MAX)
DECLARE @DB sysname
DECLARE @ROLE sysname
SELECT @ROLE = 'DenyData'
DECLARE curDB CURSOR FORWARD_ONLY STATIC FOR
SELECT [name]
FROM master..sysdatabases
WHERE [name] IN ('DB1','DB2','DB3','DB4')
ORDER BY [name]
OPEN curDB
FETCH NEXT FROM curDB INTO @DB
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQL = 'if not exists (select 1 from ' + quotename(@DB) + '.sys.database_principals where name = N' + char(39) + @ROLE + char(39) + ')' + char(10) + char(13)
SET @SQL = @SQL + 'BEGIN ' + char(10) + char(13)
SET @SQL = @SQL + 'USE ' + quotename(@DB) +';' + CHAR(13) + 'CREATE ROLE ' + @ROLE + ' AUTHORIZATION dbo;' + CHAR(13)
SELECT @SQL = @SQL + 'DENY SELECT ON dbo.' + object_name(object_id) + '(' + [name] + ') TO DenyData' + CHAR(13)
from sys.columns where [name] in ('CMNYTXID','PHNUMBR1','PHNUMBR2','PHONE3')
SET @SQL = @SQL + 'END ' + char(10) + char(13)
PRINT @SQL
--EXEC (@SQL)
FETCH NEXT FROM curDB INTO @DB
END
CLOSE curDB
DEALLOCATE curDB
December 23, 2015 at 7:45 am
Why not just your code to spit out the sql then you can copy it to a new window to run it? You can't use dynamic sql like you are trying to.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply