WIN2000 users profile into SQL2000 server

[suzan]

I want to apply a windows authentication to get users connect to the SQL2000 server under WIN2000 server.

Is there a way to copy win2000 user profile into sql2000 table instead of defining 120 users manually.

Obviously I need to go through each user to give it a certain authority but at least an effort will be saved to enter all other information such as userid,username etc.

thanks in advance

 

[RickCole]

You can't do what your suggesting in SQL Server 7.0 and I would assume this would be the same in 2000 although I'm not sure on that one. SQL server will quite happily take NT global groups. The best way to approach this is to put your users into required NT groups based on their SQL server authority and then just add the groups to SQL server.

Rick.

 

[suzan]

Finally I found such a way to import WIN2000 users into SQL2000 by doing these steps:
- Export win2000 users profile into text or excel file
- Import this file into sql2000 table using DTS import.
- Write a smal stored procedure to read from this table and add users into sql2000 security table using :

sp_addlogin [ @loginame = ] 'login'
[ , [ @passwd = ] 'password' ]
[ , [ @defdb = ] 'database' ]
[ , [ @deflanguage = ] 'language' ]
[ , [ @sid = ] sid ]
[ , [ @encryptopt = ] 'encryption_option' ]

this is helpfull when you apply a windows authentication and you have a lot of users.

 

[tlbroadbent]

Suzan,

That's a nice solution but it would create a maintenace nightmare for an organization like ours with over 2000 users. We prefer Ricks's suggestion to use NT groups rather than individual logins whenever possible.

I recommend that you join Tek-Tips. There are a number of benefits. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.