0

I have a windows user who I want to add to just one database SalesDeals on the server. I added them in SalesDeals-> Security -> Users section of the Database and gave them db_datareader permissions on the database. I cannot add them in the Top level Security section since I don't have permissions for that.

The issue is when the user tries to connect to the server CS01\CS01 they get login failed for the user error.

Do I need to get them added to the top level Server -> Security -> Users section for them to be able to connect to the Database.

Thanks

Improve this question

3 Answers 3

Reset to default
1

Yes, you must first create a Login on the SQL Server instance, then create a User in the database which maps to that Login.

Without a Login, the user cannot be authenticated to SQL Server (unless you're using a Contained Database, which is not a feature of your version of SQL Server), so they won't be able to access the database.

Improve this answer
3
  • Oh ok..I was thinking I can directly add the windows user in the security section of the database and they should be able to connect to it.. Commented Jul 18, 2019 at 18:16
  • My understanding is that this is only possible with Contained Databases, and that's a SQL Server 2016 feature. Commented Jul 18, 2019 at 18:16
  • OK Thanks..for SQL Login I know we had to create it on the server instance but I thought windows user could directly be added to the database security Commented Jul 18, 2019 at 18:20
0

You can create a Windows User as a Contained Database User in SQL Server 2012 (see here) using the steps you've performed, but that login failure will occur if you're not specifying the database in your connection string, or in SSMS if connecting via SSMS.

Improve this answer
0

How access works in SQL Server unless the database is a contained database. :

Login-->user-->role

So to provide access, you need a login created which will allow access to the instance and then map this login with the required database. This will create the user and permissions (role) can be granted.

Microsoft Doc

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.