Need to Change Developers Acct to Windows Authentication

I have my SQL 7.0 server set for Mixed security. I see now (finally)
the advantages of having windows authentication security for windows
groups.
I do most of my developing in Access Projects which require a login of
some type. I have been using my SQL login to develop with SQL and
then when I give it to the user, I set the project to use Windows
authentication. I want to be able to have Windows authentication on
my domain account but the user name for my windows account is the same
as my SQL account and it won't let me change the existing account.
Now what happens is after I develop the product I have to have the
user login and go in and change to windows authentication. It won't
let me do it with my sql account.

I fear that if I delete my sql account and create a windows
authentication account all the views, sps, etc. that I have created
will still have the reference to the sql login and therefore not work.

Do I need to have a generic sql account for developing? One thought I
had was to create a new domain account so I can create that windows
login and at least get my most recent projects working like I want to?

Hope this makes sense to someone.
Thanks in advance for your time.
Sherry"sherkozmo" <skosmicki@.sfmc-gi.org> wrote in message
news:3ff08a65.0409280648.2d0c3d4f@.posting.google.c om...
>I have my SQL 7.0 server set for Mixed security. I see now (finally)
> the advantages of having windows authentication security for windows
> groups.
> I do most of my developing in Access Projects which require a login of
> some type. I have been using my SQL login to develop with SQL and
> then when I give it to the user, I set the project to use Windows
> authentication. I want to be able to have Windows authentication on
> my domain account but the user name for my windows account is the same
> as my SQL account and it won't let me change the existing account.
> Now what happens is after I develop the product I have to have the
> user login and go in and change to windows authentication. It won't
> let me do it with my sql account.

You might find this post from yesterday useful - it gives a short overview
of moving from SQL to Windows authentication:

http://groups.google.com/groups?hl=...s.prodi gy.com

One solution to your issue (if I understood correctly) is to connect as sa,
drop the SQL login, grant login to your Windows account, then logout and
connect with Windows authentication.

> I fear that if I delete my sql account and create a windows
> authentication account all the views, sps, etc. that I have created
> will still have the reference to the sql login and therefore not work.

If the objects are owned by dbo, then there should be no problem. If the
objects are owned by a SQL login, then you can always change the owner with
sp_changeobjectowner - the bext practice would be to use dbo for production
objects anyway.

> Do I need to have a generic sql account for developing? One thought I
> had was to create a new domain account so I can create that windows
> login and at least get my most recent projects working like I want to?

It is almost always best to avoid generic accounts - there's no way to know
who is really using it, so auditing and permissions are difficult to
implement properly. The best solution is probably roles eg. your Windows
account is in the db_owner role, but user accounts are in a role with more
limited permissions.

> Hope this makes sense to someone.
> Thanks in advance for your time.
> Sherry

You're welcome.

Simon

Need some workaround( security question)

We have SQL 2005.
Say our SQL servers are in Domain 'ABC' and our clients are in Domain 'XYZ'
where the domains dont trust one another.
Theres more details but in short,I want to add Windows accounts i.e.
(XYZ\User1,XYZ\User2,etc.) without being authenticated against the domain
controller 'ABC'. When I try to add these Windows logins, it fails since it
wants to authenticate against XYZ but theres no trust.
So how can I bypass the authentication and get those entries added to my SQL
Account? Any way to hack the system tables? If not, thats fine. If theres
anyway to get these entries in the equivalent logins/users tables in the
databases is what I want to accomplish.
Thanks
If I understand what you are asking for, you want to allow connections
to users who say they are in the XYZ domain without any authentication
at all.
As someone who has to rely on SQL Server security I hope there is no
way to do this.
The obvious alternative for users in a non-trusted domain is to use
SQL Server logins, which are domain independent. That requires
individual logins be created, and they have to connect excplicitly by
name with a password
Roy Harvey
Beacon Falls, CT
On Thu, 28 Jun 2007 21:35:33 -0700, "Hassan" <hassan@.hotmail.com>
wrote:

>We have SQL 2005.
>Say our SQL servers are in Domain 'ABC' and our clients are in Domain 'XYZ'
>where the domains dont trust one another.
>Theres more details but in short,I want to add Windows accounts i.e.
>(XYZ\User1,XYZ\User2,etc.) without being authenticated against the domain
>controller 'ABC'. When I try to add these Windows logins, it fails since it
>wants to authenticate against XYZ but theres no trust.
>So how can I bypass the authentication and get those entries added to my SQL
>Account? Any way to hack the system tables? If not, thats fine. If theres
>anyway to get these entries in the equivalent logins/users tables in the
>databases is what I want to accomplish.
>Thanks

Need some workaround( security question)

We have SQL 2005.
Say our SQL servers are in Domain 'ABC' and our clients are in Domain 'XYZ'
where the domains dont trust one another.
Theres more details but in short,I want to add Windows accounts i.e.
(XYZ\User1,XYZ\User2,etc.) without being authenticated against the domain
controller 'ABC'. When I try to add these Windows logins, it fails since it
wants to authenticate against XYZ but theres no trust.
So how can I bypass the authentication and get those entries added to my SQL
Account? Any way to hack the system tables? If not, thats fine. If theres
anyway to get these entries in the equivalent logins/users tables in the
databases is what I want to accomplish.
ThanksIf I understand what you are asking for, you want to allow connections
to users who say they are in the XYZ domain without any authentication
at all.
As someone who has to rely on SQL Server security I hope there is no
way to do this.
The obvious alternative for users in a non-trusted domain is to use
SQL Server logins, which are domain independent. That requires
individual logins be created, and they have to connect excplicitly by
name with a password
Roy Harvey
Beacon Falls, CT
On Thu, 28 Jun 2007 21:35:33 -0700, "Hassan" <hassan@.hotmail.com>
wrote:

>We have SQL 2005.
>Say our SQL servers are in Domain 'ABC' and our clients are in Domain 'XYZ'
>where the domains dont trust one another.
>Theres more details but in short,I want to add Windows accounts i.e.
>(XYZ\User1,XYZ\User2,etc.) without being authenticated against the domain
>controller 'ABC'. When I try to add these Windows logins, it fails since it
>wants to authenticate against XYZ but theres no trust.
>So how can I bypass the authentication and get those entries added to my SQ
L
>Account? Any way to hack the system tables? If not, thats fine. If theres
>anyway to get these entries in the equivalent logins/users tables in the
>databases is what I want to accomplish.
>Thanks

Need some workaround( security question)

We have SQL 2005.
Say our SQL servers are in Domain 'ABC' and our clients are in Domain 'XYZ'
where the domains dont trust one another.
Theres more details but in short,I want to add Windows accounts i.e.
(XYZ\User1,XYZ\User2,etc.) without being authenticated against the domain
controller 'ABC'. When I try to add these Windows logins, it fails since it
wants to authenticate against XYZ but theres no trust.
So how can I bypass the authentication and get those entries added to my SQL
Account? Any way to hack the system tables? If not, thats fine. If theres
anyway to get these entries in the equivalent logins/users tables in the
databases is what I want to accomplish.
ThanksIf I understand what you are asking for, you want to allow connections
to users who say they are in the XYZ domain without any authentication
at all.
As someone who has to rely on SQL Server security I hope there is no
way to do this.
The obvious alternative for users in a non-trusted domain is to use
SQL Server logins, which are domain independent. That requires
individual logins be created, and they have to connect excplicitly by
name with a password
Roy Harvey
Beacon Falls, CT
On Thu, 28 Jun 2007 21:35:33 -0700, "Hassan" <hassan@.hotmail.com>
wrote:
>We have SQL 2005.
>Say our SQL servers are in Domain 'ABC' and our clients are in Domain 'XYZ'
>where the domains dont trust one another.
>Theres more details but in short,I want to add Windows accounts i.e.
>(XYZ\User1,XYZ\User2,etc.) without being authenticated against the domain
>controller 'ABC'. When I try to add these Windows logins, it fails since it
>wants to authenticate against XYZ but theres no trust.
>So how can I bypass the authentication and get those entries added to my SQL
>Account? Any way to hack the system tables? If not, thats fine. If theres
>anyway to get these entries in the equivalent logins/users tables in the
>databases is what I want to accomplish.
>Thankssql

Need security advice on xp_cmdshell, bcp, xml procedure

I have a stored procedure that creates an xml file. It executes a SELECT
statement with the FOR XML clause and then writes the xml file using bcp and
xp_cmdshell. I am calling this procedure by passing it a parameter via ADO.
I have configured the SQL Server Agent with a proxy account so non-SysAdmin
can execute xp_cmdshell.

I'm concerned about giving non-SysAdmins execute on xp_cmdshell. I'm also
concerned about having to maintain the password on my proxy account when
that users' password changes.

Is there a better, more secure way to generate this xml file.

ThanksTerri (terri@.cybernets.com) writes:
> I have a stored procedure that creates an xml file. It executes a SELECT
> statement with the FOR XML clause and then writes the xml file using bcp
> and xp_cmdshell. I am calling this procedure by passing it a parameter
> via ADO. I have configured the SQL Server Agent with a proxy account so
> non-SysAdmin can execute xp_cmdshell.
> I'm concerned about giving non-SysAdmins execute on xp_cmdshell. I'm also
> concerned about having to maintain the password on my proxy account when
> that users' password changes.
> Is there a better, more secure way to generate this xml file.

I'm not really sure what you but it sounds like you do something like:

bcp "SELECT ... FOR XML" queryout outfile.bcp

This is not likely to work very well. ODBC will chop the XML document
after each 2033 character. See KB 275583.

So you would need to get the XML document to the client, and have the
client to create the file and put it where it belongs. Which probably
is better from a security perspective as well.

--
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|||> This is not likely to work very well. ODBC will chop the XML document
> after each 2033 character. See KB 275583.
> So you would need to get the XML document to the client, and have the
> client to create the file and put it where it belongs. Which probably
> is better from a security perspective as well.

Thanks Erland,

I'm not using Query Analyzer so I don't think KB 275583 applies to me.

I'm calling the following procedure via ADO

CREATE PROCEDURE procGenerateXML
@.CheckRequestID int
AS
declare @.sql nvarchar(4000)
set @.sql= 'bcp "EXEC TestDB..proctest ' + CONVERT(varchar(8),@.ID) + '"' + '
queryout test.xml -SServer1 -T -c -r -t'
exec master..xp_cmdshell @.sql
GO

The procedure proctest looks like:

CREATE PROCEDURE proctest
@.ID int
AS
SELECT...
FROM...
WHERE...
FOR XML AUTO, ELEMENTS
GO

I then call the procedure like this
Dim cn As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim Param1
Dim ID As Integer
Dim provstr As String
Dim myfrm As Form
Dim dbs As Database
Set dbs = CurrentDb()
ID = Me.ID

cn.Provider = "sqloledb"
provstr = "Server=Server1;Database=TestDB;Trusted_Connection= Yes"
cn.Open provstr

Set cmd.ActiveConnection = cn
cmd.CommandText = "dbo.procGenerateXML"
cmd.CommandType = adCmdStoredProc
Set Param1 = cmd.CreateParameter("Input", adInteger, adParamInput)
cmd.Parameters.Append Param1
Param1.Value = ID
Set rs = cmd.Execute

I'm looking for guidance on the following:

-Can I use this xp_cmdshell method without giving my end users execute
permissions on xp_cmdshell and if not;
-Are there alternatives that don't use xp_cmdshell

Thanks|||Terri (terri@.cybernets.com) writes:
>> This is not likely to work very well. ODBC will chop the XML document
>> after each 2033 character. See KB 275583.
>>
>> So you would need to get the XML document to the client, and have the
>> client to create the file and put it where it belongs. Which probably
>> is better from a security perspective as well.
> Thanks Erland,
> I'm not using Query Analyzer so I don't think KB 275583 applies to me.

But you are using BCP which is implemented in ODBC. So I would definitely
encourage you to test to generate a large XML document, before you
ponder the issues with access to xp_cmdshell.

--
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|||If you are already calling this code from ADO, then instead of BCP and
all that you can just directly execute the FOR XML statement, loop
through each 2033 char return results and create the XML file from the
web server.

Erland Sommarskog wrote:
> Terri (terri@.cybernets.com) writes:
> >> This is not likely to work very well. ODBC will chop the XML document
> >> after each 2033 character. See KB 275583.
> >>
> >> So you would need to get the XML document to the client, and have the
> >> client to create the file and put it where it belongs. Which probably
> >> is better from a security perspective as well.
> > Thanks Erland,
> > I'm not using Query Analyzer so I don't think KB 275583 applies to me.
> But you are using BCP which is implemented in ODBC. So I would definitely
> encourage you to test to generate a large XML document, before you
> ponder the issues with access to xp_cmdshell.
>
>
> --
> 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|||pb648174 (google@.webpaul.net) writes:
> If you are already calling this code from ADO, then instead of BCP and
> all that you can just directly execute the FOR XML statement, loop
> through each 2033 char return results and create the XML file from the
> web server.

Actually, if he would do it the simple-minded way, he would not get
2033-characters slices, as he is using SQLOLEDB(*) - he would get a binary
thingie instead.

I have not investigated it, but I believe the proper way to receive FOR
XML in ADO with SQLOLEDB is to use the Stream object.

But apart from that fine detail, I agree with you. Doing this from SQL
Server will be diffictul.

(*) If you use the MSDASQL provider, that is ODBC, then you would have
to as you say. But I would not recommend that.

--
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|||Thanks to those who replied, I'm going to investigate the ADO stream object.

"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns970CB19EE5537Yazorman@.127.0.0.1...
> pb648174 (google@.webpaul.net) writes:
> > If you are already calling this code from ADO, then instead of BCP and
> > all that you can just directly execute the FOR XML statement, loop
> > through each 2033 char return results and create the XML file from the
> > web server.
> Actually, if he would do it the simple-minded way, he would not get
> 2033-characters slices, as he is using SQLOLEDB(*) - he would get a binary
> thingie instead.
> I have not investigated it, but I believe the proper way to receive FOR
> XML in ADO with SQLOLEDB is to use the Stream object.
> But apart from that fine detail, I agree with you. Doing this from SQL
> Server will be diffictul.
> (*) If you use the MSDASQL provider, that is ODBC, then you would have
> to as you say. But I would not recommend that.|||I did something like this in C# .NET recently via the native SQL OleDB
provider and had to do the looping action. I think that even when I
submitted the Query using Query Analyzer, I could see it coming back as
multiple rows... I could be wrong though.

Need script that can shrink copy of DB to fit on a notebook

I need a script that will take a 40GB 300+ table database and shrink it to the 1st 1000 rows in each table and delete security tables like tblchargecard. Want to get size to about 1gb to fit on a notebook for development. Any suggestions would be appreciated.declare a table variable with two columns, table_name and Table_rowcount.

From a join between sysindexes and sysobjetcs table, get the table names and their respective rowcounts into this table variable.

update the table_rowcount columns with table_rowcount-1000

write a script to automatically generate delete statements for each table, each delete statement being preceded by set rowcount table_rowcount and followed by set rowcount 0 statement.

run this generated script.|||Creative, but I think that will crash if you have relational integrity established, and especially if you are using cascading deletes.

If your database does have cascading deletes, (as it should) then just delete everything but, say, every 10th record, out of the highest level tables in the schema. (You can use something like WHERE Right(PrimaryKey, 1) <> 0 if you have numeric keys, for instance.) Do this in a copy of the database, of course!

As far as "delete security tables like tblchargecard", you'll have to specify those in your script.|||Thanks for the help from both of you. will give this a try.