changing the SORT Collations

Hi
I am very new in SQL2000. My question is , is it possible to change the sor
t collation in SQL2000 after installation and how. Currently my server is s
et to case sensitive. how do I change it to case insensitive?
Also is it possible to do this on my select command instead? For instance,
my server is case sensitive, but what i want to do is when I do a select com
mand, I would temporarily remove the case sensitivity so it can get the desi
red results. This setting
is only temporary and it goes back to the original setting once the session
is diconnected. Is there a way to do this?
Thank youCollation in SQL 2000 is now a per database setting. Before you had to
reinstall SQL to change the collation, and you could not load databases that
had a different sort setting.
In SQL 2000, you can set the collation on a server, database, table and
obviously transact-SQL level setting. To change the sort order for the
server, you have to detach all databases, uninstall and reinstall SQL in the
desired sort order, then convert the existing databases into new ones to
change the sort.
To change the sort of a database do ALTER DATABASE <db_name> COLLATE <new
collation order>
To alter a table, you can change the characters columns with ALTER TABLE
ALTER TABLE MyTable ALTER COLUMN ColumnName
[varchar|char](size) COLLATE <new collation order> [NOT
NULL|NULL]
Read more about it by downloading SQL books online from
http://www.microsoft.com/sql and looking up 'change collation'. Good luck.
****************************************
***************************
Andy S.
MCSE NT/2000, MCDBA SQL 7/2000
andymcdba1@.NOMORESPAM.yahoo.com
Please remove NOMORESPAM before replying.
Always keep your antivirus and Microsoft software
up to date with the latest definitions and product updates.
Be suspicious of every email attachment, I will never send
or post anything other than the text of a http:// link nor
post the link directly to a file for downloading.
This posting is provided "as is" with no warranties
and confers no rights.
****************************************
***************************
"DaSaint" <anonymous@.discussions.microsoft.com> wrote in message
news:592DF876-0E85-4B52-8733-7A4D52D62DB7@.microsoft.com...
> Hi
> I am very new in SQL2000. My question is , is it possible to change the
sort collation in SQL2000 after installation and how. Currently my server
is set to case sensitive. how do I change it to case insensitive?
> Also is it possible to do this on my select command instead? For
instance, my server is case sensitive, but what i want to do is when I do a
select command, I would temporarily remove the case sensitivity so it can
get the desired results. This setting is only temporary and it goes back to
the original setting once the session is diconnected. Is there a way to do
this?
> Thank you|||The collation on the sql server 2000 is database-specific. That means you
can create your application database with any collation you want.
To do that use, for exaple,
CREATE DATABASE dbname
COLLATE SQL_Latin1_General_CP1_CI_AI
If you want your database with one collation, having, for example, case
insensitive, and you want that one field of one table to be case sensitive,
you can do that by using:
CREATE TABLE mytable (
ID int primary key identity(1,1),
NAME nvarchar(256) COLLATE SQL_Latin1_General_CP1_CS_AI
But, if you want to rebuild your master database (that defines your system
tables collation), you can use the rebuild master using a utility.
See
http://msdn.microsoft.com/library/d... />
_24aa.asp.
"DaSaint" <anonymous@.discussions.microsoft.com> wrote in message
news:592DF876-0E85-4B52-8733-7A4D52D62DB7@.microsoft.com...
> Hi
> I am very new in SQL2000. My question is , is it possible to change the
sort collation in SQL2000 after installation and how. Currently my server
is set to case sensitive. how do I change it to case insensitive?
> Also is it possible to do this on my select command instead? For
instance, my server is case sensitive, but what i want to do is when I do a
select command, I would temporarily remove the case sensitivity so it can
get the desired results. This setting is only temporary and it goes back to
the original setting once the session is diconnected. Is there a way to do
this?
> Thank you|||Thanks andy and francisco for the info!!

changing the default drive and folder for DBs

Hi;

I feel like an idiot to be asking this,
but how do I change the default drive and folder
in which new databases are created?

I've been looking all over Server Mgmt Studio,
and I can't find anything. I'm thinking that someone
can tell me this off the top of their heads,
or slip me a link to a web page.

Thanks,

Never mind everyone - I found my own solution.

THANKS anyway.

|||

Please tell us how you fix that

changing stored procedure ownership

Hi
I have many stored procedures which have the owner has 'essbase' e.g. Clear
Nominal, how do I change the ownership of the stored procedure(s) to dbo
Thanks
See sp_changeobjectowner in SQL Server Books Online.
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Raks" <Raks@.discussions.microsoft.com> wrote in message
news:59E67253-2682-4D1C-9802-630B5F335BFC@.microsoft.com...
Hi
I have many stored procedures which have the owner has 'essbase' e.g. Clear
Nominal, how do I change the ownership of the stored procedure(s) to dbo
Thanks

changing stored procedure ownership

Hi
I have many stored procedures which have the owner has 'essbase' e.g. Clear
Nominal, how do I change the ownership of the stored procedure(s) to dbo
ThanksSee sp_changeobjectowner in SQL Server Books Online.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Raks" <Raks@.discussions.microsoft.com> wrote in message
news:59E67253-2682-4D1C-9802-630B5F335BFC@.microsoft.com...
Hi
I have many stored procedures which have the owner has 'essbase' e.g. Clear
Nominal, how do I change the ownership of the stored procedure(s) to dbo
Thankssql

Changing Security to Windows Authentications from sql login

Hi
I have around 300+ DTS packages, with most of the have been created by users
with SQL Login information.
MY company is now moving to Windows Authentication.
There two places where we can put SQL Login information in DTS Packages.
1. When you create new connection in DTS for SQL Server.
2. When you save DTS packages...
I have posted the same in DTS group also.
It is lots of manual work to change all the dts packages to windows authenti
catiosn.
Is there any simple and automatic way to change all the connections to use w
indows authentications...'?
Appreciate your help.
Thanks
JPUsing SQL DMO you can do anything. Infact the whole SQL Enterprise Manager
uses DMO object to show you what you see.
If you can create a lil application, use SQL DMO, iterate thru DTS packages
and modify their properties.
Read more about SQL DMO !
"JP" <anonymous@.discussions.microsoft.com> wrote in message
news:B8F6BF05-533D-4C8B-8D83-3958B0D24165@.microsoft.com...
> Hi
> I have around 300+ DTS packages, with most of the have been created by
users with SQL Login information.
> MY company is now moving to Windows Authentication.
> There two places where we can put SQL Login information in DTS Packages.
> 1. When you create new connection in DTS for SQL Server.
> 2. When you save DTS packages...
> I have posted the same in DTS group also.
> It is lots of manual work to change all the dts packages to windows
authenticatiosn.
> Is there any simple and automatic way to change all the connections to use
windows authentications...'?
> Appreciate your help.
> Thanks
> JP
>

Changing object ownership back to dbo

Hi:
I have a database where objects are owned by different users. I would like
to revert ownership of all objects to dbo. What is the best way to do this?
Thanks,
Charliesp_changeobjectowner
David Portas
SQL Server MVP
--
"Charlie@.CBFC" <charle1@.comcast.net> wrote in message
news:Oe9b3gfzFHA.1132@.TK2MSFTNGP10.phx.gbl...
> Hi:
> I have a database where objects are owned by different users. I would
> like
> to revert ownership of all objects to dbo. What is the best way to do
> this?
> Thanks,
> Charlie
>|||Hi,
Use the below script. Execute the script by replacing oldobjectowner and new
user. This query will give you script for changing the object.
Just cut and paste the output to a new query analyzer window and execute to
change the object owner.
set quoted_identifier off
select 'sp_changeobjectowner '+name+"'"+'newusername' +"'"+char(10)+'go'
from sysobjects where user_name(uid)='oldobjectowner'
set quoted_identifier on
Thanks
Hari
SQL Server MVP
"Charlie@.CBFC" <charle1@.comcast.net> wrote in message
news:Oe9b3gfzFHA.1132@.TK2MSFTNGP10.phx.gbl...
> Hi:
> I have a database where objects are owned by different users. I would
> like
> to revert ownership of all objects to dbo. What is the best way to do
> this?
> Thanks,
> Charlie
>

Changing DSN to DSN-Less

hi

I bought a web site of ebay for about 4500 (www.uktattoostudios.co.uk). I decided to get the web site hosted on a new hosting company called namesco.co.uk. the only problem is they dont support DSN and have DSN-Less

How would I change the site to work with DSN-Less?

the site is a tattoo studio directory.It uses MS SQL database with about 1000 studios listed. The studios can log in and change there details etc.

The only file I find on the site with dsn in is this code call tattoo.asp

<%
' FileName="Connection_odbc_conn_dsn.htm"
' Type="ADO"
' DesigntimeType="ADO"
' HTTP="false"
' Catalog=""
' Schema=""
Dim MM_TATTOO_STRING
MM_TATTOO_STRING = "dsn=TATTOO;uid=dt-data;pwd=XXXXXXXX;"
%>

I can connect to the ms sql server , Would I need to change the MS SQL 2005 database codes? If yes how would I do this. and what do i change in the above code

Finally would changing it to dsn-less cause any problems?

thanks
dave
info@.uktattoostudios.co.ukUse this t replace what u av:
************************************************** **********************************
<%
' FileName="Connection_ado_conn_string.htm"
' Type="ADO"
' DesigntimeType="ADO"
' HTTP="true"
' Catalog=""
' Schema=""
Dim MM_connme_STRING
MM_connme_STRING = "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=dt-data;Initial Catalog=Databasename;Data Source=localhost;pwd=XXXXXXXX;"
%>
************************************************** ********************************
/*
Note:
Initial Catalog : - is/should be d name of ur database
datasource : - is d name of d computer on which d database(on MS-SQL 2000) resides(ds can be computer name or IP Address)
*/

Changing database path of my reports

hi!!!
i have problem in changing the database of my reports. i found a code here

Dim crxDatabaseTable As CRAXDRT.DatabaseTable
For Each crxDatabaseTable In report.Database.Tables
crxDatabaseTable.ConnectionProperties.Item("Database Name") = "C:\Temp\xtreme.mdb"
Next crxDatabaseTable

bu when i coded this to my project it says CRAXDRT.DatabaseTable

anyone can help me for this i have change paths with code because of when i run my project any drives it has to work...Why not use
crxDatabaseTable.Location = "C:\Temp\xtreme.mdb"
instead of setting ConnectionProperties?

Also, dont forget that a report can have subreports that will also need setting location info.|||how can i use it?

i cant do anything... i think i have to define something but i dont know what...

my report name is exitreportrep when i wrote it to a form load or anywhere i have 3 choice only
exitreportrep.EnableEventLog()
exitreportrep.Equals()
exitreportrep.ReferenceEquals()

i dont know what to do...

Changing Database Mail Host Database

Hi

I am trying to change the host database (MSDB) of Database Mail to some new database (say NewDB) and

unable to make DatabaseMail90.exe point to this new database (NewDB).

Is it possible to do this?

Thanks

Uddemarri

When I passed the database name and server name as command line parameters to DatabaseMail90.exe , it picked messages from the queue but did not send any mail. The following error is logged in sysmail_event_log.

1) Exception Information =================== Exception Type: System.NullReferenceException Message: Object reference not set to an instance of an object. Data: System.Collections.ListDictionaryInternal TargetSite: Microsoft.SqlServer.Management.SqlIMail.Server.Objects.Account GetAccount(Int32) HelpLink: NULL Source: DatabaseMailEngine StackTrace Information =================== at Microsoft.SqlServer.Management.SqlIMail.Server.DataAccess.DataAccessAdapter.GetAccount(Int32 accountID) at Microsoft.SqlServer.Management.SqlIMail.Server.DataAccess.SessionManager.GetAccount(Int32 accountID) at Microsoft.SqlServer.Management.SqlIMail.Server.Controller.CommandFactory.CreateSendMailCommand(DBSession dbSession) at Microsoft.SqlServer.Management.SqlIMail.Server.Controller.CommandFactory.CreateCommand(DBSession dbSession) at Microsoft.SqlServer.Management.SqlIMail.Server.Controller.CommandRunner.Run(DBSession db) at Microsoft.SqlServer.Management.SqlIMail.IMailProcess.ThreadCallBack.MailOperation(Object o)

Any ideas?

|||That's definitely not a supported or tested option. I don't think you can start the mail service yourself.|||Thanks Roger.

changing data types

hi!
I'd like to change the datatype of one tablefield from varchar to text.
Is this possible when the table already is populated, or will there be trouble?
obscurrDepending the version you're using.

In any way

exec sp_rename MyTable, MyOldTable
select Col1, Col2, convert(TEXT, ColVarchar) ColVarchar
into MyTable
from MyOldTable
drop table MyOldTable|||I'm using SQL Server version 7.0

Changing Cube source in a EXCEL pivot table

Hi

I have developed a cube on a "test" server - using Excel 2003 as teh front end. All is well with the cube an dI now want to point Excel to the "production" server. I have modified the .oqy file to point to teh new server but Excel does not seem to use this - unless I start over and import data.

is there anyway of pointing to a different server (teh database/cube names are the same - I did an Archive and Restore to move teh database over)

thanks

Steve

By the way - this is SQL Analysis Server 2000 and Excel 2003|||

There are 2 techniques I know of to change the data source for an Excel pivot table.

The "low tech" one is to shut down the Analysis Services instance on your test server. Then when you open up the Excel file, Excel will prompt you for a server.

The "hight tech" option is to write some VBA code to change the connection string. The trick to getting at the connection string is that it is stored on the connection property of the PivotCache object. To change a single pivot table on the current sheet you would write something like the following:

thisWorkbook.ActiveSheet.PivotTables(1).PivotCache.Connection = "Provider-MSOLAP.2;Data Source=<Server>;Initial Catalog=<Database>"

|||Another technique, which was mentioned on the public SQL OLAP Newsgroup, is to save the pivot table as an XML file. Then you can find and edit the connection string (search for the server/DB/cube names), and re-open and save the XML back as .XLS.|||

Thanks to all.

I checked out the XML and the VBA solutions - at least I have options now.

Changing Cube source in a EXCEL pivot table

Hi

I have developed a cube on a "test" server - using Excel 2003 as teh front end. All is well with the cube an dI now want to point Excel to the "production" server. I have modified the .oqy file to point to teh new server but Excel does not seem to use this - unless I start over and import data.

is there anyway of pointing to a different server (teh database/cube names are the same - I did an Archive and Restore to move teh database over)

thanks

Steve

By the way - this is SQL Analysis Server 2000 and Excel 2003|||

There are 2 techniques I know of to change the data source for an Excel pivot table.

The "low tech" one is to shut down the Analysis Services instance on your test server. Then when you open up the Excel file, Excel will prompt you for a server.

The "hight tech" option is to write some VBA code to change the connection string. The trick to getting at the connection string is that it is stored on the connection property of the PivotCache object. To change a single pivot table on the current sheet you would write something like the following:

thisWorkbook.ActiveSheet.PivotTables(1).PivotCache.Connection = "Provider-MSOLAP.2;Data Source=<Server>;Initial Catalog=<Database>"

|||Another technique, which was mentioned on the public SQL OLAP Newsgroup, is to save the pivot table as an XML file. Then you can find and edit the connection string (search for the server/DB/cube names), and re-open and save the XML back as .XLS.|||

Thanks to all.

I checked out the XML and the VBA solutions - at least I have options now.

Changing credentials on-the-fly

Hi!
I have a late requirement from de Security Department.
This is the situation: the apps must connect to SQL Server 2000 through OLE
DB using a generic user, but the developers will not know its credentials
(at least the password)
Unfortunatelly, like I said, this is a late requirement because there are
many apps already working, obviously knowing the credentials.
I spent so much time thinking a way to solve this problem without
codification, finally I arrived to this idea, but I don't know if it's
achievable.
- Create a new user in SQL Server and grant the right permissions on the
addecuate DBs
- Deny permissions to the old user
- Leave the apps just how they are now
- When an app attempt to open a connection, SQL Server must modify the old
credentials with the new ones...
Is this possible?
And obviously...how?
Otherwise, do you know another solution (without reprogramming)?
Thanks in advance,
Promenade
PS: I apologize my englishConsider using an application role. They can login as themselves and then
set the application role. The role stays in effect until logout. You may
have issues with connection pooling, however.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
"Promenade" <promenade@.no.com> wrote in message
news:OwhJb2pwFHA.2656@.TK2MSFTNGP09.phx.gbl...
> Hi!
> I have a late requirement from de Security Department.
> This is the situation: the apps must connect to SQL Server 2000 through
> OLE
> DB using a generic user, but the developers will not know its credentials
> (at least the password)
> Unfortunatelly, like I said, this is a late requirement because there are
> many apps already working, obviously knowing the credentials.
> I spent so much time thinking a way to solve this problem without
> codification, finally I arrived to this idea, but I don't know if it's
> achievable.
> - Create a new user in SQL Server and grant the right permissions on the
> addecuate DBs
> - Deny permissions to the old user
> - Leave the apps just how they are now
> - When an app attempt to open a connection, SQL Server must modify the old
> credentials with the new ones...
> Is this possible?
> And obviously...how?
> Otherwise, do you know another solution (without reprogramming)?
> Thanks in advance,
> Promenade
> PS: I apologize my english
>|||The problem is that the Client makes the connection; so, there is no way to
intervene at the server to modify this.
Thus, that is why using Windows Authentication is a best practice. Unless
you log on as that user, you can't gain access. Also, with WA, no passwords
are ever transmitted over the network.
Another best practice, DEVELOPERS NEVER DO THE PRODUCTION INSTALLATION:
that's what Server Administrators are for. Thus, the Operations team has
the credentials (locked away in a vault somewhere) but the developer/end
users can use the product.
Basically, you are not going to get away with correcting bad practices
without recoding/redesigning the bad apps is some manner.
There's a saying, which may be a little harsh, but is applicable in this
situation: "There is no patch [nor hotfix] for stupidity."
Sincerely,
Anthony Thomas
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:O4guKXrwFHA.2960@.tk2msftngp13.phx.gbl...
The application need to set the application role using sp_setapprole.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Promenade" <promenade@.no.com> wrote in message
news:u3nPbjqwFHA.596@.TK2MSFTNGP12.phx.gbl...
> Thank you very much, Tom...
> I don't know anything about application roles, so maybe you can help me...
> When you said "They can login as themselves and then set the application
> role"...the process of setting the application role has to be made in
every
> application code or this task can be made in SQL Server?
>
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:#tiGw8pwFHA.2132@.TK2MSFTNGP15.phx.gbl...
then
may
> credentials
> are
the
> old
>

Changing credentials on-the-fly

Hi!
I have a late requirement from de Security Department.
This is the situation: the apps must connect to SQL Server 2000 through OLE
DB using a generic user, but the developers will not know its credentials
(at least the password)
Unfortunatelly, like I said, this is a late requirement because there are
many apps already working, obviously knowing the credentials.
I spent so much time thinking a way to solve this problem without
codification, finally I arrived to this idea, but I don't know if it's
achievable.
- Create a new user in SQL Server and grant the right permissions on the
addecuate DBs
- Deny permissions to the old user
- Leave the apps just how they are now
- When an app attempt to open a connection, SQL Server must modify the old
credentials with the new ones...
Is this possible?
And obviously...how?
Otherwise, do you know another solution (without reprogramming)?
Thanks in advance,
Promenade
PS: I apologize my englishConsider using an application role. They can login as themselves and then
set the application role. The role stays in effect until logout. You may
have issues with connection pooling, however.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
"Promenade" <promenade@.no.com> wrote in message
news:OwhJb2pwFHA.2656@.TK2MSFTNGP09.phx.gbl...
> Hi!
> I have a late requirement from de Security Department.
> This is the situation: the apps must connect to SQL Server 2000 through
> OLE
> DB using a generic user, but the developers will not know its credentials
> (at least the password)
> Unfortunatelly, like I said, this is a late requirement because there are
> many apps already working, obviously knowing the credentials.
> I spent so much time thinking a way to solve this problem without
> codification, finally I arrived to this idea, but I don't know if it's
> achievable.
> - Create a new user in SQL Server and grant the right permissions on the
> addecuate DBs
> - Deny permissions to the old user
> - Leave the apps just how they are now
> - When an app attempt to open a connection, SQL Server must modify the old
> credentials with the new ones...
> Is this possible?
> And obviously...how?
> Otherwise, do you know another solution (without reprogramming)?
> Thanks in advance,
> Promenade
> PS: I apologize my english
>|||Thank you very much, Tom...
I don't know anything about application roles, so maybe you can help me...
When you said "They can login as themselves and then set the application
role"...the process of setting the application role has to be made in every
application code or this task can be made in SQL Server?
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:#tiGw8pwFHA.2132@.TK2MSFTNGP15.phx.gbl...
> Consider using an application role. They can login as themselves and then
> set the application role. The role stays in effect until logout. You may
> have issues with connection pooling, however.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> "Promenade" <promenade@.no.com> wrote in message
> news:OwhJb2pwFHA.2656@.TK2MSFTNGP09.phx.gbl...
credentials[vbcol=seagreen]
are[vbcol=seagreen]
old[vbcol=seagreen]
>|||The application need to set the application role using sp_setapprole.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Promenade" <promenade@.no.com> wrote in message news:u3nPbjqwFHA.596@.TK2MSFTNGP12.phx.gbl...

> Thank you very much, Tom...
> I don't know anything about application roles, so maybe you can help me...
> When you said "They can login as themselves and then set the application
> role"...the process of setting the application role has to be made in eve
ry
> application code or this task can be made in SQL Server?
>
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:#tiGw8pwFHA.2132@.TK2MSFTNGP15.phx.gbl...
> credentials
> are
> old
>|||The problem is that the Client makes the connection; so, there is no way to
intervene at the server to modify this.
Thus, that is why using Windows Authentication is a best practice. Unless
you log on as that user, you can't gain access. Also, with WA, no passwords
are ever transmitted over the network.
Another best practice, DEVELOPERS NEVER DO THE PRODUCTION INSTALLATION:
that's what Server Administrators are for. Thus, the Operations team has
the credentials (locked away in a vault somewhere) but the developer/end
users can use the product.
Basically, you are not going to get away with correcting bad practices
without recoding/redesigning the bad apps is some manner.
There's a saying, which may be a little harsh, but is applicable in this
situation: "There is no patch [nor hotfix] for stupidity."
Sincerely,
Anthony Thomas
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:O4guKXrwFHA.2960@.tk2msftngp13.phx.gbl...
The application need to set the application role using sp_setapprole.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Promenade" <promenade@.no.com> wrote in message
news:u3nPbjqwFHA.596@.TK2MSFTNGP12.phx.gbl...
> Thank you very much, Tom...
> I don't know anything about application roles, so maybe you can help me...
> When you said "They can login as themselves and then set the application
> role"...the process of setting the application role has to be made in
every
> application code or this task can be made in SQL Server?
>
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:#tiGw8pwFHA.2132@.TK2MSFTNGP15.phx.gbl...
then[vbcol=seagreen]
may[vbcol=seagreen]
> credentials
> are
the[vbcol=seagreen]
> old
>

Changing credentials on-the-fly

Hi!
I have a late requirement from de Security Department.
This is the situation: the apps must connect to SQL Server 2000 through OLE
DB using a generic user, but the developers will not know its credentials
(at least the password)
Unfortunatelly, like I said, this is a late requirement because there are
many apps already working, obviously knowing the credentials.
I spent so much time thinking a way to solve this problem without
codification, finally I arrived to this idea, but I don't know if it's
achievable.
- Create a new user in SQL Server and grant the right permissions on the
addecuate DBs
- Deny permissions to the old user
- Leave the apps just how they are now
- When an app attempt to open a connection, SQL Server must modify the old
credentials with the new ones...
Is this possible?
And obviously...how?
Otherwise, do you know another solution (without reprogramming)?
Thanks in advance,
Promenade
PS: I apologize my english
Consider using an application role. They can login as themselves and then
set the application role. The role stays in effect until logout. You may
have issues with connection pooling, however.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
"Promenade" <promenade@.no.com> wrote in message
news:OwhJb2pwFHA.2656@.TK2MSFTNGP09.phx.gbl...
> Hi!
> I have a late requirement from de Security Department.
> This is the situation: the apps must connect to SQL Server 2000 through
> OLE
> DB using a generic user, but the developers will not know its credentials
> (at least the password)
> Unfortunatelly, like I said, this is a late requirement because there are
> many apps already working, obviously knowing the credentials.
> I spent so much time thinking a way to solve this problem without
> codification, finally I arrived to this idea, but I don't know if it's
> achievable.
> - Create a new user in SQL Server and grant the right permissions on the
> addecuate DBs
> - Deny permissions to the old user
> - Leave the apps just how they are now
> - When an app attempt to open a connection, SQL Server must modify the old
> credentials with the new ones...
> Is this possible?
> And obviously...how?
> Otherwise, do you know another solution (without reprogramming)?
> Thanks in advance,
> Promenade
> PS: I apologize my english
>
|||Thank you very much, Tom...
I don't know anything about application roles, so maybe you can help me...
When you said "They can login as themselves and then set the application
role"...the process of setting the application role has to be made in every
application code or this task can be made in SQL Server?
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:#tiGw8pwFHA.2132@.TK2MSFTNGP15.phx.gbl...[vbcol=seagreen]
> Consider using an application role. They can login as themselves and then
> set the application role. The role stays in effect until logout. You may
> have issues with connection pooling, however.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> "Promenade" <promenade@.no.com> wrote in message
> news:OwhJb2pwFHA.2656@.TK2MSFTNGP09.phx.gbl...
credentials[vbcol=seagreen]
are[vbcol=seagreen]
old
>
|||The application need to set the application role using sp_setapprole.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Promenade" <promenade@.no.com> wrote in message news:u3nPbjqwFHA.596@.TK2MSFTNGP12.phx.gbl...
> Thank you very much, Tom...
> I don't know anything about application roles, so maybe you can help me...
> When you said "They can login as themselves and then set the application
> role"...the process of setting the application role has to be made in every
> application code or this task can be made in SQL Server?
>
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:#tiGw8pwFHA.2132@.TK2MSFTNGP15.phx.gbl...
> credentials
> are
> old
>
|||The problem is that the Client makes the connection; so, there is no way to
intervene at the server to modify this.
Thus, that is why using Windows Authentication is a best practice. Unless
you log on as that user, you can't gain access. Also, with WA, no passwords
are ever transmitted over the network.
Another best practice, DEVELOPERS NEVER DO THE PRODUCTION INSTALLATION:
that's what Server Administrators are for. Thus, the Operations team has
the credentials (locked away in a vault somewhere) but the developer/end
users can use the product.
Basically, you are not going to get away with correcting bad practices
without recoding/redesigning the bad apps is some manner.
There's a saying, which may be a little harsh, but is applicable in this
situation: "There is no patch [nor hotfix] for stupidity."
Sincerely,
Anthony Thomas

"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:O4guKXrwFHA.2960@.tk2msftngp13.phx.gbl...
The application need to set the application role using sp_setapprole.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Promenade" <promenade@.no.com> wrote in message
news:u3nPbjqwFHA.596@.TK2MSFTNGP12.phx.gbl...
> Thank you very much, Tom...
> I don't know anything about application roles, so maybe you can help me...
> When you said "They can login as themselves and then set the application
> role"...the process of setting the application role has to be made in
every[vbcol=seagreen]
> application code or this task can be made in SQL Server?
>
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:#tiGw8pwFHA.2132@.TK2MSFTNGP15.phx.gbl...
then[vbcol=seagreen]
may[vbcol=seagreen]
> credentials
> are
the
> old
>

Changing credentials on-the-fly

Hi!
I have a late requirement from de Security Department.
This is the situation: the apps must connect to SQL Server 2000 through OLE
DB using a generic user, but the developers will not know its credentials
(at least the password)
Unfortunatelly, like I said, this is a late requirement because there are
many apps already working, obviously knowing the credentials.
I spent so much time thinking a way to solve this problem without
codification, finally I arrived to this idea, but I don't know if it's
achievable.
- Create a new user in SQL Server and grant the right permissions on the
addecuate DBs
- Deny permissions to the old user
- Leave the apps just how they are now
- When an app attempt to open a connection, SQL Server must modify the old
credentials with the new ones...
Is this possible?
And obviously...how?
Otherwise, do you know another solution (without reprogramming)?
Thanks in advance,
Promenade
PS: I apologize my english
Consider using an application role. They can login as themselves and then
set the application role. The role stays in effect until logout. You may
have issues with connection pooling, however.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
"Promenade" <promenade@.no.com> wrote in message
news:OwhJb2pwFHA.2656@.TK2MSFTNGP09.phx.gbl...
> Hi!
> I have a late requirement from de Security Department.
> This is the situation: the apps must connect to SQL Server 2000 through
> OLE
> DB using a generic user, but the developers will not know its credentials
> (at least the password)
> Unfortunatelly, like I said, this is a late requirement because there are
> many apps already working, obviously knowing the credentials.
> I spent so much time thinking a way to solve this problem without
> codification, finally I arrived to this idea, but I don't know if it's
> achievable.
> - Create a new user in SQL Server and grant the right permissions on the
> addecuate DBs
> - Deny permissions to the old user
> - Leave the apps just how they are now
> - When an app attempt to open a connection, SQL Server must modify the old
> credentials with the new ones...
> Is this possible?
> And obviously...how?
> Otherwise, do you know another solution (without reprogramming)?
> Thanks in advance,
> Promenade
> PS: I apologize my english
>
|||Thank you very much, Tom...
I don't know anything about application roles, so maybe you can help me...
When you said "They can login as themselves and then set the application
role"...the process of setting the application role has to be made in every
application code or this task can be made in SQL Server?
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:#tiGw8pwFHA.2132@.TK2MSFTNGP15.phx.gbl...[vbcol=seagreen]
> Consider using an application role. They can login as themselves and then
> set the application role. The role stays in effect until logout. You may
> have issues with connection pooling, however.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> "Promenade" <promenade@.no.com> wrote in message
> news:OwhJb2pwFHA.2656@.TK2MSFTNGP09.phx.gbl...
credentials[vbcol=seagreen]
are[vbcol=seagreen]
old
>
|||The application need to set the application role using sp_setapprole.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Promenade" <promenade@.no.com> wrote in message news:u3nPbjqwFHA.596@.TK2MSFTNGP12.phx.gbl...
> Thank you very much, Tom...
> I don't know anything about application roles, so maybe you can help me...
> When you said "They can login as themselves and then set the application
> role"...the process of setting the application role has to be made in every
> application code or this task can be made in SQL Server?
>
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:#tiGw8pwFHA.2132@.TK2MSFTNGP15.phx.gbl...
> credentials
> are
> old
>
|||The problem is that the Client makes the connection; so, there is no way to
intervene at the server to modify this.
Thus, that is why using Windows Authentication is a best practice. Unless
you log on as that user, you can't gain access. Also, with WA, no passwords
are ever transmitted over the network.
Another best practice, DEVELOPERS NEVER DO THE PRODUCTION INSTALLATION:
that's what Server Administrators are for. Thus, the Operations team has
the credentials (locked away in a vault somewhere) but the developer/end
users can use the product.
Basically, you are not going to get away with correcting bad practices
without recoding/redesigning the bad apps is some manner.
There's a saying, which may be a little harsh, but is applicable in this
situation: "There is no patch [nor hotfix] for stupidity."
Sincerely,
Anthony Thomas

"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:O4guKXrwFHA.2960@.tk2msftngp13.phx.gbl...
The application need to set the application role using sp_setapprole.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Promenade" <promenade@.no.com> wrote in message
news:u3nPbjqwFHA.596@.TK2MSFTNGP12.phx.gbl...
> Thank you very much, Tom...
> I don't know anything about application roles, so maybe you can help me...
> When you said "They can login as themselves and then set the application
> role"...the process of setting the application role has to be made in
every[vbcol=seagreen]
> application code or this task can be made in SQL Server?
>
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:#tiGw8pwFHA.2132@.TK2MSFTNGP15.phx.gbl...
then[vbcol=seagreen]
may[vbcol=seagreen]
> credentials
> are
the
> old
>

Changing credentials on-the-fly

Hi!
I have a late requirement from de Security Department.
This is the situation: the apps must connect to SQL Server 2000 through OLE
DB using a generic user, but the developers will not know its credentials
(at least the password)
Unfortunatelly, like I said, this is a late requirement because there are
many apps already working, obviously knowing the credentials.
I spent so much time thinking a way to solve this problem without
codification, finally I arrived to this idea, but I don't know if it's
achievable.
- Create a new user in SQL Server and grant the right permissions on the
addecuate DBs
- Deny permissions to the old user
- Leave the apps just how they are now
- When an app attempt to open a connection, SQL Server must modify the old
credentials with the new ones...
Is this possible?
And obviously...how?
Otherwise, do you know another solution (without reprogramming)?
Thanks in advance,
Promenade
PS: I apologize my english
Consider using an application role. They can login as themselves and then
set the application role. The role stays in effect until logout. You may
have issues with connection pooling, however.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
"Promenade" <promenade@.no.com> wrote in message
news:OwhJb2pwFHA.2656@.TK2MSFTNGP09.phx.gbl...
> Hi!
> I have a late requirement from de Security Department.
> This is the situation: the apps must connect to SQL Server 2000 through
> OLE
> DB using a generic user, but the developers will not know its credentials
> (at least the password)
> Unfortunatelly, like I said, this is a late requirement because there are
> many apps already working, obviously knowing the credentials.
> I spent so much time thinking a way to solve this problem without
> codification, finally I arrived to this idea, but I don't know if it's
> achievable.
> - Create a new user in SQL Server and grant the right permissions on the
> addecuate DBs
> - Deny permissions to the old user
> - Leave the apps just how they are now
> - When an app attempt to open a connection, SQL Server must modify the old
> credentials with the new ones...
> Is this possible?
> And obviously...how?
> Otherwise, do you know another solution (without reprogramming)?
> Thanks in advance,
> Promenade
> PS: I apologize my english
>
|||Thank you very much, Tom...
I don't know anything about application roles, so maybe you can help me...
When you said "They can login as themselves and then set the application
role"...the process of setting the application role has to be made in every
application code or this task can be made in SQL Server?
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:#tiGw8pwFHA.2132@.TK2MSFTNGP15.phx.gbl...[vbcol=seagreen]
> Consider using an application role. They can login as themselves and then
> set the application role. The role stays in effect until logout. You may
> have issues with connection pooling, however.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> "Promenade" <promenade@.no.com> wrote in message
> news:OwhJb2pwFHA.2656@.TK2MSFTNGP09.phx.gbl...
credentials[vbcol=seagreen]
are[vbcol=seagreen]
old
>
|||The application need to set the application role using sp_setapprole.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Promenade" <promenade@.no.com> wrote in message news:u3nPbjqwFHA.596@.TK2MSFTNGP12.phx.gbl...
> Thank you very much, Tom...
> I don't know anything about application roles, so maybe you can help me...
> When you said "They can login as themselves and then set the application
> role"...the process of setting the application role has to be made in every
> application code or this task can be made in SQL Server?
>
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:#tiGw8pwFHA.2132@.TK2MSFTNGP15.phx.gbl...
> credentials
> are
> old
>
|||The problem is that the Client makes the connection; so, there is no way to
intervene at the server to modify this.
Thus, that is why using Windows Authentication is a best practice. Unless
you log on as that user, you can't gain access. Also, with WA, no passwords
are ever transmitted over the network.
Another best practice, DEVELOPERS NEVER DO THE PRODUCTION INSTALLATION:
that's what Server Administrators are for. Thus, the Operations team has
the credentials (locked away in a vault somewhere) but the developer/end
users can use the product.
Basically, you are not going to get away with correcting bad practices
without recoding/redesigning the bad apps is some manner.
There's a saying, which may be a little harsh, but is applicable in this
situation: "There is no patch [nor hotfix] for stupidity."
Sincerely,
Anthony Thomas

"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:O4guKXrwFHA.2960@.tk2msftngp13.phx.gbl...
The application need to set the application role using sp_setapprole.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Promenade" <promenade@.no.com> wrote in message
news:u3nPbjqwFHA.596@.TK2MSFTNGP12.phx.gbl...
> Thank you very much, Tom...
> I don't know anything about application roles, so maybe you can help me...
> When you said "They can login as themselves and then set the application
> role"...the process of setting the application role has to be made in
every[vbcol=seagreen]
> application code or this task can be made in SQL Server?
>
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:#tiGw8pwFHA.2132@.TK2MSFTNGP15.phx.gbl...
then[vbcol=seagreen]
may[vbcol=seagreen]
> credentials
> are
the
> old
>