Solved

SQLCMD fails to execute

Posted on 2014-01-02
8
836 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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
LVL 38

Expert Comment

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

Accepted Solution

by:
EugeneZ 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:EugeneZ
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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

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…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

808 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