SA to Windows Authentication

[James Goodwill]

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.

[Dan Guzman]

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

"James Goodwill" <ja************@virgin.net> wrote in message
news:pf************@newsfe3-gui.ntli.net...

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.