Not able to create user under SQL server database

See more:

SQL

server

Hello,

I want to create database through script and at the same time want to create user inside that database. For this I have created below given stored procedure.

----Procedure Start----

CREATE PROCEDURE [dbo].[USP_CreateDatabase]     
(       
    @DatabaseName nvarchar(100),  
    @UserName nvarchar(100),
    @DBPassword nvarchar(100) 
)   
as 
/*   
exec USP_CreateDatabase 'ST_Test','client','client@123' 
*/  
BEGIN                        
    
    SET NOCOUNT ON; 

    DECLARE @scriptfirst nvarchar(max);

    IF NOT EXISTS(SELECT name FROM master.dbo.sysdatabases WHERE  name LIKE '%'+ @DatabaseName +'%')
    BEGIN
        SET @scriptfirst = N' use [master];' +char(13)+char(10)+ 
        ' Create database ['+ @DatabaseName +'];' +char(13)+char(10);

        EXEC sp_executesql @scriptfirst;

        --select @scriptfirst;
    END     

    IF NOT EXISTS(SELECT dp.name FROM sys.server_principals sp LEFT JOIN sys.database_principals dp ON sp.sid = dp.sid
    WHERE dp.name LIKE '%'+ @UserName +'%' and sp.default_database_name LIKE '%'+ @DatabaseName +'%')
    BEGIN
        SET @scriptfirst = N' use ['+ @DatabaseName +'];' +char(13)+char(10)+ 
        ' SET ANSI_NULLS ON;' +char(13)+char(10)+ 
        ' SET QUOTED_IDENTIFIER ON;' +char(13)+char(10)+ 
        ' CREATE USER [' + @UserName + '] FOR LOGIN [' + @UserName + '] WITH DEFAULT_SCHEMA=[dbo];' +char(13)+char(10)+ 
        ' ALTER ROLE [db_owner] ADD MEMBER [' + @UserName + '];' +char(13)+char(10);

        EXEC sp_executesql @scriptfirst;

        --select @scriptfirst;
    END 
END
GO

----Procedure End----

This procedure works fine when I ran with 'sa' (sysadmin) user.
On production we did not have 'sa' (sysadmin) rights. They provide us login that have server roles 'public' and 'dbcreator'. Now when I ran the stored procedure with dbcreator rights it creates the database but it did not create user.

It gives me an error:

Quote:

Msg 15063, Level 16, State 1, Line 4
The login already has an account under a different user name.
Msg 15151, Level 16, State 1, Line 5
Cannot add the principal 'client', because it does not exist or you do not have permission.

I checked the issue and found that it attached the newly created database with 'dbo' user. My requirement is to create database and create the 'client' user inside newly created database not 'dbo' user.

Note: On production we wil be having login with only 'dbcreator' rights.
Please let me know how can achieve this ?

What I have tried:

I tried several things:

1) Changed the database mode with multi-user but it did not work.

2) Tried to change newly created database owner to 'sa'.

EXEC sp_changedbowner 'sa'; 

But because of rights issue it gives me the error:

Quote:

Msg 15151, Level 16, State 1, Line 1
Cannot find the principal 'sa', because it does not exist or you do not have permission.