Skip to content
  • Categories
  • Recent
  • Tags
  • Popular
  • World
  • Users
  • Groups
Skins
  • Light
  • Cerulean
  • Cosmo
  • Flatly
  • Journal
  • Litera
  • Lumen
  • Lux
  • Materia
  • Minty
  • Morph
  • Pulse
  • Sandstone
  • Simplex
  • Sketchy
  • Spacelab
  • United
  • Yeti
  • Zephyr
  • Dark
  • Cyborg
  • Darkly
  • Quartz
  • Slate
  • Solar
  • Superhero
  • Vapor

  • Default (No Skin)
  • No Skin
Collapse
Code Project
CODE PROJECT For Those Who Code
  • Home
  • Articles
  • FAQ
Community
  1. Home
  2. Database & SysAdmin
  3. Database
  4. MSSQL Server 2005- Problem in creating strored procedure.

MSSQL Server 2005- Problem in creating strored procedure.

Scheduled Pinned Locked Moved Database
databasehelpsharepointsql-serversysadmin
1 Posts 1 Posters 0 Views 1 Watching
  • Oldest to Newest
  • Newest to Oldest
  • Most Votes
Reply
  • Reply as topic
Log in to reply
This topic has been deleted. Only users with topic management privileges can see it.
  • P Offline
    P Offline
    priyamtheone
    wrote on last edited by
    #1

    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:

    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    -- ==========================================================================================================
    -- Author: Priyamtheone
    -- Create date: 2009-Oct-08
    -- Description: This procedure is used to create a login, create an user in TestDB database associated to
    -- that login and assign the role 'db_generaluser' to that user. The login name and password
    -- 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.
    -- ==========================================================================================================
    CREATE PROCEDURE [dbo].[sp_CreateLogin]
    -- Add the parameters for the stored procedure here.
    @loginname sysname,
    @passwd sysname,
    @intRetVal int = 0 output --Output parameter.
    AS
    BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from interfering with SELECT statements.
    SET NOCOUNT ON;
    SET @intRetVal = 0;

    BEGIN TRY
    	CREATE LOGIN @loginname WITH PASSWORD=@passwd, DEFAULT\_DATABASE=\[TestDB\], DEFAULT\_LANGUAGE=\[us\_english\], CHECK\_EXPIRATION=OFF, CHECK\_POLICY=OFF
    
    	CREATE USER @loginname FOR LOGIN @loginname WITH DEFAULT\_SCHEMA=\[dbo\]
    
    	EXEC sp\_addrolemember N'db\_generaluser', @loginname
    END TRY
    BEGIN CATCH
    	SET @intRet
    
    1 Reply Last reply
    0
    Reply
    • Reply as topic
    Log in to reply
    • Oldest to Newest
    • Newest to Oldest
    • Most Votes


    • Login

    • Don't have an account? Register

    • Login or register to search.
    • First post
      Last post
    0
    • Categories
    • Recent
    • Tags
    • Popular
    • World
    • Users
    • Groups