Creating new user programmatically
Posted on 2007-11-28
I am trying to create a new user from a stored procedure but I am getting an error. code is below for stored procedure:
Create PROCEDURE [dbo].[User_Insert]
SET NOCOUNT ON
SET XACT_ABORT ON
CREATE LOGIN @Loginname WITH PASSWORD=@Password, DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
But i am getting following error when I try to execute the stored procedure in sql 2005 interface
Msg 102, Level 15, State 1, Procedure User_Insert
Incorrect syntax near '@Loginname'.
if i use following code, it works:
CREATE LOGIN [newuser] WITH PASSWORD=N'abcde234', DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
whereas [newuser] is the name of the user being added to the sql.
I'd like to call above stored procedure from a c# code so that it could add a user into sql server logins, that's why I need to pass loginname & password as a parameter to the stored procedure.
Could you also tell me how can I add newly created user(above) to a particular database user?