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: Thursday, September 25, 2014 2:00 PM
Points: 5, Visits: 63
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: 2 days ago @ 1:25 AM
Points: 7,141, Visits: 12,767
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
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: Thursday, September 25, 2014 2:00 PM
Points: 5, Visits: 63
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: 2 days ago @ 1:25 AM
Points: 7,141, Visits: 12,767
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
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: Thursday, September 25, 2014 2:00 PM
Points: 5, Visits: 63
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: 2 days ago @ 1:25 AM
Points: 7,141, Visits: 12,767
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
Post #1425125
Posted Friday, March 1, 2013 6:48 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, September 25, 2014 2:00 PM
Points: 5, Visits: 63
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 1, 2013 8:11 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 1:25 AM
Points: 7,141, Visits: 12,767
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
Post #1425533
Posted Tuesday, April 2, 2013 8:03 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, September 25, 2014 2:00 PM
Points: 5, Visits: 63
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