My company is running a large Clipper application using Advantage Database
Server 6.2 as the backend. We have .DBF tables. We have 6 major retail
markets with over 200 tables in each folder (market).
I am looking for an easy (if that is possible) way to convert these .DBF
tables into SQL Server. In the query analyzer I have created 6 linked server
connections to each of the folders. I am able to do selects from any table
using the OpenQuery function. I have heard mention of DTS but I have no idea
what that is.
Can anyone suggest an easy way to convert these tables over? Also in some
cases its not going to be just a straight column to column data transfer. I
made need to combine data from 2 or 3 more tables on the Clipper side to mak
e
one column on the SQL Server side.
Any help would be appreciated.
David Cuffee
Sleep Train Inc.
Software DeveloperYes, you can use the DTS wizzard to import the dbf files without going
through Query Analyzer commands.
However, if you already are at the point of selecting from the tables
via OpenQuery, then you also have the option of selecting a rowset directly
into a SQL Server table using INSERT INTO. For example: "insert into
MySqlTable select a, b, c from MyDbfTable". If you know SQL, then this
method will make it easy to specify columns in proper order, combine,
transform, etc. as needed.
"David C via webservertalk.com" <forum@.webservertalk.com> wrote in message
news:5298807695596@.webservertalk.com...
> My company is running a large Clipper application using Advantage Database
> Server 6.2 as the backend. We have .DBF tables. We have 6 major retail
> markets with over 200 tables in each folder (market).
> I am looking for an easy (if that is possible) way to convert these .DBF
> tables into SQL Server. In the query analyzer I have created 6 linked
> server
> connections to each of the folders. I am able to do selects from any table
> using the OpenQuery function. I have heard mention of DTS but I have no
> idea
> what that is.
> Can anyone suggest an easy way to convert these tables over? Also in some
> cases its not going to be just a straight column to column data transfer.
> I
> made need to combine data from 2 or 3 more tables on the Clipper side to
> make
> one column on the SQL Server side.
> Any help would be appreciated.
> David Cuffee
> Sleep Train Inc.
> Software Developer|||Thank JT. My brain must have not been thinking. Using the INSERT method with
the SELECT is very good way to do this. now that I have the OpenQuery workin
g.
Thank you very much.
David
JT wrote:
> Yes, you can use the DTS wizzard to import the dbf files without going
>through Query Analyzer commands.
> However, if you already are at the point of selecting from the tables
>via OpenQuery, then you also have the option of selecting a rowset directly
>into a SQL Server table using INSERT INTO. For example: "insert into
>MySqlTable select a, b, c from MyDbfTable". If you know SQL, then this
>method will make it easy to specify columns in proper order, combine,
>transform, etc. as needed.
>
>[quoted text clipped - 20 lines]
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...amming/200508/1
Showing posts with label company. Show all posts
Showing posts with label company. Show all posts
Tuesday, March 27, 2012
Sunday, March 25, 2012
Converting alpha-numeric values
Hello, all. I have come across a dilemma working with SQL server 2005. My
company has a parts database. They want to be able to select a range of
parts based on the part number. Problem is that the part numbers are mixed
with non-numeric characters and special characters. Obviously convert will
not work in the scenario. An example part number:
123-304-10000A
Is there a way to get this included in a range search say from 100 to 200?
I wrote a proc that strips out the non-numeric characters but it is way too
slow with over 140000 parts in the table.
Any help would be appreciated.Here is one way to strip out the non-numeric characters and to filter on the
numeric part only. In production replace the reference to the system table
master..spt_values with real utility table with numbers
(http://www.projectdmx.com/tsql/tblnumbers.aspx).
CREATE TABLE Parts (
part_no VARCHAR(14) PRIMARY KEY);
INSERT INTO Parts VALUES ('123-304-10000A');
INSERT INTO Parts VALUES ('AAA-BBB-00100A');
INSERT INTO Parts VALUES ('AAA-CCC-00150A');
WITH CleanParts (part_no, num_part_no)
AS
(SELECT part_no, CAST(
(SELECT SUBSTRING(part_no, n, 1)
FROM (SELECT number
FROM master..spt_values
WHERE type = 'P'
AND number BETWEEN 1 AND 100) AS Nums(n)
WHERE n <= LEN(part_no)
AND SUBSTRING(part_no, n, 1) LIKE '[0-9]'
FOR XML PATH('')) AS BIGINT)
FROM Parts)
SELECT part_no
FROM CleanParts
WHERE num_part_no BETWEEN 100 AND 200;
HTH,
Plamen Ratchev
http://www.SQLStudio.com
company has a parts database. They want to be able to select a range of
parts based on the part number. Problem is that the part numbers are mixed
with non-numeric characters and special characters. Obviously convert will
not work in the scenario. An example part number:
123-304-10000A
Is there a way to get this included in a range search say from 100 to 200?
I wrote a proc that strips out the non-numeric characters but it is way too
slow with over 140000 parts in the table.
Any help would be appreciated.Here is one way to strip out the non-numeric characters and to filter on the
numeric part only. In production replace the reference to the system table
master..spt_values with real utility table with numbers
(http://www.projectdmx.com/tsql/tblnumbers.aspx).
CREATE TABLE Parts (
part_no VARCHAR(14) PRIMARY KEY);
INSERT INTO Parts VALUES ('123-304-10000A');
INSERT INTO Parts VALUES ('AAA-BBB-00100A');
INSERT INTO Parts VALUES ('AAA-CCC-00150A');
WITH CleanParts (part_no, num_part_no)
AS
(SELECT part_no, CAST(
(SELECT SUBSTRING(part_no, n, 1)
FROM (SELECT number
FROM master..spt_values
WHERE type = 'P'
AND number BETWEEN 1 AND 100) AS Nums(n)
WHERE n <= LEN(part_no)
AND SUBSTRING(part_no, n, 1) LIKE '[0-9]'
FOR XML PATH('')) AS BIGINT)
FROM Parts)
SELECT part_no
FROM CleanParts
WHERE num_part_no BETWEEN 100 AND 200;
HTH,
Plamen Ratchev
http://www.SQLStudio.com
Friday, February 24, 2012
Convert Microsoft Word XML to PDF without using Microsoft Word
Hi all,
I work for a financial company where we send out lots of correspondence to clients on a daily and monthly basis. This would typically be something like a financial report, account statements, etc. We've decided to use PDF as the format which these documents must be in when our clients receive them.
Our marketing and sales departments designs the templates of these documents using Microsoft Word. They can save these MS Word documents in the Word XML format (either 2003 or 2007). We want it in XML format, because it is text based, and we can therefore string replace the content to update the template with a client’s information.In the same token RTF would also do, but the file size when converted to RTF is a problem.
What I am looking for is a way to convert this XML into PDF using something like XSL-FO - i.e. we do not want to use the Word Interopt on our Production Server, firstly it is a little slow and secondly the production server is unmanned and we cannot allow a WinWord.EXE process to not close successfully for some or other reason.
I've tried a number of companies' evaluation software to try and achieve this (converting the WordML to PDF via XSL-FO), but so far no luck.
Does anybody know of a stylesheet out there with which I will be able to achieve this? Or an alternative method of converting MS Word files into PDF without using Word to do it?
Thank you in advance.
Regards
This really isn't the right forum for this question. You might consider one of the Office forums located here.
Cheers,
-Isaac
Tuesday, February 14, 2012
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:
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:
>
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
>> >
>> >
>> >
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
>> >
>> >
>> >
Subscribe to:
Posts (Atom)