Cannot connect to SQL Server '05 except as sysadmin role
Posted on 2009-05-07
I'm connecting to an instance of SQL Server 2005 running on Windows Server 2003. I need to create a general-purpose login that can access all the tables in a target db except for one (everything read-only). I have the server set on mixed mode authentication, and I'm using SQL Server authentication for the login.
The error code I generally get is due to the login existing but access to the database not being allowed (code 12). When I access from Crystal Reports 2008, I only get that error message. When I connect to the server itself using third-party administration/dev apps, I can sometimes connect correctly, but can't read table data. All these problems are solved when I set my general access login (let's call it "foo") to the sysadmin role. This allows foo to connect as if it were the Sa account, and everything runs smoothly. The problem is that I can't use this solution as I have to have a very limited, read-only account to release to a broad range of users.
Multiple connections aren't the issue since I've been connected under Sa and foo on my workstation while a colleague was connected to Sa on his.
I've spent quite some time adjusting settings in Management Studio Express and a third-party administrative program as well as exhausting all ideas on Google using the error codes I got, but I'm not an inch closer to solving this.