473,595 Members | 2,576 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Problem in creating stored procedure.

88 New Member
I'm trying to create a stored procedure in MSSQL Server 2005 that'll perform the following jobs:
1) Create a login.
2) Create an user in TestDB database for the login created in step 1.
3) Assign the role 'db_generaluser ' to the user created in step 2.

The login name and password for the login to be created will be supplied from externally through input parameters. If this procedure executes successfully it returns 0 else 1 to the caller through an output parameter.

When I try to compile it, it's prompting the following error messages:
Msg 102, Level 15, State 1, Procedure sp_CreateLogin, Line 23
Incorrect syntax near '@loginname'.

Msg 319, Level 15, State 1, Procedure sp_CreateLogin, Line 23
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.

Msg 319, Level 15, State 1, Procedure sp_CreateLogin, Line 27
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.

What's my mistake? Plz help. My code follows:

Expand|Select|Wrap|Line Numbers
  1. SET ANSI_NULLS ON
  2. GO
  3. SET QUOTED_IDENTIFIER ON
  4. GO
  5. -- ==========================================================================================================
  6. -- Author:    Priyamtheone
  7. -- Create date: 2009-Oct-08
  8. -- Description:    This procedure is used to create a login, create an user in TestDB database associated to
  9. --        that login and assign the role 'db_generaluser' to that user. The login name and password
  10. --        will be supplied from externally through input parameters. If this procedure executes
  11. --        successfully it returns 0 else 1 to the caller through an output parameter.
  12. -- ==========================================================================================================
  13. CREATE PROCEDURE [dbo].[sp_CreateLogin] 
  14.     -- Add the parameters for the stored procedure here.
  15.     @loginname sysname,
  16.     @passwd    sysname,
  17.     @intRetVal int = 0 output    --Output parameter.
  18. AS
  19. BEGIN
  20.     -- SET NOCOUNT ON added to prevent extra result sets from interfering with SELECT statements.
  21.     SET NOCOUNT ON;
  22.     SET @intRetVal = 0;
  23.  
  24.     BEGIN TRY
  25.         CREATE LOGIN @loginname WITH PASSWORD=@passwd, DEFAULT_DATABASE=[TestDB], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
  26.  
  27.         CREATE USER @loginname FOR LOGIN @loginname WITH DEFAULT_SCHEMA=[dbo]
  28.  
  29.         EXEC sp_addrolemember N'db_generaluser', @loginname
  30.     END TRY
  31.     BEGIN CATCH
  32.         SET @intRetVal = 1;    --Something went wrong. So set the return value to 1.
  33.     END CATCH
  34. END
  35. GO
Oct 9 '09 #1
2 2812
ck9663
2,878 Recognized Expert Specialist
Try removing the default_schema parameter. If you left it blank, it will default to 'dbo' anyway.

Good luck!!!


--- CK
Oct 14 '09 #2
nbiswas
149 New Member
Try something like
Expand|Select|Wrap|Line Numbers
  1. set @str = '
  2.         CREATE LOGIN ' + @loginname  + 'WITH PASSWORD=' + @passwd +', DEFAULT_DATABASE=[TestDB], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
  3.  
  4.         CREATE USER' +  @loginname + 'FOR LOGIN' +  @loginname + ' WITH DEFAULT_SCHEMA=[dbo]'
Hope it will work.. didnot test though
Nov 9 '09 #3

Sign in to post your reply or Sign up for a free account.

Similar topics

2
7317
by: Yves Touze | last post by:
Hi All, I'm trying to migrate from SQL Server 7.0 to SQL Server 2000. I've got some ASP page which call VB components that retrieve shaped recordsets from SQL Server using the MSDATASHAPE provider. Precisely, here is the code i have Dim Cmdobj As New ADODB.Command Cmdobj.ActiveConnection = oconn Cmdobj.CommandType = adCmdStoredProc
1
4470
by: John Mullan | last post by:
Hi I have a problem creating a backup in SQL Server 6.5. Although, the backup produced does restore ok, I want to stop this error message appearing everytime I make a backup. The error message is: " Can't allocate space for object 'sysbackupdetail' in database 'msdb' because the 'default' segment is full. If you ran out of space in Syslogs, dump the transaction log. Otherwise, use ALTER DATABASE or sp_extendsegment to increase the size...
3
10625
by: Cesco | last post by:
Hallo to everybody. I have a DTS in SQL Server 2000 and I need to execute it from stored procedure. I know that there are various method fot does this but they doesn't work. The first method that I try to use is with the stored procedure "xp_cmdshell". If I write in DOS prompt dtsrun /F c:\DTS1.dts the DTS1 will execute well
0
2567
by: billmiami2 | last post by:
Perhaps many of you MS Access fanatics already know this, but it seems that stored procedures and views are possible in Jet. I thought I would leave this message just in case it would help anyone. I discovered this the other day while doing some experiments with ADO and ADO.NET. Basically, I wanted to run a stored MS Access query with parameters using the syntax Execute MyProcedure @Param1, @Param2...
1
3366
by: Timothy V | last post by:
Hi, I was looking at this method within the SqlCommand class. It is the SqlCommand.Parameter.Add(string, object) method. My question is, how do I use this parameter within a stored procedure? What i am really trying to do is dynamically change the ORDER BY method within the SPROC. For example (i know this won't work): CREATE PROCEDURE dbo.ClassFindAll (
1
1798
by: Paul | last post by:
Hi, I wish to be able to add tables to a sql server database at runtime from my asp.net application. As well as creating fields I also wish to be able to create indexes on selected fields and to assign user permissions. (I need to assign permissions to the table object as I will be using sp_executesql or exec to execute a string). I'm thinking there are 3 possible ways to do this:
6
1908
by: Liang Zhang | last post by:
I am now developing a java program connected to db2 via jdbc, and the db2 server(version 8.2) is installed in a visual machine running also on my computer, however, i always found that the java program suspended when sending a request to drop a stored procedure, I don't know why, and now i am really bored with this issue, can someone help me? thanks -- Best Regards Liang Zhang
9
8967
by: jyothi1105 | last post by:
Hi all, Here is some information which could help people who want to create stored procedures and execute them in their program. You can create stored procedures in two ways: Through front end or writing procedure from backend SQLServer. Back End First we'll cover how to write a stored procedure in back end. The following example is how to create a store procedure for MSSql Server:
1
13181
by: apothecary | last post by:
Hello Newbie here. Is there a way of creating a VIEW...using a stored procedure. I am basically trying to create a view to return some data that I am getting using a stored procedure. I have created the procedure and when I execute this its working ok. The stored procedure uses a datefrom and dateTo which I have set up by
0
7955
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
7883
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
8379
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
6674
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
5839
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
3873
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
3911
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
2391
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
1
1490
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.