Granting Privileges

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.
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

You might try giving these users a dbo alias.
I don't know exactly what permissions you gave the group but make sure you have.

a. added the NT Useraccount as a login account to the SQL Server

b. added the login account to the database and have given it appropriate permissions.

By default logins are only granted public access which exludes all User databases. Giving them dbo alias does give them appropriate priviledge but also gives them database owner rights. You might want to custom set the rights on the individual objects in the database.
For any user with rights to edit the schema of the database you want to alias them as dbo so you are the real owner of the objects they creat.
Another alternative to dbo alias is to create a login that has rights to the database.  Then use that alias.  You would have to drop and recreate all the objects with a new ownership by this new user.  That way you can use this user to create any objects that you want to give access to for the two users you mentioned.  Not the best way to do it, but it works.
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

If you want to grant permissions to another user than sa, you can do the following:
- using the Enterprise Manager select the table
- right click and select  properties
- click the button permission
- grant permissions to your user

This may solve your problem. If the user you want give permissions don't appear you need to include him as a user of the database in the user configuration ( database tree ).
For using a trusted connection, there is no need to add the domain users in any of the NT groups. All you have to do is the following:
1. Make sure that the SQL Server and the users are in the same domain. If they are in different domains then they should have a trust relationship between these 2 domains.
2. Make sure you are logged in as sa in order to create SQL Server logins.
3. Then from Enterprise Manager, go to the Security folder and do a "Create new login". In the dialog box, type the exact NT username for the user you want to create the login. Then choose "Windows Nt authnetication" and type in the domain name.
4.Go to the "Database access" tab and give appropriate permission and access to that user.(It is advised that you create a new role in the user DB and give that role appropriate permission. Then while assigning DB permission to any user, you can associate that user to the newly created role.)

There is no necessity to give NT level permission to any user in the Database server. This will lead to security breaches also. The above method hould work when the user tried to connect to SQL Server using Trusted connection.
John500Author Commented:

Can you clarify where the 'security' folder is exactly?  Overall I like the sounds of your method, but I need to know what you mean by the security folder.

Thanks to all!
Sorry John500 I was refering to SQL Server 7.0. I did not realise that you were refering to 6.5. In 6.5, I had created a group called SQL_Users in NT User Manager domain. From the Security manager, I gave permission to this group to access SQL Server through trusted connection, and then associated this user to a particular group with proper privileges and it is working fine without errors. Sorry for misleading you.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
John500Author Commented:

Thanks for the help on this.  Can you please clarify a couple more things:

When you say:
"From the Security manager, I gave permission to this group to access SQL Server through trusted connection"

Once I create a group from within the 'NT User Manager For Domains' menu, how can I get that group to appear in the SQL Security Manager list when I attempt to 'Grant New'.  The only groups visible are:

Backup Operators
Power Users

I created a local group but it doesn't appear even when the 'Show:  Local Groups' option is selected.

When you say:
"then associated this user to a particular group with proper privileges"

It sounds as if within the Enterprise Manager you created another group.  Is this correct?  At any rate, can you clarify how I can limit certain users from the 'local group' to only run applications against the database rather than give them access to change, delete...?

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.