Udai Mathur

Udai Mathur

  • NA
  • 49
  • 11k

Not able to create user under SQL server database

Aug 7 2020 2:05 AM
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----
  1. CREATE PROCEDURE [dbo].[USP_CreateDatabase]  
  2. (  
  3. @DatabaseName nvarchar(100),  
  4. @UserName nvarchar(100),  
  5. @DBPassword nvarchar(100)  
  6. )  
  7. as  
  8. /*  
  9. exec USP_CreateDatabase 'ST_Test','client','client@123'  
  10. */  
  11. BEGIN  
  12. SET NOCOUNT ON;  
  13.   
  14. DECLARE @scriptfirst nvarchar(max);  
  15.   
  16. IF NOT EXISTS(SELECT name FROM master.dbo.sysdatabases WHERE name LIKE '%'+ @DatabaseName +'%')  
  17. BEGIN  
  18. SET @scriptfirst = N' use [master];' +char(13)+char(10)+  
  19. ' Create database ['+ @DatabaseName +'];' +char(13)+char(10);  
  20.   
  21. EXEC sp_executesql @scriptfirst;  
  22.   
  23. --select @scriptfirst;  
  24. END  
  25.   
  26. IF NOT EXISTS(SELECT dp.name FROM sys.server_principals sp LEFT JOIN sys.database_principals dp ON sp.sid = dp.sid  
  27. WHERE dp.name LIKE '%'+ @UserName +'%' and sp.default_database_name LIKE '%'+ @DatabaseName +'%')  
  28. BEGIN  
  29. SET @scriptfirst = N' use ['+ @DatabaseName +'];' +char(13)+char(10)+  
  30. ' SET ANSI_NULLS ON;' +char(13)+char(10)+  
  31. ' SET QUOTED_IDENTIFIER ON;' +char(13)+char(10)+  
  32. ' CREATE USER [' + @UserName + '] FOR LOGIN [' + @UserName + '] WITH DEFAULT_SCHEMA=[dbo];' +char(13)+char(10)+  
  33. ' ALTER ROLE [db_owner] ADD MEMBER [' + @UserName + '];' +char(13)+char(10);  
  34.   
  35. EXEC sp_executesql @scriptfirst;  
  36.   
  37. --select @scriptfirst;  
  38. END  
  39. END  
  40. GO  
  41.   
  42. ----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."

Answers (1)