March 10, 2008 at 3:27 pm
Hi All,
This should be simple but I seem to be playing with my mental blocks today. In the following Create Procedure I receive an "Incorrect syntax near '@NewPassword" referring to the line ....WITH PASSWORD= @NewPassword. It hasn't mattered how I've tried to format @NewPassword.
CREATE PROCEDURE sproc_Create_New_User
(
@NewUserName VARCHAR(32),
@NewPassword VARCHAR(8)
)
AS
SET NOCOUNT ON
CREATE LOGIN [@NewUserName] WITH PASSWORD= @NewPassword,
DEFAULT_DATABASE=[master],
CHECK_EXPIRATION=OFF,
CHECK_POLICY=OFF
GO
What am I not seeing?
Thank you.
March 10, 2008 at 5:04 pm
geedeearr (3/10/2008)
Hi All,This should be simple but I seem to be playing with my mental blocks today. In the following Create Procedure I receive an "Incorrect syntax near '@NewPassword" referring to the line ....WITH PASSWORD= @NewPassword. It hasn't mattered how I've tried to format @NewPassword.
CREATE PROCEDURE sproc_Create_New_User
(
@NewUserName VARCHAR(32),
@NewPassword VARCHAR(8)
)
AS
SET NOCOUNT ON
CREATE LOGIN [@NewUserName][/i] WITH PASSWORD= @NewPassword,
DEFAULT_DATABASE=[master],
CHECK_EXPIRATION=OFF,
CHECK_POLICY=OFF
GO
What am I not seeing?
Thank you.
You've got Name brackets around a Variable...
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 10, 2008 at 5:08 pm
Oops! I just remembered...
A known limitation of CREATE LOGIN is that the login name has to be a literal.
Thus, you will have to use dynamic SQL to parametize the Login Name in your sProc.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 10, 2008 at 9:42 pm
Thank you.
I didn't know that. I'll go with a dynamic sproc then.
March 10, 2008 at 9:57 pm
Actually, you can use variables as parameters to [font="Courier New"]sp_AddLogin()[/font].
Sorry, I keep remembering additional details...
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 10, 2008 at 10:07 pm
That sproc I already did know about from SQL 2000. I'm trying to get all my old scripts updated (me as well), saw the CREATE LOGIN and tried to use it. btw, the extraneous brackets around @NewUserName were left over from me trying anything to get around that error. :ermm: I won't come clean about any other silly things I tried. I didn't clean up my code very well before I posted.
Thank you again.
March 11, 2008 at 10:02 am
fyi
Attached is a text file of the dynamic sql I ended with and it works very well.
Thanks again.
March 11, 2008 at 10:35 pm
Very nice. Glad it worked out.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply