Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Login failure Expand / Collapse
Author
Message
Posted Tuesday, February 26, 2013 11:10 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, March 18, 2014 9:46 AM
Points: 5, Visits: 48
I had a user state they could not log into a specific database with an NT login and the SS log showed this to be true, as shown below. I could see the login had the correct database security rights but it did not have access rights to the database itself. I tried doing an sp_grantdbaccess via SSMS and it accepted the command but didn't give the access rights. I then manually opened security in SSMS and checked the box to give the access rights for the database and it gave no error - just didn't apply the access. I checked with another DBA since I had not seen this and he told me to delete the database login via SSMS and then go to the security section and from there give the login access to the database. It worked as he said but, he couldn't tell me why... can anyone out there enlighten me?

Thanks in advance.

Date 2/26/2013 11:15:10 AM
Log SQL Server (Current - 2/26/2013 11:17:00 AM)

Source Logon

Message
Login failed for user 'xxxxx\eabrvintldev'. Reason: Failed to open the explicitly specified database. [CLIENT: 10.xxx.82.82]
Post #1424189
Posted Tuesday, February 26, 2013 8:14 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 9:25 AM
Points: 7,070, Visits: 12,523
It sounds related to a mismatched SID between the Server Login and Database User. By any chance was the database in question restored to this server from a backup taken from a different server?

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy
Post #1424305
Posted Thursday, February 28, 2013 5:42 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, March 18, 2014 9:46 AM
Points: 5, Visits: 48
Yes, this is a dev database that had recently been refreshed from prod - 2 entirely different servers. I routinely synch the SID's for native SS logins but, I was under the impression that was not necessary for NT logins?
Post #1425006
Posted Thursday, February 28, 2013 9:16 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 9:25 AM
Points: 7,070, Visits: 12,523
It's not required for NT logins provided they are on the same domain. The SID in that case comes from AD which is not going to change from one SQL instance to another.

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy
Post #1425104
Posted Thursday, February 28, 2013 9:27 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, March 18, 2014 9:46 AM
Points: 5, Visits: 48
Good info re: AD, I wasn't aware of that item. This does give me something to check though, the next time I do a DB refresh I'll check the SID for this login... stranger things have happened.
Post #1425111
Posted Thursday, February 28, 2013 9:35 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 9:25 AM
Points: 7,070, Visits: 12,523
hjhatl-911184 (2/28/2013)
stranger things have happened.

Indeed they have You could always check the SID for the login on the source system and check it against your dev instance. You were only dropping and recreating the dev DB user after restore, correct, not the login?

If you have a moment down the line I would be interested to know what you find towards id'ing a root cause.


__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy
Post #1425125
Posted Friday, March 01, 2013 6:48 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, March 18, 2014 9:46 AM
Points: 5, Visits: 48
I have a clue... the login being referenced is a service account used by the application for connection purposes. The login id in question does not exist in the production database, a different name is used for security purposes. The DB refresh script has been used for a number of years but, recently changed and that's where the fun occurred. The grantdbaccess was commented out for some reason but, the addrolemember was still assigning security rights to the database. Therefore, the login had security rights on the database but, didn't have permission to access the database.

THAT WAS FUN!

Thank you for hanging in there with me - in the end it was still a learning exercise, which is always a good thing.
Post #1425479
Posted Friday, March 01, 2013 8:11 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 9:25 AM
Points: 7,070, Visits: 12,523
I am happy you got to the bottom of it. I try to call these out whenever I can, it's not a knock, just pointing out the preferred syntax for new development. sp_grantdbaccess was deprecated in SQL 2005 and will be removed from a future product so while you're in there consider changing those calls to use CREATE USER instead.

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy
Post #1425533
Posted Tuesday, April 02, 2013 8:03 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, March 18, 2014 9:46 AM
Points: 5, Visits: 48
Thanks for the heads up on that one it was already on radar for change along with a few others.
Post #1437906
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse