Q:

Adding Login and users to SQL2005

I am trying to add users into our db by using a stored procedure. I am using the following code
declare @username as varchar(50)
set @username ='test1'
DECLARE @SQL NVARCHAR(4000);
begin

SET @SQL = 'CREATE LOGIN ' + @username + ' WITH PASSWORD = ''12345'',     DEFAULT_DATABASE=[dbname], DEFAULT_LANGUAGE=[British],     CHECK_EXPIRATION=OFF,  CHECK_POLICY=OFF';

EXECUTE(@SQL);

end
EXEC sp_grantdbaccess @username;
EXEC SP_ADDROLEMEMBER N'db_owner', @username,

This works but produces a schema for every new user. I would like to attach the users to an existing schema. Any help would be appreciate 

A:

You should replace sp_grantdbaccess with CREATE USER.  The procedure sp_grantdbacess is to be deprecated.
You can attach a default schema with CREATE USER.  From http://msdn.microsoft.com/en-us/library/ms173463(SQL.105).aspx
CREATE USER user_name 
    [ { { FOR | FROM }
      { 
        LOGIN login_name 
        | CERTIFICATE cert_name 
        | ASYMMETRIC KEY asym_key_name
      } 
      | WITHOUT LOGIN
    ] 
    [ WITH DEFAULT_SCHEMA = schema_name ]

