Hi Greg/Grant,
The issue isn't whether or not the service account has access to the files, it's whether or not it's been granted permission to access the server and the databases. Open SSMS and make sure it's configured as a Login and also that the Login has access to the database. Regards, Greg From: [email protected] [mailto:[email protected]] On Behalf Of Grant Castner Sent: Wednesday, 23 March 2011 11:33 AM To: ozDotNet Subject: Re: [OT] SQL Server Windows Authentication Hi Greg, Its been a while since I had this problem but when you moved the databases to the new server, did it create a whole bunch of additional Schemas under Security for each database? I've seen it create new schemas for each user (especially Windows users). Deleting those additional schemas fixed the problem. Kind regards, Grant On 22 March 2011 16:34, Greg Keogh <[email protected]> wrote: Folks, this morning I was forced to perform a big switch-over to a pair of new Win2008 R2 domain controller and file/web/sql server machines. I have found that SQL Server Management Studio on the workstation machines does not have permission to access the databases when I use Windows Authentication. If I use 'sa' then there is no problem. If I try to open a database node in Object Explorer I get: The database XXXXX is not accessible. (ObjectExplorer) Procmon on the server shows me that NETWORK SERVICE does not have read permission on the LDF and MDF files, but If I give it full control, it has no effect. If I add Authenticated Users it fixes the procmon warnings but has no effect upon the problem. I'm unable to find a point of blame for this problem. I don't know if it's the NTFS permissions, some security or roles in SQL Server, or some domain user/group problem. Any ideas anyone? I'm stuffed. Greg -- Castner IT Pty Ltd Dr Grant Castner Director [email protected] www.castnerit.com.au 0458770749 Level 22 Northbank Plaza, 69 Ann Street, Brisbane QLD 4000 Twitter: http://twitter.com/castnerit Facebook: facebook/castnerit.com <http://www.facebook.com/pages/Brisbane-Australia/Castner-IT/291936316886>
