February 26, 2013 at 11:10 am
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.
Date2/26/2013 11:15:10 AM
LogSQL Server (Current - 2/26/2013 11:17:00 AM)
SourceLogon
Message
Login failed for user 'xxxxx\eabrvintldev'. Reason: Failed to open the explicitly specified database. [CLIENT: 10.xxx.82.82]
February 26, 2013 at 8:14 pm
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
February 28, 2013 at 5:43 am
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?
February 28, 2013 at 9:16 am
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
February 28, 2013 at 9:27 am
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. :w00t:
February 28, 2013 at 9:35 am
hjhatl-911184 (2/28/2013)
stranger things have happened. :w00t:
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
March 1, 2013 at 6:48 am
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. 🙂
March 1, 2013 at 8:11 am
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
April 2, 2013 at 8:03 am
Thanks for the heads up on that one it was already on radar for change along with a few others. 🙂
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply