locked
db access RRS feed

  • Question

  • i have given db access to the user. But still he is getting error, like "cant't open db dbname requested by the login. The login failed. login failed for user xyz." Kindly anyone help me
    Thursday, August 11, 2011 9:01 AM

Answers

  • What is the database default for that login.. It is possible 'master' database to which you have not given rights
    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/


    Hallo Uri,

    just for clarification - maybe I fail - but by default the guest account has the "connect" privilege for master.

    So - from my point of view - all users should have access to master.

    I suppose that default database is different from master and no access privileges have been granted.

    This will make sence concerning the error message which has been mentioned from the thread initiator.


    Uwe Ricken

    MCITP Database Administrator 2005
    MCITP Database Administrator 2008
    MCITS Microsoft SQL Server 2008, Database Development

    db Berater GmbH
    http://www-db-berater.de
    Thursday, August 11, 2011 2:13 PM

All replies

  • i have given db access to the user. But still he is getting error, like "cant't open db dbname requested by the login. The login failed. login failed for user xyz."
    Thursday, August 11, 2011 8:51 AM
  • We have a seperate Forum category for security related question.

    SQL Server Security

     


    - Kerobin
    Thursday, August 11, 2011 8:56 AM
  • hello anyone is there who knows the answer.
    Thursday, August 11, 2011 10:22 AM
  • Can you check that the login you have created has the "default database" set to the database you've granted permissions to.

    Are there any error codes for the login failure, ie.

    Msg 18456, Level 14, State x, Server <server name>, Line 1
    Login failed for user '<user name>'

    Cheers,

    Andrew

     

    • Proposed as answer by John Eisbrener Thursday, August 11, 2011 12:50 PM
    Thursday, August 11, 2011 10:40 AM
  • What is the database default for that login.. It is possible 'master' database to which you have not given rights
    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    • Proposed as answer by John Eisbrener Thursday, August 11, 2011 12:50 PM
    • Unproposed as answer by John Eisbrener Thursday, August 11, 2011 12:50 PM
    Thursday, August 11, 2011 10:41 AM
  • Go

    to SQL Server >> Security >> Logins and right click on "USER XYZ" and select Properties

     

    Then

    in Login Properties, go to the User Mapping tab. Then, on the User Mapping tab, select the desired database

    then

    check the role db_owner.

    Click OK

    .

     
    Thursday, August 11, 2011 11:48 AM
  • Thursday, August 11, 2011 1:39 PM
  • What is the database default for that login.. It is possible 'master' database to which you have not given rights
    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/


    Hallo Uri,

    just for clarification - maybe I fail - but by default the guest account has the "connect" privilege for master.

    So - from my point of view - all users should have access to master.

    I suppose that default database is different from master and no access privileges have been granted.

    This will make sence concerning the error message which has been mentioned from the thread initiator.


    Uwe Ricken

    MCITP Database Administrator 2005
    MCITP Database Administrator 2008
    MCITS Microsoft SQL Server 2008, Database Development

    db Berater GmbH
    http://www-db-berater.de
    Thursday, August 11, 2011 2:13 PM
  • How does this user try to connect? From an application? In such case, how does the connection string look like? From SSMS? Something else?

    Is it an SQL login or a Windows user?


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Thursday, August 11, 2011 10:06 PM
  • Go

    check the role db_owner.

    Click OK

     

    With respect - that's the most stupid recommendation I've ever read.

    Why should a user have db_owner privileges to a database?

    Do you know what permission a db_owner will have?

    At least the "connect" privilege is required to connect to the database.
    other privileges have to be granted by datbase roles like db_datareader ...
    best recommendation to you...

    read: http://vyaskn.tripod.com/sql_server_security_best_practices.htm


    Uwe Ricken

    MCITP Database Administrator 2005
    MCITP Database Administrator 2008
    MCITS Microsoft SQL Server 2008, Database Development

    db Berater GmbH
    http://www-db-berater.de
    Friday, August 12, 2011 4:18 AM