Solved

SQLCMD fails to execute

Posted on 2014-01-02
8
844 Views
Last Modified: 2014-01-21
  
 USE [master]
GO
:SETVAR DatabaseName "TEST"
:SETVAR LoginName [domain\VIVEKANANDHAN.PERIASAMY]
:SETVAR Access "READ"
/****** Object:  Login @LoginName    Script Date: 12/27/2013 09:46:10 ******/
IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = $(LoginName))
CREATE LOGIN $(LoginName) FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]
GO
USE $(DatabaseName)
GO
/****** Object:  User @LoginName    Script Date: 12/27/2013 09:46:09 ******/
IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = $(LoginName))
CREATE USER $(LoginName) FOR LOGIN $(LoginName) WITH DEFAULT_SCHEMA=$(LoginName)
GO

IF UPPER $(Access) LIKE '%READ%' 
      BEGIN
            USE $(DatabaseName)
            GO
            EXEC sp_addrolemember N'db_datareader', $(LoginName)
            GO
      END
ELSE IF UPPER $(Access)LIKE '%WRITE%'
      BEGIN
            USE $(DatabaseName)
            GO
            EXEC sp_addrolemember N'db_datawriter', $(LoginName)
            GO
      END
ELSE IF UPPER $(Access) LIKE '%DBO%'
      BEGIN
            USE $(DatabaseName)
            GO
            EXEC sp_addrolemember N'db_owner',  $(LoginName)
            GO
      END
ELSE IF UPPER $(Access) LIKE '%ADMIN%'
      BEGIN
            EXEC master..sp_addsrvrolemember @loginame = $(LoginName), @rolename = N'sysadmin'
      END
END

/******  CREATE LOGIN AND USER WITH PERMISSIONS SCRIPT END  ******/

Open in new window


i'm trying to assign the SQL login using SQLCMD mode. I know we can use another method to achieve it.But i want to do with SQLCMD.But scripts fails. I have tried googling lots but i didn't find any syntax error. But when i execute, it fails with syntax error.
Can any experts tell me ,what i did wrong?
0
Comment
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 25

Expert Comment

by:Mohammed Khawaja
ID: 39752763
Could you please provide more details such as what is the exact script you are trying to run.
0
 
LVL 5

Author Comment

by:VIVEKANANDHAN_PERIASAMY
ID: 39752780
Above is the exact script i;m using. Only i have masked the domain name.
:SETVAR LoginName [domain\VIVEKANANDHAN.PERIASAMY]
except this line , all other lines are the exact line i'm using it
0
 
LVL 24

Expert Comment

by:DBAduck - Ben Miller
ID: 39758445
What is the message you get back? I don't believe you should have the GO words in there unless you are going to be using Management Studio. Take out the GOs and try it again. If it fails, please post the message you get.
0
How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

 
LVL 38

Expert Comment

by:Jim P.
ID: 39758495
What user ID are you running it under?
0
 
LVL 43

Accepted Solution

by:
Eugene Z earned 500 total points
ID: 39758844
put values in single quotes

e.g
instead of

IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = $(LoginName))

--use

IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = '$(LoginName)')
0
 
LVL 43

Expert Comment

by:Eugene Z
ID: 39758877
try:

 USE [master]
GO
:SETVAR DatabaseName "TEST"
:SETVAR LoginName "[domain\VIVEKANANDHAN.PERIASAMY]"
:SETVAR Access "READ"



IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = '$(LoginName)')
CREATE LOGIN $(LoginName) FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]
GO
USE $(DatabaseName)
GO

IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = '$(LoginName)')
CREATE USER $(LoginName) FOR LOGIN $(LoginName) WITH DEFAULT_SCHEMA=$(LoginName)
GO

IF UPPER ('$(Access)') LIKE '%READ%' 
      BEGIN
            
            EXEC sp_addrolemember N'db_datareader', $(LoginName)
            
      END
IF UPPER ('$(Access)') LIKE '%WRITE%'
      BEGIN
           
            EXEC sp_addrolemember N'db_datawriter', $(LoginName)
            
      END
 IF UPPER ('$(Access)') LIKE '%DBO%'
      BEGIN
          
            EXEC sp_addrolemember N'db_owner',  $(LoginName)
           
      END
 IF UPPER ('$(Access)') LIKE '%ADMIN%'
      BEGIN
            EXEC master..sp_addsrvrolemember @loginame = $(LoginName), @rolename = N'sysadmin'
      END

Open in new window

0
 
LVL 27

Expert Comment

by:Zberteoc
ID: 39759354
If your syntax error is in regards to GO word then the problem is that when you execute batches outside the Management Studio the SQL server doesn't recognize GO because it is not an SQL command. You will have to get rid of your GOs and find another way to write that script.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question