General discussion


SQL: Windows Authentication

By michaelh ·
What type of security level must a user have to gain access to a SQL database through Windows Authentication? It seems to only work for me if I assign a user Admin access, and I don't see any built in groups for SQL.

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Comments

Collapse -

by Joseph Moore In reply to SQL: Windows Authenticati ...

I take it you have SQL 2000.

There are now 2 kinds of authentication, Windows Authentication (which does default to Administrators only if you don't change it) and SQL Authentication, which is the old fashioned SQL method with local SQL accounts.

So, you need to check how your SQL Server is configured. Open Enterprise Manager, expand the groups until your SQL Server is listed, then right-click the Server and choose Properties.

Click the Security tab. In the top Security section, is it selected for Windows Only, or "SQL Server and Windows"? The default install method is Windows Authentication only. So, if you want to make local SQL accounts on the SQL server for SQL Authentication, make sure "SQL Server and Windows" is selected.

Collapse -

by Joseph Moore In reply to

OK, now let's make a local SQL account. Expand your SQL Server in Enterprise Manager. The folders expand out (Databases, Data Transfer, Mangement, etc.). Expand Security.
Right-click on Logins and choose New Login.

In the Name box, type the name of the Login you want to make (in my example, BOB).

In the Authentication section, select SQL Server Authentication, and type in the password.

In the Defaults section, select the database this login will default to (the one it will use the majority of the time).

If this is a user SQL account, then ignore the Server Roles tab; if BOB is gonna be an administrator on SQL Server, then you would click this tab and select the admin role.

Next, click the Database Access tab, and click the Permit column for the database BOB will use.

Then in the "Database Roles for 'database'" section, select the type of user role for BOB account. Typically, users are members of the Public role so they can read data in the db.

Click OK. You will verify the password and that is it.

Collapse -

by Joseph Moore In reply to

At this point, BOB can access that database.

hope this helps

Related Discussions

Related Forums