Exactly what steps are necessary to configure SQL Server 6.5 access to any user group other than Administrator.
As an Administrator, or one having administrator privileges, I have no problem accessing a database as long as I'm logged on to the NT server as Administrator. However, if I log on to the same machine using a domain other than the local machine, I can't access the database when I run applications against the database, such as a report tool.
Access errors will appear related to 'owner dbo' or errors related to column names of the database tables such as 'column name does not exist.' I'm sure both these errors are related to access privileges.
Here is what I have done:
From within NT's 'Administrative Tools', and the 'User Manager For Domains' menu item, I've ensured that I'm listed as a 'Power User' and 'Backup Operator.' I'm fairly certain that if I list myself as an Administrator for all domains of the local machine, then I will have no problem accessing. However, I want to configure myself as a Power User or Backup User to test the operation of applications running against the DB. These applications will be run by non administrators eventually.
From within the SQL Server Setup I've chosen 'Windows NT Integrated' as the 'Login Security Mode'. I've done this because within the ODBC data source(used by all applications) I've also chosen the login procedure as 'With Windows NT authentication using the network login ID' rather than using 'With SQL Server authentication using a login ID and password entered by the user'
Since I've chosen the former method the Help documents explain that the Security Mode of SQL must be set up as 'Windows NT Integrated'
Within the Security Manager I've granted the appropriate permissions and can see that I do appear in both categories mentioned. Also, the appropriate database has been assigned to me for both user types.
Within Enterprise Manager, I've edited the server to use a 'Trusted Connection'. Also, from the 'Manage Logins' menu item, I've ensured that my name appears as one of the 'Login Names' and once chosen, my name appears in all databases listed.
Even though I've done all this, I'm still getting the errors mentioned. What else is there?
Also, related to this question is my concern for allowing regular 'Users' access to the database when running applications against the DB. I would have chosen 'Users' rather than Power User or Backup Operator but the list becomes too large! I may want a user in a particular domain to have access but how can I obtain that user alone instead of getting the entire list of users in the domain?? I realize that individual users can be given a login ID and password thus limitting access to the users I desire, but I still have all the other users slowing down the Security Manager everytime I use it to do a simple task. Therefore, I want to be able to grab 1 user at a time when granting privileges. I got around this by choosing Power User or Backup Operators. I have a feeling that I need to exlude all undesired users from within the 'User Manager For Domains' menu item. Would you agree?
Thanks for the help, I'll increase points if this drags out.