Post reply

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

  • 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.

  • April 26, 2017 at 3:06 pm

    #1940113

    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

  • April 27, 2017 at 8:44 am

    #1940226

    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.

  • April 27, 2017 at 10:13 am

    #1940261

    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

  • April 28, 2017 at 3:15 am

    #1940343

    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

  • April 28, 2017 at 9:16 am

    #1940395

    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.

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

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