InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comFri, 24 May 2013 00:13:24 GMT20http://www.sqlservercentral.com/Forums/Topic1424189-146-1.aspxThanks for the heads up on that one it was already on radar for change along with a few others. :-)Tue, 02 Apr 2013 08:03:26 GMThjhatl-911184http://www.sqlservercentral.com/Forums/Topic1424189-146-1.aspxI 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. [u][url=http://msdn.microsoft.com/en-us/library/ms178013(v=sql.90).aspx]sp_grantdbaccess[/url][/u] 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.Fri, 01 Mar 2013 08:11:45 GMTopc.threehttp://www.sqlservercentral.com/Forums/Topic1424189-146-1.aspxI 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. :-)Fri, 01 Mar 2013 06:48:47 GMThjhatl-911184http://www.sqlservercentral.com/Forums/Topic1424189-146-1.aspx[quote][b]hjhatl-911184 (2/28/2013)[/b][hr]stranger things have happened. :w00t:[/quote]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.Thu, 28 Feb 2013 09:35:59 GMTopc.threehttp://www.sqlservercentral.com/Forums/Topic1424189-146-1.aspxGood 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:Thu, 28 Feb 2013 09:27:10 GMThjhatl-911184http://www.sqlservercentral.com/Forums/Topic1424189-146-1.aspxIt'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.Thu, 28 Feb 2013 09:16:29 GMTopc.threehttp://www.sqlservercentral.com/Forums/Topic1424189-146-1.aspxYes, 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?Thu, 28 Feb 2013 05:42:59 GMThjhatl-911184http://www.sqlservercentral.com/Forums/Topic1424189-146-1.aspxIt 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?Tue, 26 Feb 2013 20:14:21 GMTopc.threehttp://www.sqlservercentral.com/Forums/Topic1424189-146-1.aspxI 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 AMLog SQL Server (Current - 2/26/2013 11:17:00 AM)Source LogonMessageLogin failed for user 'xxxxx\eabrvintldev'. Reason: Failed to open the explicitly specified database. [CLIENT: 10.xxx.82.82]Tue, 26 Feb 2013 11:10:01 GMThjhatl-911184