TECHNOLOGIES
FORUMS
JOBS
BOOKS
EVENTS
INTERVIEWS
Live
MORE
LEARN
Training
CAREER
MEMBERS
VIDEOS
NEWS
BLOGS
Sign Up
Login
No unread comment.
View All Comments
No unread message.
View All Messages
No unread notification.
View All Notifications
Answers
Post
An Article
A Blog
A News
A Video
An EBook
An Interview Question
Ask Question
Forums
Monthly Leaders
Forum guidelines
Udai Mathur
NA
49
10.9k
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----
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."
Reply
Answers (
1
)
User defined table in Sql Server
How to install sql instance on some system which has already sql serve