Avatar of Zack
ZackFlag for Australia

asked on 

SQL Incorrect syntax near

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;

Open in new window


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

What is going here any assistance is welcome.
Microsoft SQL ServerSQL

Avatar of undefined
Last Comment
Mark Wills
Avatar of Ryan Chong
Ryan Chong
Flag of Singapore image

saw that error?

SET @usr = "tempdwlogin';

try:
SET @usr = 'tempdwlogin';

Open in new window

Avatar of Mark Wills
Mark Wills
Flag of Australia image

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;
Avatar of Zack
Zack
Flag of Australia image

ASKER

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;

Open in new window


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

Thank you.
Avatar of Ryan Chong
Ryan Chong
Flag of Singapore image

it seems no syntax error for your latest scripts posted above.
Avatar of Mark Wills
Mark Wills
Flag of Australia image

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....
Avatar of Zack
Zack
Flag of Australia image

ASKER

Hi guys could this be permissions related, I'm currently logined in as a sysadmin.
Avatar of Zack
Zack
Flag of Australia image

ASKER

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;

Open in new window


Still same error
Pic.PNG
ASKER CERTIFIED SOLUTION
Avatar of Mark Wills
Mark Wills
Flag of Australia image

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Avatar of Zack
Zack
Flag of Australia image

ASKER

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.
Avatar of Mark Wills
Mark Wills
Flag of Australia image

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

Open in new window

Avatar of Zack
Zack
Flag of Australia image

ASKER

Hi Mark,

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

Thank you for your help.
Avatar of Mark Wills
Mark Wills
Flag of Australia image

My pleasure, happy to help :)
Microsoft SQL Server
Microsoft SQL Server

Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.

171K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews

TRUSTED BY

IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo