Hello I am tring to add a user to all of my DB's named HTML_ howerver I am getting a syntax error Msg 102, Level 15, State 1, Line 30
Incorrect syntax near ''-
Here is the Query
Declare @dbCount int
Declare @NumberOfDatabases int
Declare @dbName varchar(20)
Declare @cmd varchar(8000)
CREATE TABLE #temp_dbList (dbCount int identity(1,1), dbName varchar(20))
--Insert into temp table all valid logging databases
INSERT INTO #temp_dbList (dbName)
SELECT name FROM master..sysdatabases WHERE name LIKE 'HTML_' ORDER BY name
--Set count to 1, and number to # of databases in temp table
SET @dbCount=1
SELECT @NumberOfDatabases = COUNT(dbCount) FROM #temp_dbList
--Loop through databases and run
WHILE @dbCount <= @NumberOfDatabases
BEGIN
SELECT @dbName=dbName FROM #temp_dbList WHERE dbCount=@dbCount
SET @cmd = 'IF (SELECT COUNT(name) FROM ' + @dbName + '..sysobjects WHERE xtype = ''U' + char(10)
SET @cmd = @cmd + 'USE ' + @dbName + '' + char(10)
SET @cmd = @cmd + 'Drop User [web_user] '+ char(10)
SET @cmd = @cmd + 'Create Role db_executor ' + char(10)
SET @cmd = @cmd + 'GRANT EXECUTE TO db_executor ' + char(10)
/*SET @cmd = @cmd + 'GO ' + char(10)*/
SET @cmd = @cmd + 'EXEC sp_grantdbaccess 'web_user' ' + char(10)
/*SET @cmd = @cmd + 'GO ' + char(10)*/
SET @cmd = @cmd + 'EXEC sp_addrolemember 'db_datareader', 'web_user' ' + char(10)
/*SET @cmd = @cmd + 'GO ' + char(10)*/
SET @cmd = @cmd + 'EXEC sp_addrolemember 'db_executor', 'web_user' ' + char(10)
/*SET @cmd = @cmd + 'GO ' + char(10)*/
SET @cmd = @cmd + 'ALTER USER web_user WITH DEFAULT_SCHEMA = dbo ' + char(10)
EXEC(@cmd)
SET @dbCount=@dbCount+1
END
DROP Table #temp_dbList
SET NOCOUNT OFF
Start Free Trial
