sql server (alternate): Hello,

We have a SQL 2000 installation (running on NT4 SP6a) with a database
accessed by a client application. Currently the application logs into the
database using a the SA account. I'd like to move the client access over to
Window Authentication. Each user has their own NT account. What steps do I
need to take to achieve this?

Any help would be greatly appreciated.

James G.

Below are the basic steps you'll need to perform in order to implement
Windows authentication. This assumes you plan to use role-based security in
order to simplify security administration tasks.

1) Add user database roles:

USE MyDatabase
sp_addrole 'PowerUsers'
sp_addrole 'RegularUsers'

2) Grant object permissions:

USE MyDatabase
GRANT ALL ON MyTable TO PowerUsers
GRANT SELECT ON MyTable TO RegularUsers

3) Grant Windows accounts access to SQL Server

EXEC sp_grantlogin 'MyDomain\Account1'
EXEC sp_grantlogin 'MyDomain\Account2'

4) Add these accounts to needed database(s)

USE MyDatabase
EXEC sp_grantdbaccess 'PowerUsers'
EXEC sp_grantdbaccess 'RegularUsers'

5) Add users to roles

EXEC sp_addrolemember 'PowerUsers', 'MyDomain\Account1'
EXEC sp_addrolemember 'RegularUsers', 'MyDomain\Account2'


--
Hope this helps.

Dan Guzman
SQL Server MVP

[quoted text, click to view]