Convert SQL logins to Windows authentication

I was wondering if anyone knew if it's possible to easily convert existing
SQL Server logins and passwords to Windows Authentication logins and
passwords for a SQL Server 2000 database? If so, does anyone know of a
script or utility that does this?Hi,
ASAIK, there is no automated way to convert SQL logins to Windows users.
You need to receate the Windows Logins manually in OS side (In the domain
level) and then you
need to give access to that use in sql 2000 using sp_grantlogin [@.logina
me
=] 'login'.
After this you need to give exact precilages to this user in database level
as well as server level.
Thanks
Hari
SQL Server MVP
"Noone" <IDontLikeSpam@.nowhere.com> wrote in message
news:%23uC1fDLmFHA.576@.TK2MSFTNGP15.phx.gbl...
>I was wondering if anyone knew if it's possible to easily convert existing
>SQL Server logins and passwords to Windows Authentication logins and
>passwords for a SQL Server 2000 database? If so, does anyone know of a
>script or utility that does this?
>|||I didn't think there was one either for SQL Server 2000. Do you know if
there's going to be any new features/utilities that might be able to do this
in future SQL Servers? Thanks a bunch Hari for your post.
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:ucIE%23UPmFHA.572@.TK2MSFTNGP15.phx.gbl...
> Hi,
> ASAIK, there is no automated way to convert SQL logins to Windows users.
> You need to receate the Windows Logins manually in OS side (In the domain
> level) and then you
> need to give access to that use in sql 2000 using sp_grantlogin [@.logi
name
> =] 'login'.
> After this you need to give exact precilages to this user in database
> level as well as server level.
> Thanks
> Hari
> SQL Server MVP
>
> "Noone" <IDontLikeSpam@.nowhere.com> wrote in message
> news:%23uC1fDLmFHA.576@.TK2MSFTNGP15.phx.gbl...
>|||Hi,
I dont think so. Because this sort of Login upgrades will come very rarely.
Thanks
Hari
SQL Server MVP
"John" <director0512athotmaildotcom> wrote in message
news:OaJsHhRmFHA.1968@.TK2MSFTNGP14.phx.gbl...
>I didn't think there was one either for SQL Server 2000. Do you know if
>there's going to be any new features/utilities that might be able to do
>this in future SQL Servers? Thanks a bunch Hari for your post.
>
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:ucIE%23UPmFHA.572@.TK2MSFTNGP15.phx.gbl...
>|||Suppose I create the new Windows Authentication accounts for the existing
SQL accounts...is there a known script/utility that can transfer the SQL
login passwords to the new corresponding Windows Authentication passwords?
Just wondering since we have a bunch of users with SQL logins and that if I
created their Windows Authentication logins the passwords would have to
re-specified manually?
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:eH%23J%23vSmFHA.3648@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I dont think so. Because this sort of Login upgrades will come very
> rarely.
> Thanks
> Hari
> SQL Server MVP
> "John" <director0512athotmaildotcom> wrote in message
> news:OaJsHhRmFHA.1968@.TK2MSFTNGP14.phx.gbl...
>|||SQL Server doesn't store passwords for Windows authenticated accounts since
Windows takes care of verifying the account. All you need to do is grant
the existing Windows accounts access to SQL Server, grant database access
and add the account to roles:
EXEC sp_grantlogin 'MyDomain\SomeUser'
GO
USE MyDatabase
GO
EXEC sp_grantdbaccess 'MyDomain\SomeUser'
EXEC sp_addrolemember 'MyRole', 'MyDomain\SomeUser'
GO
Hope this helps.
Dan Guzman
SQL Server MVP
"Noone" <IDontLikeSpam@.nowhere.com> wrote in message
news:%23xAR2CamFHA.3568@.TK2MSFTNGP10.phx.gbl...
> Suppose I create the new Windows Authentication accounts for the existing
> SQL accounts...is there a known script/utility that can transfer the SQL
> login passwords to the new corresponding Windows Authentication passwords?
> Just wondering since we have a bunch of users with SQL logins and that if
> I created their Windows Authentication logins the passwords would have to
> re-specified manually?
>
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:eH%23J%23vSmFHA.3648@.TK2MSFTNGP10.phx.gbl...
>|||Thanks Dan. Much appreciated :o)
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:utDRMwhmFHA.516@.TK2MSFTNGP10.phx.gbl...
> SQL Server doesn't store passwords for Windows authenticated accounts
> since Windows takes care of verifying the account. All you need to do is
> grant the existing Windows accounts access to SQL Server, grant database
> access and add the account to roles:
> EXEC sp_grantlogin 'MyDomain\SomeUser'
> GO
> USE MyDatabase
> GO
> EXEC sp_grantdbaccess 'MyDomain\SomeUser'
> EXEC sp_addrolemember 'MyRole', 'MyDomain\SomeUser'
> GO
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Noone" <IDontLikeSpam@.nowhere.com> wrote in message
> news:%23xAR2CamFHA.3568@.TK2MSFTNGP10.phx.gbl...
>

Convert from logins from SQL to Windows authentication

My company is converting from Novell to Windows (Active Directory)
environment. One of the SQL 200 server has more than 550 logins/users and
they are using SQL authentication.
What is the quickest way to convert all of them to Windows authentication
and keeping them to have same permission to databases & database roles (e.g
script etc). Manually recreate them is a big task.
Regards,
Johnny
Anyone can give me some advices please ?
"Johnny" wrote:

> My company is converting from Novell to Windows (Active Directory)
> environment. One of the SQL 200 server has more than 550 logins/users and
> they are using SQL authentication.
> What is the quickest way to convert all of them to Windows authentication
> and keeping them to have same permission to databases & database roles (e.g
> script etc). Manually recreate them is a big task.
> Regards,
> Johnny
|||Since you cannot convert a login, you have to add a new, re-map the users to the new login, then
drop the old login. Check out sp_change_users_logins for re-mapping users.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Johnny" <Johnny@.discussions.microsoft.com> wrote in message
news:DBBAA319-8FCA-484B-8706-4B857BD6FE8C@.microsoft.com...[vbcol=seagreen]
> Anyone can give me some advices please ?
> "Johnny" wrote:
|||sp_change_users_login cannot be used with Microsoft Windows NT? users. I
cannot remap the database users to the new windows logins.
e.g sp_change_users_login update_one,'user1','domain\users1'
But if I drop the database user before adding the user to the windows login,
the user will lose all the roles and permissions. Even the scripting is not
very useful because it isn't grouped by users.
Help please.
Johnny
"Tibor Karaszi" wrote:

> Since you cannot convert a login, you have to add a new, re-map the users to the new login, then
> drop the old login. Check out sp_change_users_logins for re-mapping users.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Johnny" <Johnny@.discussions.microsoft.com> wrote in message
> news:DBBAA319-8FCA-484B-8706-4B857BD6FE8C@.microsoft.com...
>
>
|||Can anyone out there give me more suggestions ?
Regards,
Johnny
"Johnny" wrote:
[vbcol=seagreen]
> sp_change_users_login cannot be used with Microsoft Windows NT? users. I
> cannot remap the database users to the new windows logins.
> e.g sp_change_users_login update_one,'user1','domain\users1'
> But if I drop the database user before adding the user to the windows login,
> the user will lose all the roles and permissions. Even the scripting is not
> very useful because it isn't grouped by users.
> Help please.
> Johnny
>
> "Tibor Karaszi" wrote:
|||Take a look at sp_help_revlogin.
http://support.microsoft.com/kb/246133
-oj
"Johnny" <Johnny@.discussions.microsoft.com> wrote in message
news:85513978-7C52-4AC0-8A12-CB9A9636E903@.microsoft.com...[vbcol=seagreen]
> Can anyone out there give me more suggestions ?
> Regards,
> Johnny
> "Johnny" wrote:

Convert from logins from SQL to Windows authentication

My company is converting from Novell to Windows (Active Directory)
environment. One of the SQL 200 server has more than 550 logins/users and
they are using SQL authentication.
What is the quickest way to convert all of them to Windows authentication
and keeping them to have same permission to databases & database roles (e.g
script etc). Manually recreate them is a big task.
Regards,
JohnnyAnyone can give me some advices please ?
"Johnny" wrote:

> My company is converting from Novell to Windows (Active Directory)
> environment. One of the SQL 200 server has more than 550 logins/users an
d
> they are using SQL authentication.
> What is the quickest way to convert all of them to Windows authentication
> and keeping them to have same permission to databases & database roles (e.
g
> script etc). Manually recreate them is a big task.
> Regards,
> Johnny|||Since you cannot convert a login, you have to add a new, re-map the users to
the new login, then
drop the old login. Check out sp_change_users_logins for re-mapping users.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Johnny" <Johnny@.discussions.microsoft.com> wrote in message
news:DBBAA319-8FCA-484B-8706-4B857BD6FE8C@.microsoft.com...[vbcol=seagreen]
> Anyone can give me some advices please ?
> "Johnny" wrote:
>|||sp_change_users_login cannot be used with Microsoft Windows NT? users. I
cannot remap the database users to the new windows logins.
e.g sp_change_users_login update_one,'user1','domain\users1'
But if I drop the database user before adding the user to the windows login,
the user will lose all the roles and permissions. Even the scripting is not
very useful because it isn't grouped by users.
Help please.
Johnny
"Tibor Karaszi" wrote:

> Since you cannot convert a login, you have to add a new, re-map the users
to the new login, then
> drop the old login. Check out sp_change_users_logins for re-mapping users.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Johnny" <Johnny@.discussions.microsoft.com> wrote in message
> news:DBBAA319-8FCA-484B-8706-4B857BD6FE8C@.microsoft.com...
>
>|||Can anyone out there give me more suggestions ?
Regards,
Johnny
"Johnny" wrote:
[vbcol=seagreen]
> sp_change_users_login cannot be used with Microsoft Windows NT? users. I
> cannot remap the database users to the new windows logins.
> e.g sp_change_users_login update_one,'user1','domain\users1'
> But if I drop the database user before adding the user to the windows logi
n,
> the user will lose all the roles and permissions. Even the scripting is n
ot
> very useful because it isn't grouped by users.
> Help please.
> Johnny
>
> "Tibor Karaszi" wrote:
>|||Take a look at sp_help_revlogin.
http://support.microsoft.com/kb/246133
-oj
"Johnny" <Johnny@.discussions.microsoft.com> wrote in message
news:85513978-7C52-4AC0-8A12-CB9A9636E903@.microsoft.com...[vbcol=seagreen]
> Can anyone out there give me more suggestions ?
> Regards,
> Johnny
> "Johnny" wrote:
>

Convert from logins from SQL to Windows authentication

My company is converting from Novell to Windows (Active Directory)
environment. One of the SQL 200 server has more than 550 logins/users and
they are using SQL authentication.
What is the quickest way to convert all of them to Windows authentication
and keeping them to have same permission to databases & database roles (e.g
script etc). Manually recreate them is a big task.
Regards,
JohnnyAnyone can give me some advices please ?
"Johnny" wrote:
> My company is converting from Novell to Windows (Active Directory)
> environment. One of the SQL 200 server has more than 550 logins/users and
> they are using SQL authentication.
> What is the quickest way to convert all of them to Windows authentication
> and keeping them to have same permission to databases & database roles (e.g
> script etc). Manually recreate them is a big task.
> Regards,
> Johnny|||Since you cannot convert a login, you have to add a new, re-map the users to the new login, then
drop the old login. Check out sp_change_users_logins for re-mapping users.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Johnny" <Johnny@.discussions.microsoft.com> wrote in message
news:DBBAA319-8FCA-484B-8706-4B857BD6FE8C@.microsoft.com...
> Anyone can give me some advices please ?
> "Johnny" wrote:
>> My company is converting from Novell to Windows (Active Directory)
>> environment. One of the SQL 200 server has more than 550 logins/users and
>> they are using SQL authentication.
>> What is the quickest way to convert all of them to Windows authentication
>> and keeping them to have same permission to databases & database roles (e.g
>> script etc). Manually recreate them is a big task.
>> Regards,
>> Johnny|||sp_change_users_login cannot be used with Microsoft Windows NT® users. I
cannot remap the database users to the new windows logins.
e.g sp_change_users_login update_one,'user1','domain\users1'
But if I drop the database user before adding the user to the windows login,
the user will lose all the roles and permissions. Even the scripting is not
very useful because it isn't grouped by users.
Help please.
Johnny
"Tibor Karaszi" wrote:
> Since you cannot convert a login, you have to add a new, re-map the users to the new login, then
> drop the old login. Check out sp_change_users_logins for re-mapping users.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Johnny" <Johnny@.discussions.microsoft.com> wrote in message
> news:DBBAA319-8FCA-484B-8706-4B857BD6FE8C@.microsoft.com...
> > Anyone can give me some advices please ?
> >
> > "Johnny" wrote:
> >
> >> My company is converting from Novell to Windows (Active Directory)
> >> environment. One of the SQL 200 server has more than 550 logins/users and
> >> they are using SQL authentication.
> >>
> >> What is the quickest way to convert all of them to Windows authentication
> >> and keeping them to have same permission to databases & database roles (e.g
> >> script etc). Manually recreate them is a big task.
> >>
> >> Regards,
> >> Johnny
>
>|||Can anyone out there give me more suggestions ?
Regards,
Johnny
"Johnny" wrote:
> sp_change_users_login cannot be used with Microsoft Windows NT® users. I
> cannot remap the database users to the new windows logins.
> e.g sp_change_users_login update_one,'user1','domain\users1'
> But if I drop the database user before adding the user to the windows login,
> the user will lose all the roles and permissions. Even the scripting is not
> very useful because it isn't grouped by users.
> Help please.
> Johnny
>
> "Tibor Karaszi" wrote:
> > Since you cannot convert a login, you have to add a new, re-map the users to the new login, then
> > drop the old login. Check out sp_change_users_logins for re-mapping users.
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://www.solidqualitylearning.com/
> >
> >
> > "Johnny" <Johnny@.discussions.microsoft.com> wrote in message
> > news:DBBAA319-8FCA-484B-8706-4B857BD6FE8C@.microsoft.com...
> > > Anyone can give me some advices please ?
> > >
> > > "Johnny" wrote:
> > >
> > >> My company is converting from Novell to Windows (Active Directory)
> > >> environment. One of the SQL 200 server has more than 550 logins/users and
> > >> they are using SQL authentication.
> > >>
> > >> What is the quickest way to convert all of them to Windows authentication
> > >> and keeping them to have same permission to databases & database roles (e.g
> > >> script etc). Manually recreate them is a big task.
> > >>
> > >> Regards,
> > >> Johnny
> >
> >
> >|||Take a look at sp_help_revlogin.
http://support.microsoft.com/kb/246133
-oj
"Johnny" <Johnny@.discussions.microsoft.com> wrote in message
news:85513978-7C52-4AC0-8A12-CB9A9636E903@.microsoft.com...
> Can anyone out there give me more suggestions ?
> Regards,
> Johnny
> "Johnny" wrote:
>> sp_change_users_login cannot be used with Microsoft Windows NT® users. I
>> cannot remap the database users to the new windows logins.
>> e.g sp_change_users_login update_one,'user1','domain\users1'
>> But if I drop the database user before adding the user to the windows
>> login,
>> the user will lose all the roles and permissions. Even the scripting is
>> not
>> very useful because it isn't grouped by users.
>> Help please.
>> Johnny
>>
>> "Tibor Karaszi" wrote:
>> > Since you cannot convert a login, you have to add a new, re-map the
>> > users to the new login, then
>> > drop the old login. Check out sp_change_users_logins for re-mapping
>> > users.
>> >
>> > --
>> > Tibor Karaszi, SQL Server MVP
>> > http://www.karaszi.com/sqlserver/default.asp
>> > http://www.solidqualitylearning.com/
>> >
>> >
>> > "Johnny" <Johnny@.discussions.microsoft.com> wrote in message
>> > news:DBBAA319-8FCA-484B-8706-4B857BD6FE8C@.microsoft.com...
>> > > Anyone can give me some advices please ?
>> > >
>> > > "Johnny" wrote:
>> > >
>> > >> My company is converting from Novell to Windows (Active Directory)
>> > >> environment. One of the SQL 200 server has more than 550
>> > >> logins/users and
>> > >> they are using SQL authentication.
>> > >>
>> > >> What is the quickest way to convert all of them to Windows
>> > >> authentication
>> > >> and keeping them to have same permission to databases & database
>> > >> roles (e.g
>> > >> script etc). Manually recreate them is a big task.
>> > >>
>> > >> Regards,
>> > >> Johnny
>> >
>> >
>> >