I was hoping to grant data reader access in several databases at once for a report login. Unfortunately, when I try the following, I get an "Incorrect syntax near @name" error.Is there a query that can do this, or do you have to go through each database one at a time?DECLARE @name VARCHAR(50) -- database name DECLARE db_cursor CURSOR FOR SELECT name FROM master.dbo.sysdatabases WHERE name LIKE '2009%'OPEN db_cursor FETCH NEXT FROM db_cursor INTO @name WHILE @@FETCH_STATUS = 0 BEGIN use @name --Error points here. go exec sp_addrolemember db_datareader, reportuser go FETCH NEXT FROM db_cursor INTO @name END CLOSE db_cursor DEALLOCATE db_cursor
Granting access on multiple databases
Posted - 2009-11-11 : 13:21:12
