?
Solved

Create Login script to the server

Posted on 2008-10-15
5
Medium Priority
?
3,394 Views
Last Modified: 2008-10-15
I am trrying to create logins to a few database servers, i tried two scripts, some of them work on  a particualr server while the second works on some servers.

here are the scripts

USE [master]
GO
if not exists(select * from sys.server_principals where name = ABC\apps_dev')
begin
      CREATE LOGIN [ABC\apps_dev] FROM WINDOWS
end
GO

It gives me an error on some database servers
Line 3: Incorrect syntax near 'LOGIN'.

I tried this on the server it fails  and this works on some server
USE [master]
GO
if not exists(select * from syslogins where name = 'ABC\apps_dev')
begin
      exec sp_grantlogin 'ABC\apps_dev'
end
GO

I would like to know why these versions work on some servers, is this a versioning issue and how can I verify which version is used.

0
Comment
Question by:countrymeister
[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
  • 3
5 Comments
 
LVL 37

Expert Comment

by:momi_sabag
ID: 22722096
create login only works on sql 2005
won't work on sql 2000
0
 
LVL 1

Author Comment

by:countrymeister
ID: 22722300
momi_sabaq

How can I verify the version of the database server
0
 
LVL 13

Expert Comment

by:devsolns
ID: 22722342
You have to use the syntax below for SQL SERVER 2000:

EXEC sp_grantlogin 'Corporate\Test'
EXEC sp_addlogin 'test','hello'

See this link:
http://support.microsoft.com/kb/325003
0
 
LVL 13

Accepted Solution

by:
devsolns earned 1000 total points
ID: 22722352
To Get the version:
SELECT  SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')
0
 
LVL 13

Expert Comment

by:devsolns
ID: 22722380
See this link to match the product versions to what you have returned from the query above:

http://support.microsoft.com/kb/321185
0

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

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

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.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

765 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