troubleshooting Question

SQL Incorrect syntax near

Avatar of Zack
ZackFlag for Australia asked on
Microsoft SQL ServerSQL
12 Comments1 Solution139 ViewsLast Modified:
Hi EE,

Am using the following script to assign permissions to views and stored procs etc:

DECLARE @objname varchar(150), @type varchar(10), @usr varchar(20), @sql nvarchar(100);
SET @usr = "tempdwlogin';
 
DECLARE read_only_cur CURSOR FOR 
SELECT sc.name + '.' + ob.name, ob.type
FROM sys.objects ob, sys.schemas sc
WHERE ob.schema_id = sc.schema_id
AND ob.type IN ('U', 'V', 'FN', 'IF', 'TF', 'FS', 'FT','P', 'PC');
 
OPEN read_only_cur;
FETCH NEXT FROM read_only_cur
INTO @objname, @type;
WHILE @@FETCH_STATUS = 0
   BEGIN
   	  IF @type IN ( 'U', 'V')
   	  BEGIN
   	  	SET @sql = N'GRANT SELECT ON ' + @objname + N'TO ' + @usr;
   	  	EXEC sp_executesql @sql;
   	  END
   	  ELSE
   	  	BEGIN
   	  	   SET @sql = N'GRANT EXECUTE ON ' + @objname + N'TO ' + @usr;
   	  	   EXEC sp_executesql @sql;
   	  	END
      FETCH NEXT FROM read_only_cur;
   END;
CLOSE read_only_cur;
DEALLOCATE read_only_cur;

I keep getting the error: Incorrect syntax near 'tempdwlogin'.

What is going here any assistance is welcome.
ASKER CERTIFIED SOLUTION
Mark Wills
Topic Advisor
Join our community to see this answer!
Unlock 1 Answer and 12 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 12 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros