locked
Windows group member not able to access database RRS feed

  • Question

  • Hi,

    I've a user who is not able to access a specific database. He is a member of a group that has access to the database, I can do an execute as that user in the database and it works.

    I've traced his attempt to access the database in profiler and can see the message

    The server principal "domainname\account" is not able to access the database "databasename" under the current security context.

    using xp_loginfo and looking in AD I can see he is a member of this group, I've added a test account to this group and can execute this sproc.

    I'm no expert on Kerberos but our windows admin assures me there is nothing wrong with this users account, having checked his kerbeos token.

    I can see there are no denys in the database.

    I'm out of ideas what to check next and would appreciate any suggestions. 


    Sean

    Friday, January 31, 2014 3:38 PM

Answers

  • Hi,

    apologies for the delay replying, it was a windows related issue, I got the user to restart his machine and it was working again when he logged in this morning.

    He was definitely not picking up his membership of the approriate AD group.

     In future I'll be quicker to check what he is picking up in his kerberos ticket using  the "GPResult /R" command at the DOS prompt and if the appropruate group isn't present I'll get the user to Lock\unlock their machine and if that doesn't work to restart it.

    I appreciate your replies and sorry to waste your time with a non SQl issue.


    Sean

    • Marked as answer by Sean2000 Monday, February 3, 2014 9:56 AM
    Monday, February 3, 2014 9:54 AM

All replies

  • Is he relying upon membership in the local administrator group, but not using "Run as administrator" when he starts the process?

    In addition to the Profiler message, what error does the user receive? Are there any related errors in the SQL Server error log?


    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

    Friday, January 31, 2014 4:49 PM
  • Hi,

    thanks for the reply

    No he was just getting the message I saw in the profiler trace and nothing else in the logs.

    I got him to run "GPResult /R" at his DOS command and this group doesn't appear so it looks like an issue on the windows AD side.

    Just need to find out why he isn't picking up this group membership, he's done several locks and unlocks prior to this to pick it up, this time I've got him to restart his machine, hopefully that will resolve it.


    Sean

    Friday, January 31, 2014 5:22 PM
  • If you do "EXECUTE AS LOGIN = 'domainname\account'" in some other database, and then to USE on this database, does that work?

    Exactly does this user try to access the database? Since you got this from Profiler, I assume that he is already logged into the server?


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Proposed as answer by Sofiya Li Monday, February 3, 2014 3:16 AM
    Friday, January 31, 2014 10:34 PM
  • Hi,

    apologies for the delay replying, it was a windows related issue, I got the user to restart his machine and it was working again when he logged in this morning.

    He was definitely not picking up his membership of the approriate AD group.

     In future I'll be quicker to check what he is picking up in his kerberos ticket using  the "GPResult /R" command at the DOS prompt and if the appropruate group isn't present I'll get the user to Lock\unlock their machine and if that doesn't work to restart it.

    I appreciate your replies and sorry to waste your time with a non SQl issue.


    Sean

    • Marked as answer by Sean2000 Monday, February 3, 2014 9:56 AM
    Monday, February 3, 2014 9:54 AM