Not able to create user under SQL server database

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:

"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 will 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) Given the 'client' login all the server role except sysadmin. But it did not created 'client' user in newly created database.

3) 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."

1 Reply

Know the answer? Post it — somebody with the same question will find it here.

Laxmidhar Sahoo6y ago
Hi

The below permission may help you

CREATE USER cannot be used to create a guest user because the guest user already exists inside every database. You can enable the guest user by granting it CONNECT permission, as shown:
GRANT CONNECT TO guest;
GO

For more details

https://docs.microsoft.com/en-us/sql/t-sql/statements/create-user-transact-sql?view=sql-server-ver15

Thanks and regards