Thursday, March 8, 2012
Convert SQL logins to Windows authentication
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 SQL Account to Windows Accounts + EXEC on ALL SPs
I have an SQL Server 2000 Database that has an "SQL Account" that has
execute permission on all Stored procedures. it is what was used by
the company. This one account is used by "all workstations".
I want to fix this and use Windows Accounts, and get rid of that SQL
Account. How do I go about adding that Windows Account permission to
all the Stored Procedures?
What I want to do is to just add several windows account then go about
removing the permission where necessary on an account by account
basis.
Any suggestions would be greatly appreciated!DaBrain (Tapplication@.gmail.com) writes:
Quote:
Originally Posted by
I am no DBA, but this is my task.
>
I have an SQL Server 2000 Database that has an "SQL Account" that has
execute permission on all Stored procedures. it is what was used by
the company. This one account is used by "all workstations".
>
I want to fix this and use Windows Accounts, and get rid of that SQL
Account. How do I go about adding that Windows Account permission to
all the Stored Procedures?
>
What I want to do is to just add several windows account then go about
removing the permission where necessary on an account by account
basis.
First thing is of course to grant access to the Windows accounts. This
can be per account, or by granting access to Windows groups. The latter
is more convenient, since it will catch all new accounts - provided that
they should have access of course!
Whatever, I recommend that you create a role, and then add all Windows
logins to that role:
exec sp_addrole 'ourrole'
exec sp_addrolemember 'ourrole', 'DOMAIN\Group'
(I may have misremembered the order of the paramerers to sp_addrolemember.)
Then you can grant access to the procedures to the role:
SELECT 'GRANT EXEC ON ' + quotename(name) + ' TO ourrole'
FROM sysobjects
WHERE xtype = 'P'
As new procedures you would have to grant acess to these as well. There
is unfortunately no way in SQL 2000 to grant exec rights in advance,
so to speak. (It is possiuble in SQL 2005, where you can grant EXEC on
schema level.)
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Hi
DaBrain, will also have to configure all the client applications to use
Windows authentication instead of SQL Server Authentication. (or instruct
the users as to how to do it).
Depending on the number of users, the application they are using, this might
be a time-c0nsuming task.
If they connect through Access XP ADPs this is a relatively simple task.
If they connect using a DSN it will be slightly more difficult.
If they connect through some custom application where the connection info is
stored in a .INI file or the registry and the developers didn't plan on
connecting with Windows authentication this might be somewhere between
difficult and impossible.
--
-Dick Christoph
"Erland Sommarskog" <esquel@.sommarskog.sewrote in message
news:Xns990D92FA5FA6Yazorman@.127.0.0.1...
Quote:
Originally Posted by
DaBrain (Tapplication@.gmail.com) writes:
Quote:
Originally Posted by
>I am no DBA, but this is my task.
>>
>I have an SQL Server 2000 Database that has an "SQL Account" that has
>execute permission on all Stored procedures. it is what was used by
>the company. This one account is used by "all workstations".
>>
>I want to fix this and use Windows Accounts, and get rid of that SQL
>Account. How do I go about adding that Windows Account permission to
>all the Stored Procedures?
>>
>What I want to do is to just add several windows account then go about
>removing the permission where necessary on an account by account
>basis.
>
First thing is of course to grant access to the Windows accounts. This
can be per account, or by granting access to Windows groups. The latter
is more convenient, since it will catch all new accounts - provided that
they should have access of course!
>
Whatever, I recommend that you create a role, and then add all Windows
logins to that role:
>
exec sp_addrole 'ourrole'
exec sp_addrolemember 'ourrole', 'DOMAIN\Group'
>
(I may have misremembered the order of the paramerers to
sp_addrolemember.)
>
Then you can grant access to the procedures to the role:
>
SELECT 'GRANT EXEC ON ' + quotename(name) + ' TO ourrole'
FROM sysobjects
WHERE xtype = 'P'
>
As new procedures you would have to grant acess to these as well. There
is unfortunately no way in SQL 2000 to grant exec rights in advance,
so to speak. (It is possiuble in SQL 2005, where you can grant EXEC on
schema level.)
>
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx
Tuesday, February 14, 2012
Convert from logins from SQL to Windows authentication
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
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
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
>> >
>> >
>> >
Sunday, February 12, 2012
convert db2 timestamp to sql server datetime
I am moving data from db2 8.1 for windows. the dates in db2 are defined as timestamp. i want to convert these to sql server datetime format in sql server 2000 using dts and sql.
does anyone have examples or something??
any help would be greatly appreciated.Look here
http://weblogs.sqlteam.com/brettk/archive/2005/06/02/5528.aspx