Comments (11)

  1. Paul Storm says:

    Hi Amit,

    "DATEADD" doesn't seem to take bigint arguments. Once the difference between Record Time and sys.ms_ticks gets larger than 24 days 20 hours and 50 minutes the Notification Time calculation fails: 2,147,483,647 / (1000 * 60 * 60 * 24) = 24.855

    Something like the following is needed:

    dateadd (ss, ((a.[Record Time]/1000) - (sys.ms_ticks/1000)), GETDATE()) as [Notification_Time]

    Thanks for your posting,

    regards,

    Paul

  2. Bill says:

    The problem I am having with this error is that I am the new DBA and have local admin rights on the server. I can't get into SSMS so I can't run any sql queries or grant my login 'control server' privs. Any ideas how to get around the problem?

  3. Balmukund says:

    Hi Bill,

    Follow msdn.microsoft.com/.../dd207004.aspx if you are windows admin.

  4. Mohamed Azlan says:

    Hi, sorry if this is a double post. I am not sure if the first post went through.

    Recently, to deploy my site, I changed my authentication from 'Windows' to 'SQL authentication'. Successfully added the user to ‘SecurityLogins’. But having problem enabling database role membership as it returns error 15247. I believe error 15151 is also that of permission issues.

    Here's an extra info in case it might help narrow down the problem scope, if i access my server it shows this error;

    "Login failed for user 'kaneXtreme' ".

    In my localhost,it shows this error;

    "The SELECT permission was denied on the object, database, schema"

    In my opinion, I think both error messages are pointing to a similar problem which is permission.

    This problem has been remained unsolved for some time now.

    If anyone have come across this problem before I really hope to get a few input to work around on this.

    Thank you in advance.

  5. Mohamed Azlan says:

    Solution found for my own thread 7 Jan 2012 4:46 AM

    After struggling for so long i found a solution. Thank God 🙂

    Before you reinstall SQL server using Mixed Mode Authentication. (I know the pain of doing that. Sometimes, it wont work for this because you backup your database.mdf under the non permissible user and when you attach it after reinstalling SQL it still shows the same 15247 error)

    What worked for me is;

    Logged in under windows authentication

    1) Went to DatabaseSecurity -> Right clicked on my role -> Selected all the necessary permissions

    2) Went to Security ->Right clicked on my role -> Selected all the necessary permissions

    3) Restart SQL

    4) Log in using SQL authentication

    5) Repeat steps (1) & (2)

    By God’s grace it should work. If not, try reinstalling SQL with a positive mind

  6. Tim Kelly says:

    I have lots of SQL Servers and linked servers are utilized heavily in my environment and there is one very rare cause that I have not seen documented anywhere. If you have an existing linked server and have created the proper SPN's and setup the proper AD Delegation and everything is working fine and then all of a sudden someone with proper rights gets the above described error 14/11 with reference to NT Authority/Anonymous yet everyone else can test the connection and query across without issue...it is as simple as having the developer restart SSMS and sometimes there machine. I have seen this only 3 times, if someone knows of a fix out there for SSMS or for an API on the client machine, please reply. Hope this helps save someone time.

    -TK

  7. Barry says:

    Had this same issue.

    What caused it was that I set "Permission to Connect to database engine" to "Denied" in a different Active Directory group.  This was in the Login Properties -> Status.

    What I did not understand is even if a user is in a differnt Active Directory group that is Granted access, the Deny access in the other AD group takes precedence.  Any user in the "Denied" AD group will never be able to login no matter what other AD groups are granted access.

    The error messages are the same as above...   wish Microsoft would put an error in saying "login denied access due to permissions" or something like that.

    Good luck!

  8. S.E. says:

    Do not forget multiple domain environments. For example, you can add a local domain group from another domain as an SQL Server login without any problem - however, of course, that login will not grant access to anyone. The error message in the SQL Server error log will be the same as above.

    Example: SQL Server is in the NORTH.company.com domain. You can add a login from SOUTH.company.com domain like this: SOUTHAppContacts

    No members of group SOUTHAppContacts can access SQL Server.

    Just be aware.

  9. Punyabrata says:

    Hi Amit, what is the resolution of this issue?

  10. MrCapable says:

    My Errors:

    Reason: Token-based server access validation failed with an infrastructure error. Check for previous errors.

    Error: 18456, Severity: 14, State: 11.

    Trials:

    This problem was affecting members of just a particular AD group who had no issues connecting on other servers.

    Adding their individual windows logins also didnt solve the problem.

    My Solution:

    So after 2-3 days of hopping around blogs and solutions, it occurred to me that the database engine could recognise the users but just wouldnt grant them access the the SQL instance.

    This was a instance-level issue and not a database one.

    I ran the query:

    select name from syslogins where hasaccess =0

    and found that the BUILTINAdministrators has been denied access to the SQL instance, once I enabled this through SSMS(Security >> right click the login >> Properties  >>Status >> Settings >>Permission to connect to database engine), the affected logins were able to connect. 🙂

    I discovered the affected windows group is actually a memeber of the local Admin group on the box, hence BUILTINAdministrators.

    The key is to find out if the affected users belonged to other groups that could have been denied access to the instance.

  11. Kshama Shetti says:

    Hello Amit,
    I to am getting the same issue when the Active directory user ia dropped and recreated. What is the solution for it?

    Thank you,
    Kshama.

Skip to main content