SQL Incorrect syntax near

saw that error?

SET @usr = "tempdwlogin';

try:

SET @usr = 'tempdwlogin';

Select allOpen in new window

could be the double quoote ?

Instead of        :  SET @usr = "tempdwlogin';

try   :   SET @usr = 'tempdwlogin';


Also, your second fetch next isnt populating your variables.... Change line 25 to :

      FETCH NEXT FROM read_only_cur INTO @objname, @type;

HI Mark,

Code now:

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 ' + 'tempdwlogin';
   	  	EXEC sp_executesql @sql;
   	  END
   	  ELSE
   	  	BEGIN
   	  	   SET @sql = N'GRANT EXECUTE ON ' + @objname + N'TO ' + 'tempdwlogin';
   	  	   EXEC sp_executesql @sql;
   	  	END
       FETCH NEXT FROM read_only_cur INTO @objname, @type
   END;
CLOSE read_only_cur;
DEALLOCATE read_only_cur;

Select allOpen in new window

Error Msg 102, Level 15, State 1, Line 1 Incorrect syntax near 'tempdwlogin'

Thank you.

it seems no syntax error for your latest scripts posted above.

why change the variable @usr to literal  'tempdwlogin'

e.g. you have changed :

SET @sql = N'GRANT SELECT ON ' + @objname + N'TO ' + 'tempdwlogin';

and should be :

SET @sql = N'GRANT SELECT ON ' + @objname + N' TO ' + @usr;

Also, make surre there is a space in the ' TO '

@ryan, just noticed and endorsed your eARLIER POST....

Hi guys could this be permissions related, I'm currently logined in as a sysadmin.

Hi Mark,

Okay corrected it to this:

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 INTO @objname, @type
   END;
CLOSE read_only_cur;
DEALLOCATE read_only_cur;

Select allOpen in new window

Still same error
Pic.PNG

Hi Guys,

Okay, that got it now REALLY weird error:

Cannot find the user 'tempdwlogin', because it does not exist or you do not have permission.
Msg 15151, Level 16, State 1, Line 1

I am logged in as tempdwlogin.

Any ideas?

Thank you.

Could be your USER.... Have you copied DB's or something ? Might have to check your user credentials. If so, you might need to drop the user and recreate the user. Those userid's dont transfer well in a copied / restored (from elsewhere) DB. The users ultimately live in master / sql server instance, not to a specific DB as such - even though that is where you see them...

Here is a quick test / sample of what you might need to look at. In the test below, I create an SQL login 'MyTestLogin' and a user for that login 'MyTestUser'

Me thinks it is a slightly different question and could go off on a tangent (of significance), and might be well worth asking a specific question about permissions....

use master; 

SELECT *
FROM sys.sysusers
WHERE name like 'MyTest%' ;

go

create login MyTestLogin with password = '123MyTestLogin123';
go

create user MyTestUser for login MyTestLogin;
go

use EE;
go

GRANT SELECT ON dbo.tmpempid TO MyTestLogin;
go

-- the above fails with the same error as you get. 

--Msg 15151, Level 16, State 1, Line 58
--Cannot find the user 'MyTestLogin', because it does not exist or you do not have permission.

-- Need to assign permissions to USER

GRANT SELECT ON dbo.tmpempid TO MyTestUser;
go


-- now clean up the testing

use master;
go
drop user MyTestUser;
go
drop login MyTestLogin;
go

Select allOpen in new window

Hi Mark,

Good point I will investigate and likely post again about this weird issue.

Thank you for your help.

My pleasure, happy to help :)