TSQL Code syntax problem

Question

Post reply

TSQL Code syntax problem

Chandu-212374

SSCrazy

Points: 2247
More actions
August 26, 2010 at 7:53 am

#237239

Can someone please rectify the syntax error in below code in second line:
SELECT @groupname = 'domname\userid'
CREATE USER @groupname FOR LOGIN @groupname
When i hover the mouse over @groupname in second line it says : Incorrect syntax, expecting ID or QUOTED_ID

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

i don't think the CREATE USER command allows variables; you'd have to switch to dynamic SQL instead:

declare @groupname varchar(50),

@sql varchar(1000)

SELECT @groupname = 'domname\userid'

SET @sql ='CREATE USER ' + @groupname + ' FOR LOGIN ' + @groupname

EXEC(@sql)

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Cadavre SSC-Forever Points: 41690 More actions · Answer 2

Would need to be dynamic SQL, e.g.

DECLARE @sql AS VARCHAR(MAX)

SELECT @groupname = 'domname\userid'

SET @sql = 'CREATE USER ' + @groupname + ' FOR LOGIN ' + @groupname

EXEC (@sql)

Damnit Lowell! 😛

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Lowell SSC Guru Points: 323518 More actions · Answer 3

skcadavre (8/26/2010)
Damnit Lowell! 😛

we cross post each others same answers just a bit too often 🙂

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Cadavre SSC-Forever Points: 41690 More actions · Answer 4

Lowell (8/26/2010)
we cross post each others same answers just a bit too often 🙂

I'm beginning to think I should reload the page before posting 😛

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/