SQL Server 2017 user not being created with script

Hi we have a project written in VB which creates a database and database user in SQL server 2005, this has been working for years, but now we want to move our services to a SQL 2017 database and for some reason the code is no longer creating a user for the database. This leads to us not being able to log in to the program we are trying to move to a SQL Server 2017 server. I have replaced the necessary files and updated the compatibility version as required for the software to run and it does, at this point it seems like only the database user is not being created.

Here is an example of the script being used to create the user:

/****** Object:  Login [VetMasterUser]    Script Date: 03/13/2008 17:17:10 ******/
/* For security reasons the login is created disabled and with a random password. */
/****** Object:  Login [VetMasterUser]    Script Date: 03/13/2008 17:17:10 ******/
CREATE LOGIN [VetMasterUser] WITH PASSWORD=N'3ÇÌL¨ .ÍÉ÷íóU@—¤‘_õ16})¦Â''*', DEFAULT_DATABASE=[Master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
EXEC sys.sp_addsrvrolemember @loginame = N'VetMasterUser', @rolename = N'sysadmin'
GO
ALTER LOGIN [VetMasterUser] ENABLE

Open in new window


At this point I am lost to what the reason is that the user is not being created,

Any help would be appreciated.
LIBRALEXAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

 
Máté FarkasDatabase Developer and AdministratorCommented:
You created a login not  user.
If you want to make a user then your have to assign this login to a database.
BTW, how do you know that user was not created?
0
 
LIBRALEXAuthor Commented:
Hi

Ok, I will modify the code to create a user instead and see if that helps

the User is not displayed in the Security/Users/ directory as shown in the attachment, and when I try to connect to the database with the program I get a message that says "The server was saved but a connection could not be established. Login failed for user 'VetMasterUser' "
Example-Image.docx
0
 
LIBRALEXAuthor Commented:
Hi

I found the problem,

I was looking at the wrong place, the user was created.

The problem was that it was only using windows authentication and not, SQL and windows authentication

Thanks for the help though, have a nice day
0
 
Máté FarkasDatabase Developer and AdministratorCommented:
No the problem was that you did not assign the login to the database just add a sysadmin server role.
This does not add the user to database. This is why you did not see that user in your database Security part.
0

Experts Exchange Solution brought to you by ConnectWise

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
 
LIBRALEXAuthor Commented:
Issue has been resolved, was also able to create the user
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.