Solved

SQLCMD fails to execute

Posted on 2014-01-02
8
851 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
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 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

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!

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…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

696 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