Click here to Skip to main content
15,610,824 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:

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) 
exec USP_CreateDatabase 'ST_Test','client','client@123' 

    DECLARE @scriptfirst nvarchar(max);

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

        EXEC sp_executesql @scriptfirst;

        --select @scriptfirst;

    IF NOT EXISTS(SELECT FROM sys.server_principals sp LEFT JOIN sys.database_principals dp ON sp.sid = dp.sid
    WHERE LIKE '%'+ @UserName +'%' and sp.default_database_name LIKE '%'+ @DatabaseName +'%')
        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;

----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:
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:
Msg 15151, Level 16, State 1, Line 1
Cannot find the principal 'sa', because it does not exist or you do not have permission.
Updated 6-Aug-20 2:44am
Herman<T>.Instance 6-Aug-20 8:43am    
To create a new user you first create a Login and then the User.

1 solution

It's not possible with only dbcreator role. You need ALTER ANY USER permission.
Share this answer
Udai Karan Mathur 7-Aug-20 3:17am    
Thanks for your response. I given my 'client' login to all the server roles except 'sysadmin' but then too same issue exists. It creates the database with 'dbo' user not 'client' user.

Please let me know what role / permission I am missing.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900