Are we doing something wrong in specifying how the user is to access our database, using different schemas?

Question

Are we doing something wrong in specifying how the user is to access our database, using different schemas?

Rod at work

SSC-Dedicated

Points: 33903
More actions
April 26, 2017 at 2:12 pm

#336620

I'm having problems making a test user account access the database for a new app we're writing. Whenever I try to run the app I get the following error:
"The underlying provider failed on Open. Login failed for user OURDOMAIN\App.Test_User"
In trying to figure out what's wrong and looking for answers I came across this question posted here: ADO.NET Connection String. I then checked the database and found that every one of us on the development team's default schema is our own domain login (not sure what that does) and the database role we're all in adds us to db_owner. Seems to me like we'd have a pass on everything, whereas the user's default schema is dbo. They should at least also have access to the additional schemas our database and app uses. But that referenced Stackoverflow article says that the connection string cannot specify the schema. So I'm no sure how we'd get to it using EF. What am I missing here?
Kindest Regards, Rod Connect with me on LinkedIn.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Chris Harshman SSC-Forever Points: 42192 More actions · Answer 1

You can look at the error logs in Management Studio under:
Management \ SQL Server Logs \ Current
to see a more detailed message on why the login failed. Schema defaults probably have nothing to do with the login failure, that's more to do with where SQL Server looks first to resolve objects that are only referenced with a 1 part name instead of SchemaName.ObjectName
https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-user-transact-sql

Rod at work SSC-Dedicated Points: 33903 More actions · Answer 2

Chris, do I need to be an administrator on the server in order to view the logs?

Kindest Regards, Rod Connect with me on LinkedIn.

Chris Harshman SSC-Forever Points: 42192 More actions · Answer 3

not necessarily on the server itself, but you do have to be in the SYSADMIN or SECURITYADMIN role for the instance to see the logs in Management Studio

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 4

Or if you have administrator (or sufficient) permission on the server, you can open the actual text files that the logs are stored in (errorlog, errorlog.1 etc).

John

Rod at work SSC-Dedicated Points: 33903 More actions · Answer 5

Chris Harshman - Thursday, April 27, 2017 10:13 AM
not necessarily on the server itself, but you do have to be in the SYSADMIN or SECURITYADMIN role for the instance to see the logs in Management Studio

Thank you Chris, I'm neither in the SYSADMIN nor SECURITYADMIN roles. Guess I'll have to get together with the DBA to resolve this issue.

Kindest Regards, Rod Connect with me on LinkedIn.