[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 880
  • Last Modified:

SQLCMD fails to execute

  
 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
VIVEKANANDHAN_PERIASAMY
Asked:
VIVEKANANDHAN_PERIASAMY
1 Solution
 
Mohammed KhawajaCommented:
Could you please provide more details such as what is the exact script you are trying to run.
0
 
VIVEKANANDHAN_PERIASAMYAuthor Commented:
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
 
DBAduck - Ben MillerPrincipal ConsultantCommented:
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
Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

 
Jim P.Commented:
What user ID are you running it under?
0
 
Eugene ZCommented:
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
 
Eugene ZCommented:
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
 
ZberteocCommented:
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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now