CODEWITHSUNDEEP
sql-server
Konrad Viltersten
Konrad Viltersten

Reputation: 39190

Can't log in with user of role db_owner

I'm running the following script and receive no errors nor warnings. I also verify that the user actually has been created. When I click on it, I get to see the roles (the membership db_owner is checked).

create login normalLogin
  with password = 'normalpass',
  check_policy = off
create login adminLogin
  with password = 'adminpass',
  check_policy = off

create user common for login normalLogin;
create user admin for login adminLogin;

execute sp_addrolemember db_owner, admin

However, when I try to log in to the server using admin as user name and adminpass as password (SQL authorization), I get the error message telling me this.

Login failed for user 'admin'. (Microsoft SQL Server, Error: 18456)

Accessing the helping link, I get two feasible suggestions.

  1. Verify that SQL Server is configured in Mixed Authentication Mode.
  2. Verify that SQL Server login exists and that you have spelled it properly.

I'm certain that (1) is correct (still, how can I re-check it?) so the suspicions fall on (2). Is the script above insufficient? I have the feeling that maybe the user on DB level isn't enough and that I need to create it on server level. Is it so? How can I script such an operation? I wish, of course, to keep the security tight and compartmentalized.

Upvotes: 0

Views: 3321

Answers (1)

Deep Kalra
Deep Kalra

Reputation: 1428

Can you try this:

  1. Open your SQL Server Management Studio.
  2. Database server right click and go to properties.
  3. Choose Security option and check SQL Server and Windows authentication mode.
  4. Enable TCP/IP connection in SQL Configuration Manager.
  5. Restart your SQL server service.

Upvotes: 4

Related Questions