Solved

SQLCMD fails to execute

Posted on 2014-01-02
8
840 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
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
Independent Software Vendors: 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!

 
LVL 38

Expert Comment

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

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 42

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 26

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

Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

Question has a verified solution.

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

Suggested Solutions

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

685 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