Thursday, March 8, 2012

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...
>

No comments:

Post a Comment