Create User cmd problem

[porfirion]

Hi,
I'm writing c# app and I want to add a new login and user to a SS 2005 DB using Stored Procedure.
I've come with the folowing code:

DECLARE @fullName nvarchar(50)
SET @fullName = @lastName+' '+@firstName

SET @cmd = 'CREATE USER '+ @fullName +' FOR LOGIN ' + @shortName;
EXEC sp_executesql @cmd

can someone tell me how can I create a new user with a space between first and a last name ?

[debasisdas]

I am don't think spaces are allowed .

Try enclosing the fullname with double quotes. That might help.

[porfirion]

I've already tried with the " but it did not work either :(

If it's no problem to create that kind of user, using wizard from the context menu on the DB>Security>Users>New user ... - there should be some way of doing the same thing from the code level

[porfirion]

Ok I've did some research and came up with an answer to my problem:

CREATE PROCEDURE test
AS
DECLARE @s nvarchar(20)
SET @s = 'jeden dwatrzy'
EXEC sp_adduser test2 ,@s

or with the usage of the transaction

CREATE PROCEDURE test
AS
BEGIN transaction
DECLARE @s nvarchar(100)
declare @a nvarchar(30)
SET @a = '[jakis nowy]'
 
SET @s = 'CREATE USER'+ @a +' FOR LOGIN [test2]'
EXEC sp_executesql @s
COMMIT transaction