locked
User access problem in database RRS feed

  • Question

  • Hi

    One user has the problem in accessing to Database

    User has access to appropriate  Active directory to access Database but he is unable to access it.

    Error message is 'Login failed for the user '

    everyone who is part of that Active directory group are able to access our Database but for one user it is giving problems

    Active directory group added as a user to Database.

    Do we need to add user/Active directory in server level as well to  access database/server ?

    Friday, November 18, 2011 5:11 PM

Answers

  • I have had similar problems at times.  Adding the Domain/User directly to the server as a login (I believe this can be done without adding any additional rights to the login) should work.

    The best I can tell, if I add the login to a Domain Local Group that is a SQL Server login, the SQL Server will not reread that membership until the SQL Server service restarts.  (After that it should be  possible to drop the specific login from the server, leaving access through the domain group.)

    I usually add logins to Domain Global Groups, then make those global groups members of the Domain Local Group that is granted right to the SQL Server.  That seems to work fine when a new login is added.

    But that is strictly anecdotal, since I have never done a scientific investigation. 

    Other people have reported this as well:

    https://connect.microsoft.com/SQLServer/feedback/details/248615/login-fails-when-user-is-granted-access-via-a-domain-group

    I would be very interested if your experience matches mine.

    RLF

    • Marked as answer by Peja Tao Monday, November 21, 2011 7:36 AM
    Friday, November 18, 2011 7:05 PM
  • Hi Russell

     

    Thanks for the reply

    I have added AD group in the server level security. so user is able to access Database now...

     

    • Marked as answer by Peja Tao Monday, November 21, 2011 7:36 AM
    Friday, November 18, 2011 8:44 PM

All replies

  • I have had similar problems at times.  Adding the Domain/User directly to the server as a login (I believe this can be done without adding any additional rights to the login) should work.

    The best I can tell, if I add the login to a Domain Local Group that is a SQL Server login, the SQL Server will not reread that membership until the SQL Server service restarts.  (After that it should be  possible to drop the specific login from the server, leaving access through the domain group.)

    I usually add logins to Domain Global Groups, then make those global groups members of the Domain Local Group that is granted right to the SQL Server.  That seems to work fine when a new login is added.

    But that is strictly anecdotal, since I have never done a scientific investigation. 

    Other people have reported this as well:

    https://connect.microsoft.com/SQLServer/feedback/details/248615/login-fails-when-user-is-granted-access-via-a-domain-group

    I would be very interested if your experience matches mine.

    RLF

    • Marked as answer by Peja Tao Monday, November 21, 2011 7:36 AM
    Friday, November 18, 2011 7:05 PM
  • Hi Russell

     

    Thanks for the reply

    I have added AD group in the server level security. so user is able to access Database now...

     

    • Marked as answer by Peja Tao Monday, November 21, 2011 7:36 AM
    Friday, November 18, 2011 8:44 PM