April 24, 2007 at 9:27 am
I am trying to give a user access to a new SQL Server 2000 database on a server that has 3 instances (servername, servername/GOLDSTD and servername/TST). No matter what I did (including giving him dbo rights to all databases on the server) he could not register the servername in SQL Server Enterprise Manager or access the database using an ODBC connection until I gave him sysadmin rights. Even though it is just a development server, I am not comfortable giving out sysadmin rights to all the people who needs to access the new database. Any suggestions?
April 24, 2007 at 9:32 am
Which instance was he trying to connect to? How did you grant him login rights to the SQL Server?
K. Brian Kelley
@kbriankelley
April 24, 2007 at 9:50 am
He was trying to connect to the default instance (servername only). I gave him permissions first through Enterprise Manager, then tried giving it using T-SQL and Query analyzer. Neither worked even though both times I could see his login in the database sysusers table.
April 24, 2007 at 10:11 am
And you could see his login in sysxlogins? If so, was he denied access for some reason, such as a particular Windows group he was a member of being denied access?
K. Brian Kelley
@kbriankelley
April 24, 2007 at 10:18 am
Yes, he was in sysxlogins too. I don't have access denied to any particular Windows group - I just grant access on this server to the individual developers based on the database they are using. Doesn't make sense but I had the same problem with a second developer. Again, when I grant sysadmin rights it works fine. I thought it might be because of the instances since this is the only server I have that uses named instances.
April 24, 2007 at 2:49 pm
It's not a named instance thing. Something else is going on. Anything in the server application, security, or system event logs which seem to correlate? What about the SQL Server log?
K. Brian Kelley
@kbriankelley
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply