6 Replies - 951 Views - Last Post: 06 June 2009 - 12:22 PM

#1 elhamnoori  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 7
  • Joined: 29-May 09

create login

Posted 04 June 2009 - 07:21 AM

hi everybody!
I want to write an store procedure in SQL that:
it has two parameters for login Name and password. I write this code. but it has some errors.

USE DreamHome_Rental
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE usp_cteateLogin
	@lgnName NVARCHAR(10),
	@pswrd NVARCHAR(10)
AS
BEGIN
	CREATE LOGIN @lgnName WITH PASSWORD=@pswrd
END
GO




how can I do this?
and I want do this with out using sp_addlogin because MSDN writes: "This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Use CREATE LOGIN instead. "

Is This A Good Question/Topic? 0
  • +

Replies To: create login

#2 sloanthrasher  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 18
  • Joined: 31-May 09

Re: create login

Posted 04 June 2009 - 05:53 PM

The first thought that comes to mind is that the SP will run under the callers login -- They would need admin rights to add a new login ID to SQL Server.
What is the error message you are seeing?
Was This Post Helpful? 0
  • +
  • -

#3 elhamnoori  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 7
  • Joined: 29-May 09

Re: create login

Posted 04 June 2009 - 09:43 PM

View Postsloanthrasher, on 4 Jun, 2009 - 04:53 PM, said:

The first thought that comes to mind is that the SP will run under the callers login -- They would need admin rights to add a new login ID to SQL Server.
What is the error message you are seeing?


thanks Dear sloanthrasher!
I login to SQL Server with admin login. the error I'm seeing is:
 
Msg 102, Level 15, State 1, Procedure usp_cteateLogin, Line 6
Incorrect syntax near '@lgnName'.
Msg 319, Level 15, State 1, Procedure usp_cteateLogin, Line 6
Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.


Was This Post Helpful? 0
  • +
  • -

#4 kunkka  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 99
  • Joined: 15-May 09

Re: create login

Posted 04 June 2009 - 10:08 PM

you have missed a Semicolon somewhere.. if you can put up the code can give a look
Was This Post Helpful? 0
  • +
  • -

#5 elhamnoori  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 7
  • Joined: 29-May 09

Re: create login

Posted 05 June 2009 - 12:14 AM

View Postkunkka, on 4 Jun, 2009 - 09:08 PM, said:

you have missed a Semicolon somewhere.. if you can put up the code can give a look


dear kunkka!
the semicolon symbol is used between two statement. but this procedure has only one statemant.
no. the problem wasn't for missing a semicolon.
Was This Post Helpful? 0
  • +
  • -

#6 sloanthrasher  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 18
  • Joined: 31-May 09

Re: create login

Posted 05 June 2009 - 02:09 PM

The problem is simple syntax in the CREATE PROCEDURE statement. You need ( ) around the parameters.
The code should be something like:
USE DreamHome_Rental;
SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO
CREATE PROCEDURE usp_cteateLogin (
	@lgnName NVARCHAR(10),
	@pswrd NVARCHAR(10)
)
AS
BEGIN
	CREATE LOGIN @lgnName WITH PASSWORD=@pswrd;
END
GO


The error message is pointing to line 6, which is the CREATE PROCEDURE line. Specifically the line right after that one (for SQL Server, they are one line together).

This post has been edited by sloanthrasher: 05 June 2009 - 02:10 PM

Was This Post Helpful? 0
  • +
  • -

#7 elhamnoori  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 7
  • Joined: 29-May 09

Re: create login

Posted 06 June 2009 - 12:22 PM

View Postsloanthrasher, on 5 Jun, 2009 - 01:09 PM, said:

The problem is simple syntax in the CREATE PROCEDURE statement. You need ( ) around the parameters.
The code should be something like:
USE DreamHome_Rental;
SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO
CREATE PROCEDURE usp_cteateLogin (
	@lgnName NVARCHAR(10),
	@pswrd NVARCHAR(10)
)
AS
BEGIN
	CREATE LOGIN @lgnName WITH PASSWORD=@pswrd;
END
GO


The error message is pointing to line 6, which is the CREATE PROCEDURE line. Specifically the line right after that one (for SQL Server, they are one line together).


this code has still the same error.
but the following code runs successfully:
USE [DreamHome_Rental]
GO
/****** Object:  StoredProcedure [dbo].[uspCreateUser]	Script Date: 05/13/2009 19:50:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


-- =============================================
CREATE PROCEDURE [dbo].[uspCreateUser]
	@p1 NVARCHAR(20) , 
	@p2 NVARCHAR(20) 
AS
	DECLARE @STR NVARCHAR(1000)
	SET @STR='CREATE LOGIN [' + @p1 + '] WITH PASSWORD=''' + @p2 + ''',DEFAULT_DATABASE=[DreamHome_Rental];'			
	EXEC (@STR)

	GO



but i need to find another way to do this.

This post has been edited by elhamnoori: 06 June 2009 - 12:24 PM

Was This Post Helpful? 0
  • +
  • -

Page 1 of 1