June 23, 2014 at 10:33 am
Hi Friends,
one of my user not able to access SQL server via windows authentication. however he has db_owner rights on the database. he is getting following error:
Message
Login failed for user 'domain\user'. Reason: Token-based server access validation failed with an infrastructure error. Check for previous errors. [CLIENT: 10.xx.xxx.xx]
not sure what went wrong, earlier it was working fine (its SQL 2008 R2).
what i have done -
1. I restored a database
2. rename the existing database with _old name
3. rename the new restore database with the the previous database name which renamed to _old
not sure if this caused any problem, doesn't look like to me. i got the windows user checked with windows team they didn't find anything wrong with this user at domain level.
any help will be highly appreciated.
thanks,
DG
June 23, 2014 at 10:43 am
What have you tried? A quick google search of your exact error message produces a number of hits. Some of those are even right back to this site and all of them say basically the same thing (much expected). The user is question is likely in a group has deny connect to this database.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 23, 2014 at 12:04 pm
Dear Sean,
thanks for your quick response, after checking a lot on google i asked my question here when i didn't get any appropriate solution.
Please can you help checking "The user is question is likely in a group has deny connect to this database."
how can we check if the user/group has deny connect to the database?
thanks,
DG
June 23, 2014 at 12:16 pm
DKG-967908 (6/23/2014)
Dear Sean,thanks for your quick response, after checking a lot on google i asked my question here when i didn't get any appropriate solution.
Please can you help checking "The user is question is likely in a group has deny connect to this database."
how can we check if the user/group has deny connect to the database?
thanks,
DG
Is this user part of any groups? Do those groups have deny connect on the database in question?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 23, 2014 at 12:20 pm
Sean,
this user probably be a member of multiple groups(domain group) that i will check with windows team.
but how can i check at SQL level that any groups "have deny connect on the database in question"?
June 23, 2014 at 12:33 pm
DKG-967908 (6/23/2014)
Sean,this user probably be a member of multiple groups(domain group) that i will check with windows team.
but how can i check at SQL level that any groups "have deny connect on the database in question"?
Let me see I can help clarify.
Is this user a member of multiple domain groups? Are these domain groups tied to a role within sql server? Do any of these roles have deny connection to the database? Does that help?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 23, 2014 at 11:42 pm
Dear Sean,
from the very beginning i am trying to understand "how to check that any user or domain group have deny connection to the database?"
and i guess its not limited with database....because that domain user is not able to register this server from the other server (using ssms from out side). I may be wrong but it looks to me there is some permission deny at SQL server level instead of database level.
Hope this clarify the problem. Please express your thoughts.
thanks,
DKG
June 24, 2014 at 9:41 am
What is the state the error message 18456 give for this issue?
Should it be state 12?.
You may refer here.
Server level permission could be found with this sql.
Look for the user name or group name you have in the column Granteename in result set.
-- Find GranteeName who has deny permission on server
SELECT
ServerName = CONVERT(VARCHAR,SERVERPROPERTY('SERVERNAME'))
,class_desc
,major_id
,GranteeName = suser_name(grantee_principal_id)
,GrantorName = suser_name(grantor_principal_id)--,spr.type
, permission_name,state_desc
FROM sys.server_principals sp
JOIN sys.server_permissions spr ON spr.grantee_principal_id = sp.principal_id
--WHERE sp.name = '' -- enter group name
WHERE spr.state = 'D' -- Filter for DENY permissions
-Suneel
June 24, 2014 at 11:33 am
Did the restored database come from another server ? If so, there might be a SID mismatch for the user.
June 24, 2014 at 12:07 pm
homebrew01 - the db came from another server but i already checked there is no SID mistmatch.
thanks suneel kamavaram for the script and many thanks to Sean Lange.
Issue resolved by the help of Sean Lange - yes the user was a member of one group that group already added on the SQL server with deny permission.
June 24, 2014 at 12:15 pm
Glad you got it sorted out and thanks for letting us know.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply