Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Create Login script to the server

Posted on 2008-10-15
5
Medium Priority
?
3,397 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

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

597 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