Authentication problem on 2005 Express Edition

I'll start from the beginning: I have a Windows application developed in C#.
It uses a SQL Server 2005 Express Edition database to store data. Now I have
created a deployment of this app and database, and I try to install it on my
own machine. No problems, works without errors.
Now, I put this deployment project on the network and a user downloads it to
his Vista machine and runs the setup. When he starts the app he gets the
following error message:
"Login failed for user "MyUser". The user is not associated with a thrusted
server connection".
The connection string I use in my application is this:
Server=.\SQLEXPRESS;Database=MyDB;User ID=MyUser;Password=thissucks
In the SQL Server Management Studio Express I have already marked the "SQL
Server and Windows authentication mode" (mixed mode?) under
server/options/security. If
there is anywhere else this should be set, please let me know.
Since I have no control over the Vista machine, I can't force him/her to
create a new account that is thrusted. All I want is to deploy my app and
database, without having to worry about authorization and any other weird
stuff, since in many cases I don't know the setup on each box.
I have already tried alot of the suggestions I've found, but let's start all
over so it will be right. What should I do to remove this error? How can I
deploy my database without having to think about authorization/security
stuff?
TIA
PZ
To resolve your issue, tell me how you defined the User MyUser at MyDB level;
is it mapped to a login? In this case, the login is a Windows login or a SQL
login?
If it is a Windows login (...CREATE LOGIN loginname FROM WINDOWS...) it
should map a Domain Account, otherwise on the new machine it will be not
authenticated, and yolu should script a new login and remap the MyUser to
this new login.
Gilberto Zampatti
> I'll start from the beginning: I have a Windows application developed in C#.
> It uses a SQL Server 2005 Express Edition database to store data. Now I have
> created a deployment of this app and database, and I try to install it on my
> own machine. No problems, works without errors.
> Now, I put this deployment project on the network and a user downloads it to
> his Vista machine and runs the setup. When he starts the app he gets the
> following error message:
> "Login failed for user "MyUser". The user is not associated with a thrusted
> server connection".
> The connection string I use in my application is this:
> Server=.\SQLEXPRESS;Database=MyDB;User ID=MyUser;Password=thissucks
> In the SQL Server Management Studio Express I have already marked the "SQL
> Server and Windows authentication mode" (mixed mode?) under
> server/options/security. If
> there is anywhere else this should be set, please let me know.
> Since I have no control over the Vista machine, I can't force him/her to
> create a new account that is thrusted. All I want is to deploy my app and
> database, without having to worry about authorization and any other weird
> stuff, since in many cases I don't know the setup on each box.
> I have already tried alot of the suggestions I've found, but let's start all
> over so it will be right. What should I do to remove this error? How can I
> deploy my database without having to think about authorization/security
> stuff?
> TIA
> PZ
>
>
|||Hi Gilberto
Sounds reasonable. I'll try to describe it here, so you can determine if I
am missing something (please note that I use Server Management Studio
Express on my development machine to deal with these issues, while I don't
setup anything on the deployment machine and I run no scripts):
- First of all, I want to create everything on database level/scope, so when
the database is deployed the user installing it don't have to create/change
any user/authenticaion in Windows at all.
- I have created a new User account in the database "MyDB" scope (under
"SQLExpress\databases\MyDB\Security\Users". This user is called "MyUser"
- This user is given 2 role memberships: db_datareader and db_datawriter
- This user is not set to any schema ownerships and no extended properties
are added
- Under the "SQLExpress\databases\MyDB\properties" I have added the user
"MyUser" as type "User" and given him the following explicit permissions:
Connect, Insert, Update, Delete, Execute
I have also created this user under "SQLExpress\Security\Logins" and
selected the option "SQL Server authentication" without selecting "Enforce
password policy". However, I fail to se if this is required, since this is
not on database level, but on server level. And I only deploy the database
files. Could it be that I'm missing this user on the other server under
"Logins"? Is that why I need a script to create it?
Can you explain to me how the database is deployed on the other machine?
Does it also create a server object and store the database MyDB within? In
that case, it could be true that I need to run some scripts to put that new
server object up-to-date?
Please advise.
Regards,
PZ
"Gilberto Zampatti" <GilbertoZampatti@.discussions.microsoft.com> wrote in
message news:EF361E5F-D149-4006-8E5E-C0001E03E511@.microsoft.com...[vbcol=seagreen]
> To resolve your issue, tell me how you defined the User MyUser at MyDB
> level;
> is it mapped to a login? In this case, the login is a Windows login or a
> SQL
> login?
> If it is a Windows login (...CREATE LOGIN loginname FROM WINDOWS...) it
> should map a Domain Account, otherwise on the new machine it will be not
> authenticated, and yolu should script a new login and remap the MyUser to
> this new login.
> Gilberto Zampatti
|||What do you mean when you write "...And I only deploy the database
> files...": did you bak-up and restore the dbfiles or detach-attache the same files or what else?
It seems to me that in the deployed instance you are not able to mat MyUser
to a valid login (remember that this mapping is made considering some "sid"
of the login and not simply the login name.
The simplest way to operate could be:
- deploy (once) an instance of SQL Express and on each instance run a script
to create a SQL login (why not a Windows login?) named xxx
- deploy your DB files
- launch a script to:
- drop MyUser
- create MyUser mapping it to the xxx login and grant him the needed
permissions
Gilberto Zampatti
"Preben Zacho" wrote:

> Hi Gilberto
> Sounds reasonable. I'll try to describe it here, so you can determine if I
> am missing something (please note that I use Server Management Studio
> Express on my development machine to deal with these issues, while I don't
> setup anything on the deployment machine and I run no scripts):
> - First of all, I want to create everything on database level/scope, so when
> the database is deployed the user installing it don't have to create/change
> any user/authenticaion in Windows at all.
> - I have created a new User account in the database "MyDB" scope (under
> "SQLExpress\databases\MyDB\Security\Users". This user is called "MyUser"
> - This user is given 2 role memberships: db_datareader and db_datawriter
> - This user is not set to any schema ownerships and no extended properties
> are added
> - Under the "SQLExpress\databases\MyDB\properties" I have added the user
> "MyUser" as type "User" and given him the following explicit permissions:
> Connect, Insert, Update, Delete, Execute
> I have also created this user under "SQLExpress\Security\Logins" and
> selected the option "SQL Server authentication" without selecting "Enforce
> password policy". However, I fail to se if this is required, since this is
> not on database level, but on server level. And I only deploy the database
> files. Could it be that I'm missing this user on the other server under
> "Logins"? Is that why I need a script to create it?
> Can you explain to me how the database is deployed on the other machine?
> Does it also create a server object and store the database MyDB within? In
> that case, it could be true that I need to run some scripts to put that new
> server object up-to-date?
> Please advise.
> Regards,
> PZ
> "Gilberto Zampatti" <GilbertoZampatti@.discussions.microsoft.com> wrote in
> message news:EF361E5F-D149-4006-8E5E-C0001E03E511@.microsoft.com...
>
>

Authentication problem on 2005 Express Edition

I'll start from the beginning: I have a Windows application developed in C#.
It uses a SQL Server 2005 Express Edition database to store data. Now I have
created a deployment of this app and database, and I try to install it on my
own machine. No problems, works without errors.
Now, I put this deployment project on the network and a user downloads it to
his Vista machine and runs the setup. When he starts the app he gets the
following error message:
"Login failed for user "MyUser". The user is not associated with a thrusted
server connection".
The connection string I use in my application is this:
Server=.\SQLEXPRESS;Database=MyDB;User ID=MyUser;Password=thissucks
In the SQL Server Management Studio Express I have already marked the "SQL
Server and Windows authentication mode" (mixed mode?) under
server/options/security. If
there is anywhere else this should be set, please let me know.
Since I have no control over the Vista machine, I can't force him/her to
create a new account that is thrusted. All I want is to deploy my app and
database, without having to worry about authorization and any other weird
stuff, since in many cases I don't know the setup on each box.
I have already tried alot of the suggestions I've found, but let's start all
over so it will be right. What should I do to remove this error? How can I
deploy my database without having to think about authorization/security
stuff?
TIA
PZTo resolve your issue, tell me how you defined the User MyUser at MyDB level
;
is it mapped to a login? In this case, the login is a Windows login or a SQL
login?
If it is a Windows login (...CREATE LOGIN loginname FROM WINDOWS...) it
should map a Domain Account, otherwise on the new machine it will be not
authenticated, and yolu should script a new login and remap the MyUser to
this new login.
Gilberto Zampatti
> I'll start from the beginning: I have a Windows application developed in C
#.
> It uses a SQL Server 2005 Express Edition database to store data. Now I ha
ve
> created a deployment of this app and database, and I try to install it on
my
> own machine. No problems, works without errors.
> Now, I put this deployment project on the network and a user downloads it
to
> his Vista machine and runs the setup. When he starts the app he gets the
> following error message:
> "Login failed for user "MyUser". The user is not associated with a thruste
d
> server connection".
> The connection string I use in my application is this:
> Server=.\SQLEXPRESS;Database=MyDB;User ID=MyUser;Password=thissucks
> In the SQL Server Management Studio Express I have already marked the "SQL
> Server and Windows authentication mode" (mixed mode?) under
> server/options/security. If
> there is anywhere else this should be set, please let me know.
> Since I have no control over the Vista machine, I can't force him/her to
> create a new account that is thrusted. All I want is to deploy my app and
> database, without having to worry about authorization and any other weird
> stuff, since in many cases I don't know the setup on each box.
> I have already tried alot of the suggestions I've found, but let's start a
ll
> over so it will be right. What should I do to remove this error? How can I
> deploy my database without having to think about authorization/security
> stuff?
> TIA
> PZ
>
>|||Hi Gilberto
Sounds reasonable. I'll try to describe it here, so you can determine if I
am missing something (please note that I use Server Management Studio
Express on my development machine to deal with these issues, while I don't
setup anything on the deployment machine and I run no scripts):
- First of all, I want to create everything on database level/scope, so when
the database is deployed the user installing it don't have to create/change
any user/authenticaion in Windows at all.
- I have created a new User account in the database "MyDB" scope (under
" SQLExpress\databases\MyDB\Security\Users
". This user is called "MyUser"
- This user is given 2 role memberships: db_datareader and db_datawriter
- This user is not set to any schema ownerships and no extended properties
are added
- Under the "SQLExpress\databases\MyDB\properties" I have added the user
"MyUser" as type "User" and given him the following explicit permissions:
Connect, Insert, Update, Delete, Execute
I have also created this user under "SQLExpress\Security\Logins" and
selected the option "SQL Server authentication" without selecting "Enforce
password policy". However, I fail to se if this is required, since this is
not on database level, but on server level. And I only deploy the database
files. Could it be that I'm missing this user on the other server under
"Logins"' Is that why I need a script to create it?
Can you explain to me how the database is deployed on the other machine?
Does it also create a server object and store the database MyDB within? In
that case, it could be true that I need to run some scripts to put that new
server object up-to-date'
Please advise.
Regards,
PZ
"Gilberto Zampatti" <GilbertoZampatti@.discussions.microsoft.com> wrote in
message news:EF361E5F-D149-4006-8E5E-C0001E03E511@.microsoft.com...[vbcol=seagreen]
> To resolve your issue, tell me how you defined the User MyUser at MyDB
> level;
> is it mapped to a login? In this case, the login is a Windows login or a
> SQL
> login?
> If it is a Windows login (...CREATE LOGIN loginname FROM WINDOWS...) it
> should map a Domain Account, otherwise on the new machine it will be not
> authenticated, and yolu should script a new login and remap the MyUser to
> this new login.
> Gilberto Zampatti|||What do you mean when you write "...And I only deploy the database
> files...": did you bak-up and restore the dbfiles or detach-attache the same files
or what else?
It seems to me that in the deployed instance you are not able to mat MyUser
to a valid login (remember that this mapping is made considering some "sid"
of the login and not simply the login name.
The simplest way to operate could be:
- deploy (once) an instance of SQL Express and on each instance run a script
to create a SQL login (why not a Windows login?) named xxx
- deploy your DB files
- launch a script to:
- drop MyUser
- create MyUser mapping it to the xxx login and grant him the needed
permissions
Gilberto Zampatti
"Preben Zacho" wrote:

> Hi Gilberto
> Sounds reasonable. I'll try to describe it here, so you can determine if I
> am missing something (please note that I use Server Management Studio
> Express on my development machine to deal with these issues, while I don't
> setup anything on the deployment machine and I run no scripts):
> - First of all, I want to create everything on database level/scope, so wh
en
> the database is deployed the user installing it don't have to create/chang
e
> any user/authenticaion in Windows at all.
> - I have created a new User account in the database "MyDB" scope (under
> " SQLExpress\databases\MyDB\Security\Users
". This user is called "MyUser"
> - This user is given 2 role memberships: db_datareader and db_datawriter
> - This user is not set to any schema ownerships and no extended properties
> are added
> - Under the "SQLExpress\databases\MyDB\properties" I have added the user
> "MyUser" as type "User" and given him the following explicit permissions:
> Connect, Insert, Update, Delete, Execute
> I have also created this user under "SQLExpress\Security\Logins" and
> selected the option "SQL Server authentication" without selecting "Enforce
> password policy". However, I fail to se if this is required, since this is
> not on database level, but on server level. And I only deploy the database
> files. Could it be that I'm missing this user on the other server under
> "Logins"' Is that why I need a script to create it?
> Can you explain to me how the database is deployed on the other machine?
> Does it also create a server object and store the database MyDB within? In
> that case, it could be true that I need to run some scripts to put that ne
w
> server object up-to-date'
> Please advise.
> Regards,
> PZ
> "Gilberto Zampatti" <GilbertoZampatti@.discussions.microsoft.com> wrote in
> message news:EF361E5F-D149-4006-8E5E-C0001E03E511@.microsoft.com...
>
>

Authentication problem on 2005 Express Edition

I'll start from the beginning: I have a Windows application developed in C#.
It uses a SQL Server 2005 Express Edition database to store data. Now I have
created a deployment of this app and database, and I try to install it on my
own machine. No problems, works without errors.
Now, I put this deployment project on the network and a user downloads it to
his Vista machine and runs the setup. When he starts the app he gets the
following error message:
"Login failed for user "MyUser". The user is not associated with a thrusted
server connection".
The connection string I use in my application is this:
Server=.\SQLEXPRESS;Database=MyDB;User ID=MyUser;Password=thissucks
In the SQL Server Management Studio Express I have already marked the "SQL
Server and Windows authentication mode" (mixed mode?) under
server/options/security. If
there is anywhere else this should be set, please let me know.
Since I have no control over the Vista machine, I can't force him/her to
create a new account that is thrusted. All I want is to deploy my app and
database, without having to worry about authorization and any other weird
stuff, since in many cases I don't know the setup on each box.
I have already tried alot of the suggestions I've found, but let's start all
over so it will be right. What should I do to remove this error? How can I
deploy my database without having to think about authorization/security
stuff?
TIA
PZTo resolve your issue, tell me how you defined the User MyUser at MyDB level;
is it mapped to a login? In this case, the login is a Windows login or a SQL
login?
If it is a Windows login (...CREATE LOGIN loginname FROM WINDOWS...) it
should map a Domain Account, otherwise on the new machine it will be not
authenticated, and yolu should script a new login and remap the MyUser to
this new login.
Gilberto Zampatti
> I'll start from the beginning: I have a Windows application developed in C#.
> It uses a SQL Server 2005 Express Edition database to store data. Now I have
> created a deployment of this app and database, and I try to install it on my
> own machine. No problems, works without errors.
> Now, I put this deployment project on the network and a user downloads it to
> his Vista machine and runs the setup. When he starts the app he gets the
> following error message:
> "Login failed for user "MyUser". The user is not associated with a thrusted
> server connection".
> The connection string I use in my application is this:
> Server=.\SQLEXPRESS;Database=MyDB;User ID=MyUser;Password=thissucks
> In the SQL Server Management Studio Express I have already marked the "SQL
> Server and Windows authentication mode" (mixed mode?) under
> server/options/security. If
> there is anywhere else this should be set, please let me know.
> Since I have no control over the Vista machine, I can't force him/her to
> create a new account that is thrusted. All I want is to deploy my app and
> database, without having to worry about authorization and any other weird
> stuff, since in many cases I don't know the setup on each box.
> I have already tried alot of the suggestions I've found, but let's start all
> over so it will be right. What should I do to remove this error? How can I
> deploy my database without having to think about authorization/security
> stuff?
> TIA
> PZ
>
>|||Hi Gilberto
Sounds reasonable. I'll try to describe it here, so you can determine if I
am missing something (please note that I use Server Management Studio
Express on my development machine to deal with these issues, while I don't
setup anything on the deployment machine and I run no scripts):
- First of all, I want to create everything on database level/scope, so when
the database is deployed the user installing it don't have to create/change
any user/authenticaion in Windows at all.
- I have created a new User account in the database "MyDB" scope (under
"SQLExpress\databases\MyDB\Security\Users". This user is called "MyUser"
- This user is given 2 role memberships: db_datareader and db_datawriter
- This user is not set to any schema ownerships and no extended properties
are added
- Under the "SQLExpress\databases\MyDB\properties" I have added the user
"MyUser" as type "User" and given him the following explicit permissions:
Connect, Insert, Update, Delete, Execute
I have also created this user under "SQLExpress\Security\Logins" and
selected the option "SQL Server authentication" without selecting "Enforce
password policy". However, I fail to se if this is required, since this is
not on database level, but on server level. And I only deploy the database
files. Could it be that I'm missing this user on the other server under
"Logins"' Is that why I need a script to create it?
Can you explain to me how the database is deployed on the other machine?
Does it also create a server object and store the database MyDB within? In
that case, it could be true that I need to run some scripts to put that new
server object up-to-date'
Please advise.
Regards,
PZ
"Gilberto Zampatti" <GilbertoZampatti@.discussions.microsoft.com> wrote in
message news:EF361E5F-D149-4006-8E5E-C0001E03E511@.microsoft.com...
> To resolve your issue, tell me how you defined the User MyUser at MyDB
> level;
> is it mapped to a login? In this case, the login is a Windows login or a
> SQL
> login?
> If it is a Windows login (...CREATE LOGIN loginname FROM WINDOWS...) it
> should map a Domain Account, otherwise on the new machine it will be not
> authenticated, and yolu should script a new login and remap the MyUser to
> this new login.
> Gilberto Zampatti
>> I'll start from the beginning: I have a Windows application developed in
>> C#.
>> It uses a SQL Server 2005 Express Edition database to store data. Now I
>> have
>> created a deployment of this app and database, and I try to install it on
>> my
>> own machine. No problems, works without errors.
>> Now, I put this deployment project on the network and a user downloads it
>> to
>> his Vista machine and runs the setup. When he starts the app he gets the
>> following error message:
>> "Login failed for user "MyUser". The user is not associated with a
>> thrusted
>> server connection".
>> The connection string I use in my application is this:
>> Server=.\SQLEXPRESS;Database=MyDB;User ID=MyUser;Password=thissucks
>> In the SQL Server Management Studio Express I have already marked the
>> "SQL
>> Server and Windows authentication mode" (mixed mode?) under
>> server/options/security. If
>> there is anywhere else this should be set, please let me know.
>> Since I have no control over the Vista machine, I can't force him/her to
>> create a new account that is thrusted. All I want is to deploy my app and
>> database, without having to worry about authorization and any other weird
>> stuff, since in many cases I don't know the setup on each box.
>> I have already tried alot of the suggestions I've found, but let's start
>> all
>> over so it will be right. What should I do to remove this error? How can
>> I
>> deploy my database without having to think about authorization/security
>> stuff?
>> TIA
>> PZ
>>|||What do you mean when you write "...And I only deploy the database
> files...": did you bak-up and restore the dbfiles or detach-attache the same files or what else?
It seems to me that in the deployed instance you are not able to mat MyUser
to a valid login (remember that this mapping is made considering some "sid"
of the login and not simply the login name.
The simplest way to operate could be:
- deploy (once) an instance of SQL Express and on each instance run a script
to create a SQL login (why not a Windows login?) named xxx
- deploy your DB files
- launch a script to:
- drop MyUser
- create MyUser mapping it to the xxx login and grant him the needed
permissions
Gilberto Zampatti
"Preben Zacho" wrote:
> Hi Gilberto
> Sounds reasonable. I'll try to describe it here, so you can determine if I
> am missing something (please note that I use Server Management Studio
> Express on my development machine to deal with these issues, while I don't
> setup anything on the deployment machine and I run no scripts):
> - First of all, I want to create everything on database level/scope, so when
> the database is deployed the user installing it don't have to create/change
> any user/authenticaion in Windows at all.
> - I have created a new User account in the database "MyDB" scope (under
> "SQLExpress\databases\MyDB\Security\Users". This user is called "MyUser"
> - This user is given 2 role memberships: db_datareader and db_datawriter
> - This user is not set to any schema ownerships and no extended properties
> are added
> - Under the "SQLExpress\databases\MyDB\properties" I have added the user
> "MyUser" as type "User" and given him the following explicit permissions:
> Connect, Insert, Update, Delete, Execute
> I have also created this user under "SQLExpress\Security\Logins" and
> selected the option "SQL Server authentication" without selecting "Enforce
> password policy". However, I fail to se if this is required, since this is
> not on database level, but on server level. And I only deploy the database
> files. Could it be that I'm missing this user on the other server under
> "Logins"' Is that why I need a script to create it?
> Can you explain to me how the database is deployed on the other machine?
> Does it also create a server object and store the database MyDB within? In
> that case, it could be true that I need to run some scripts to put that new
> server object up-to-date'
> Please advise.
> Regards,
> PZ
> "Gilberto Zampatti" <GilbertoZampatti@.discussions.microsoft.com> wrote in
> message news:EF361E5F-D149-4006-8E5E-C0001E03E511@.microsoft.com...
> > To resolve your issue, tell me how you defined the User MyUser at MyDB
> > level;
> > is it mapped to a login? In this case, the login is a Windows login or a
> > SQL
> > login?
> > If it is a Windows login (...CREATE LOGIN loginname FROM WINDOWS...) it
> > should map a Domain Account, otherwise on the new machine it will be not
> > authenticated, and yolu should script a new login and remap the MyUser to
> > this new login.
> > Gilberto Zampatti
> >> I'll start from the beginning: I have a Windows application developed in
> >> C#.
> >> It uses a SQL Server 2005 Express Edition database to store data. Now I
> >> have
> >> created a deployment of this app and database, and I try to install it on
> >> my
> >> own machine. No problems, works without errors.
> >>
> >> Now, I put this deployment project on the network and a user downloads it
> >> to
> >> his Vista machine and runs the setup. When he starts the app he gets the
> >> following error message:
> >>
> >> "Login failed for user "MyUser". The user is not associated with a
> >> thrusted
> >> server connection".
> >>
> >> The connection string I use in my application is this:
> >>
> >> Server=.\SQLEXPRESS;Database=MyDB;User ID=MyUser;Password=thissucks
> >>
> >> In the SQL Server Management Studio Express I have already marked the
> >> "SQL
> >> Server and Windows authentication mode" (mixed mode?) under
> >> server/options/security. If
> >> there is anywhere else this should be set, please let me know.
> >>
> >> Since I have no control over the Vista machine, I can't force him/her to
> >> create a new account that is thrusted. All I want is to deploy my app and
> >> database, without having to worry about authorization and any other weird
> >> stuff, since in many cases I don't know the setup on each box.
> >>
> >> I have already tried alot of the suggestions I've found, but let's start
> >> all
> >> over so it will be right. What should I do to remove this error? How can
> >> I
> >> deploy my database without having to think about authorization/security
> >> stuff?
> >>
> >> TIA
> >>
> >> PZ
> >>
> >>
> >>
>
>sql

audit trails

HI all,
Where can I read up about the best practice when it comes to creatin audit
trail. What to store how much to store, etc
What do some of the gurus do,
Thanks
RobertRobert Bravery wrote:
> HI all,
> Where can I read up about the best practice when it comes to creatin audit
> trail. What to store how much to store, etc
> What do some of the gurus do,
> Thanks
> Robert
Some examples here:
http://www.aspfaq.com/show.asp?id=2448
Good practice for audit (and for almost any application for that
matter) is to deny users direct access to tables. Otherwise there are
always likely to be ways to subvert your audit trail.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Thanks David
Robert
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1139305231.327268.266710@.f14g2000cwb.googlegroups.com...
> Robert Bravery wrote:
audit
> Some examples here:
> http://www.aspfaq.com/show.asp?id=2448
> Good practice for audit (and for almost any application for that
> matter) is to deny users direct access to tables. Otherwise there are
> always likely to be ways to subvert your audit trail.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>|||Triggers can be used to track insert/update/deletes. However, for auditing
most any type of event (such as SQL selects, SP execution, altering objects,
etc.) you can use SQL Server Profiler:
http://msdn2.microsoft.com/ms190378.aspx
http://msdn2.microsoft.com/ms191148.aspx
http://msdn2.microsoft.com/ms190176.aspx
"Robert Bravery" <me@.u.com> wrote in message
news:%239iyBf8KGHA.1132@.TK2MSFTNGP10.phx.gbl...
> HI all,
> Where can I read up about the best practice when it comes to creatin audit
> trail. What to store how much to store, etc
> What do some of the gurus do,
> Thanks
> Robert
>
>
>

Audit Table

Hi all gurus
I want to create a store procedure which will generate an audit table and
track all the changes done in any of my tables in any database of my
server.
Please guide me where to start
Any guidance will be highly appreciated
ThanksYou probably won;t want a stored proc, but a trigger on each of the tables
you want to audit.. I believe there's a MS design pattern you can copy foor
this. Check out MSDN... In general,
1) decide what you want to record.. . some ideas are
Table, RecordIdentifier, Column, OldValue, NewValue, WHoChangedit,
ChangeDatetime, Operation(Insert/Update/Delete), etc...
2) build a table to store the data
3) write trigger in each table that inserts new record in audit table for
each row being modified...
hth,
Charles
"AM" wrote:

> Hi all gurus
> I want to create a store procedure which will generate an audit table and
> track all the changes done in any of my tables in any database of my
> server.
> Please guide me where to start
> Any guidance will be highly appreciated
> Thanks
>
>|||hi,
You can add to the strategic or central production's tables fields such as:
-LastDML ('u': update, 'i': insert)
-LastDMLuser (linked to tables as tblsysusers and tblsysrights)
"CBretana" wrote:
[vbcol=seagreen]
> You probably won;t want a stored proc, but a trigger on each of the tables
> you want to audit.. I believe there's a MS design pattern you can copy fo
or
> this. Check out MSDN... In general,
> 1) decide what you want to record.. . some ideas are
> Table, RecordIdentifier, Column, OldValue, NewValue, WHoChangedit,
> ChangeDatetime, Operation(Insert/Update/Delete), etc...
> 2) build a table to store the data
> 3) write trigger in each table that inserts new record in audit table for
> each row being modified...
> hth,
> Charles
> "AM" wrote:
>|||Hi all
I cannot change anykind on tables , so I can not add triggers on the tables
Thanks
AM
"Enric" <Enric@.discussions.microsoft.com> wrote in message
news:D4198061-8BD2-47D5-B2F2-6D117618D84A@.microsoft.com...
> hi,
> You can add to the strategic or central production's tables fields such
as:[vbcol=seagreen]
> -LastDML ('u': update, 'i': insert)
> -LastDMLuser (linked to tables as tblsysusers and tblsysrights)
>
> "CBretana" wrote:
>
tables[vbcol=seagreen]
foor[vbcol=seagreen]
for[vbcol=seagreen]
and[vbcol=seagreen]|||Then you will need to intercept the Stored Proc(s) that are inserting,
updating, or deleting record sfrom these tables and put the same code, more
or less in there, understanding that any changes made to the tables
directly, or from another as yet unwritten SP< will not be audited...
"AM" wrote:

> Hi all
> I cannot change anykind on tables , so I can not add triggers on the table
s
> Thanks
> AM
> "Enric" <Enric@.discussions.microsoft.com> wrote in message
> news:D4198061-8BD2-47D5-B2F2-6D117618D84A@.microsoft.com...
> as:
> tables
> foor
> for
> and
>
>|||This may be an option.
http://www.lumigent.com/
"AM" <anonymous@.extraquest.com> wrote in message
news:ufPXUHUOFHA.1176@.TK2MSFTNGP12.phx.gbl...
> Hi all
> I cannot change anykind on tables , so I can not add triggers on the
tables
> Thanks
> AM
> "Enric" <Enric@.discussions.microsoft.com> wrote in message
> news:D4198061-8BD2-47D5-B2F2-6D117618D84A@.microsoft.com...
> as:
> tables
copy[vbcol=seagreen]
> foor
> for
table[vbcol=seagreen]
> and
my[vbcol=seagreen]
>|||Thanks
"Terri" <terri@.cybernets.com> wrote in message
news:d2s32a$tkr$1@.reader2.nmix.net...
> This may be an option.
> http://www.lumigent.com/
>
> "AM" <anonymous@.extraquest.com> wrote in message
> news:ufPXUHUOFHA.1176@.TK2MSFTNGP12.phx.gbl...
> tables
such[vbcol=seagreen]
> copy
WHoChangedit,[vbcol=seagreen]
table[vbcol=seagreen]
> table
> my
>

Audit Table

Hi all gurus
I want to create a store procedure which will generate an audit table and
track all the changes done in any of my tables in any database of my
server.
Please guide me where to start
Any guidance will be highly appreciated
ThanksYou probably won;t want a stored proc, but a trigger on each of the tables
you want to audit.. I believe there's a MS design pattern you can copy foor
this. Check out MSDN... In general,
1) decide what you want to record.. . some ideas are
Table, RecordIdentifier, Column, OldValue, NewValue, WHoChangedit,
ChangeDatetime, Operation(Insert/Update/Delete), etc...
2) build a table to store the data
3) write trigger in each table that inserts new record in audit table for
each row being modified...
hth,
Charles
"AM" wrote:

> Hi all gurus
> I want to create a store procedure which will generate an audit table and
> track all the changes done in any of my tables in any database of my
> server.
> Please guide me where to start
> Any guidance will be highly appreciated
> Thanks
>
>|||hi,
You can add to the strategic or central production's tables fields such as:
-LastDML ('u': update, 'i': insert)
-LastDMLuser (linked to tables as tblsysusers and tblsysrights)
"CBretana" wrote:
> You probably won;t want a stored proc, but a trigger on each of the tables
> you want to audit.. I believe there's a MS design pattern you can copy fo
or
> this. Check out MSDN... In general,
> 1) decide what you want to record.. . some ideas are
> Table, RecordIdentifier, Column, OldValue, NewValue, WHoChangedit,
> ChangeDatetime, Operation(Insert/Update/Delete), etc...
> 2) build a table to store the data
> 3) write trigger in each table that inserts new record in audit table for
> each row being modified...
> hth,
> Charles
> "AM" wrote:
>|||Hi all
I cannot change anykind on tables , so I can not add triggers on the tables
Thanks
AM
"Enric" <Enric@.discussions.microsoft.com> wrote in message
news:D4198061-8BD2-47D5-B2F2-6D117618D84A@.microsoft.com...
> hi,
> You can add to the strategic or central production's tables fields such
as:
> -LastDML ('u': update, 'i': insert)
> -LastDMLuser (linked to tables as tblsysusers and tblsysrights)
>
> "CBretana" wrote:
>
tables
foor
for
and|||Then you will need to intercept the Stored Proc(s) that are inserting,
updating, or deleting record sfrom these tables and put the same code, more
or less in there, understanding that any changes made to the tables
directly, or from another as yet unwritten SP< will not be audited...
"AM" wrote:

> Hi all
> I cannot change anykind on tables , so I can not add triggers on the table
s
> Thanks
> AM
> "Enric" <Enric@.discussions.microsoft.com> wrote in message
> news:D4198061-8BD2-47D5-B2F2-6D117618D84A@.microsoft.com...
> as:
> tables
> foor
> for
> and
>
>|||This may be an option.
http://www.lumigent.com/
"AM" <anonymous@.extraquest.com> wrote in message
news:ufPXUHUOFHA.1176@.TK2MSFTNGP12.phx.gbl...
> Hi all
> I cannot change anykind on tables , so I can not add triggers on the
tables
> Thanks
> AM
> "Enric" <Enric@.discussions.microsoft.com> wrote in message
> news:D4198061-8BD2-47D5-B2F2-6D117618D84A@.microsoft.com...
> as:
> tables
copy
> foor
> for
table
> and
my
>|||Thanks
"Terri" <terri@.cybernets.com> wrote in message
news:d2s32a$tkr$1@.reader2.nmix.net...
> This may be an option.
> http://www.lumigent.com/
>
> "AM" <anonymous@.extraquest.com> wrote in message
> news:ufPXUHUOFHA.1176@.TK2MSFTNGP12.phx.gbl...
> tables
such
> copy
WHoChangedit,
table
> table
> my
>

Audit Table

Hi all gurus
I want to create a store procedure which will generate an audit table and
track all the changes done in any of my tables in any database of my
server.
Please guide me where to start
Any guidance will be highly appreciated
Thanks
You probably won;t want a stored proc, but a trigger on each of the tables
you want to audit.. I believe there's a MS design pattern you can copy foor
this. Check out MSDN... In general,
1) decide what you want to record.. . some ideas are
Table, RecordIdentifier, Column, OldValue, NewValue, WHoChangedit,
ChangeDatetime, Operation(Insert/Update/Delete), etc...
2) build a table to store the data
3) write trigger in each table that inserts new record in audit table for
each row being modified...
hth,
Charles
"AM" wrote:

> Hi all gurus
> I want to create a store procedure which will generate an audit table and
> track all the changes done in any of my tables in any database of my
> server.
> Please guide me where to start
> Any guidance will be highly appreciated
> Thanks
>
>
|||hi,
You can add to the strategic or central production's tables fields such as:
-LastDML ('u': update, 'i': insert)
-LastDMLuser (linked to tables as tblsysusers and tblsysrights)
"CBretana" wrote:
[vbcol=seagreen]
> You probably won;t want a stored proc, but a trigger on each of the tables
> you want to audit.. I believe there's a MS design pattern you can copy foor
> this. Check out MSDN... In general,
> 1) decide what you want to record.. . some ideas are
> Table, RecordIdentifier, Column, OldValue, NewValue, WHoChangedit,
> ChangeDatetime, Operation(Insert/Update/Delete), etc...
> 2) build a table to store the data
> 3) write trigger in each table that inserts new record in audit table for
> each row being modified...
> hth,
> Charles
> "AM" wrote:
|||Hi all
I cannot change anykind on tables , so I can not add triggers on the tables
Thanks
AM
"Enric" <Enric@.discussions.microsoft.com> wrote in message
news:D4198061-8BD2-47D5-B2F2-6D117618D84A@.microsoft.com...
> hi,
> You can add to the strategic or central production's tables fields such
as:[vbcol=seagreen]
> -LastDML ('u': update, 'i': insert)
> -LastDMLuser (linked to tables as tblsysusers and tblsysrights)
>
> "CBretana" wrote:
tables[vbcol=seagreen]
foor[vbcol=seagreen]
for[vbcol=seagreen]
and[vbcol=seagreen]
|||Then you will need to intercept the Stored Proc(s) that are inserting,
updating, or deleting record sfrom these tables and put the same code, more
or less in there, understanding that any changes made to the tables
directly, or from another as yet unwritten SP< will not be audited...
"AM" wrote:

> Hi all
> I cannot change anykind on tables , so I can not add triggers on the tables
> Thanks
> AM
> "Enric" <Enric@.discussions.microsoft.com> wrote in message
> news:D4198061-8BD2-47D5-B2F2-6D117618D84A@.microsoft.com...
> as:
> tables
> foor
> for
> and
>
>
|||This may be an option.
http://www.lumigent.com/
"AM" <anonymous@.extraquest.com> wrote in message
news:ufPXUHUOFHA.1176@.TK2MSFTNGP12.phx.gbl...
> Hi all
> I cannot change anykind on tables , so I can not add triggers on the
tables[vbcol=seagreen]
> Thanks
> AM
> "Enric" <Enric@.discussions.microsoft.com> wrote in message
> news:D4198061-8BD2-47D5-B2F2-6D117618D84A@.microsoft.com...
> as:
> tables
copy[vbcol=seagreen]
> foor
> for
table[vbcol=seagreen]
> and
my
>
|||Thanks
"Terri" <terri@.cybernets.com> wrote in message
news:d2s32a$tkr$1@.reader2.nmix.net...[vbcol=seagreen]
> This may be an option.
> http://www.lumigent.com/
>
> "AM" <anonymous@.extraquest.com> wrote in message
> news:ufPXUHUOFHA.1176@.TK2MSFTNGP12.phx.gbl...
> tables
such[vbcol=seagreen]
> copy
WHoChangedit,[vbcol=seagreen]
table
> table
> my
>

Audit Table

Hi all gurus
I want to create a store procedure which will generate an audit table and
track all the changes done in any of my tables in any database of my
server.
Please guide me where to start
Any guidance will be highly appreciated
ThanksYou probably won;t want a stored proc, but a trigger on each of the tables
you want to audit.. I believe there's a MS design pattern you can copy foor
this. Check out MSDN... In general,
1) decide what you want to record.. . some ideas are
Table, RecordIdentifier, Column, OldValue, NewValue, WHoChangedit,
ChangeDatetime, Operation(Insert/Update/Delete), etc...
2) build a table to store the data
3) write trigger in each table that inserts new record in audit table for
each row being modified...
hth,
Charles
"AM" wrote:
> Hi all gurus
> I want to create a store procedure which will generate an audit table and
> track all the changes done in any of my tables in any database of my
> server.
> Please guide me where to start
> Any guidance will be highly appreciated
> Thanks
>
>|||hi,
You can add to the strategic or central production's tables fields such as:
-LastDML ('u': update, 'i': insert)
-LastDMLuser (linked to tables as tblsysusers and tblsysrights)
"CBretana" wrote:
> You probably won;t want a stored proc, but a trigger on each of the tables
> you want to audit.. I believe there's a MS design pattern you can copy foor
> this. Check out MSDN... In general,
> 1) decide what you want to record.. . some ideas are
> Table, RecordIdentifier, Column, OldValue, NewValue, WHoChangedit,
> ChangeDatetime, Operation(Insert/Update/Delete), etc...
> 2) build a table to store the data
> 3) write trigger in each table that inserts new record in audit table for
> each row being modified...
> hth,
> Charles
> "AM" wrote:
> > Hi all gurus
> >
> > I want to create a store procedure which will generate an audit table and
> > track all the changes done in any of my tables in any database of my
> > server.
> >
> > Please guide me where to start
> >
> > Any guidance will be highly appreciated
> >
> > Thanks
> >
> >
> >|||Hi all
I cannot change anykind on tables , so I can not add triggers on the tables
Thanks
AM
"Enric" <Enric@.discussions.microsoft.com> wrote in message
news:D4198061-8BD2-47D5-B2F2-6D117618D84A@.microsoft.com...
> hi,
> You can add to the strategic or central production's tables fields such
as:
> -LastDML ('u': update, 'i': insert)
> -LastDMLuser (linked to tables as tblsysusers and tblsysrights)
>
> "CBretana" wrote:
> > You probably won;t want a stored proc, but a trigger on each of the
tables
> > you want to audit.. I believe there's a MS design pattern you can copy
foor
> > this. Check out MSDN... In general,
> > 1) decide what you want to record.. . some ideas are
> > Table, RecordIdentifier, Column, OldValue, NewValue, WHoChangedit,
> > ChangeDatetime, Operation(Insert/Update/Delete), etc...
> >
> > 2) build a table to store the data
> > 3) write trigger in each table that inserts new record in audit table
for
> > each row being modified...
> >
> > hth,
> > Charles
> >
> > "AM" wrote:
> >
> > > Hi all gurus
> > >
> > > I want to create a store procedure which will generate an audit table
and
> > > track all the changes done in any of my tables in any database of my
> > > server.
> > >
> > > Please guide me where to start
> > >
> > > Any guidance will be highly appreciated
> > >
> > > Thanks
> > >
> > >
> > >|||Then you will need to intercept the Stored Proc(s) that are inserting,
updating, or deleting record sfrom these tables and put the same code, more
or less in there, understanding that any changes made to the tables
directly, or from another as yet unwritten SP< will not be audited...
"AM" wrote:
> Hi all
> I cannot change anykind on tables , so I can not add triggers on the tables
> Thanks
> AM
> "Enric" <Enric@.discussions.microsoft.com> wrote in message
> news:D4198061-8BD2-47D5-B2F2-6D117618D84A@.microsoft.com...
> > hi,
> > You can add to the strategic or central production's tables fields such
> as:
> >
> > -LastDML ('u': update, 'i': insert)
> > -LastDMLuser (linked to tables as tblsysusers and tblsysrights)
> >
> >
> > "CBretana" wrote:
> >
> > > You probably won;t want a stored proc, but a trigger on each of the
> tables
> > > you want to audit.. I believe there's a MS design pattern you can copy
> foor
> > > this. Check out MSDN... In general,
> > > 1) decide what you want to record.. . some ideas are
> > > Table, RecordIdentifier, Column, OldValue, NewValue, WHoChangedit,
> > > ChangeDatetime, Operation(Insert/Update/Delete), etc...
> > >
> > > 2) build a table to store the data
> > > 3) write trigger in each table that inserts new record in audit table
> for
> > > each row being modified...
> > >
> > > hth,
> > > Charles
> > >
> > > "AM" wrote:
> > >
> > > > Hi all gurus
> > > >
> > > > I want to create a store procedure which will generate an audit table
> and
> > > > track all the changes done in any of my tables in any database of my
> > > > server.
> > > >
> > > > Please guide me where to start
> > > >
> > > > Any guidance will be highly appreciated
> > > >
> > > > Thanks
> > > >
> > > >
> > > >
>
>|||This may be an option.
http://www.lumigent.com/
"AM" <anonymous@.extraquest.com> wrote in message
news:ufPXUHUOFHA.1176@.TK2MSFTNGP12.phx.gbl...
> Hi all
> I cannot change anykind on tables , so I can not add triggers on the
tables
> Thanks
> AM
> "Enric" <Enric@.discussions.microsoft.com> wrote in message
> news:D4198061-8BD2-47D5-B2F2-6D117618D84A@.microsoft.com...
> > hi,
> > You can add to the strategic or central production's tables fields such
> as:
> >
> > -LastDML ('u': update, 'i': insert)
> > -LastDMLuser (linked to tables as tblsysusers and tblsysrights)
> >
> >
> > "CBretana" wrote:
> >
> > > You probably won;t want a stored proc, but a trigger on each of the
> tables
> > > you want to audit.. I believe there's a MS design pattern you can
copy
> foor
> > > this. Check out MSDN... In general,
> > > 1) decide what you want to record.. . some ideas are
> > > Table, RecordIdentifier, Column, OldValue, NewValue, WHoChangedit,
> > > ChangeDatetime, Operation(Insert/Update/Delete), etc...
> > >
> > > 2) build a table to store the data
> > > 3) write trigger in each table that inserts new record in audit table
> for
> > > each row being modified...
> > >
> > > hth,
> > > Charles
> > >
> > > "AM" wrote:
> > >
> > > > Hi all gurus
> > > >
> > > > I want to create a store procedure which will generate an audit
table
> and
> > > > track all the changes done in any of my tables in any database of
my
> > > > server.
> > > >
> > > > Please guide me where to start
> > > >
> > > > Any guidance will be highly appreciated
> > > >
> > > > Thanks
> > > >
> > > >
> > > >
>|||Thanks
"Terri" <terri@.cybernets.com> wrote in message
news:d2s32a$tkr$1@.reader2.nmix.net...
> This may be an option.
> http://www.lumigent.com/
>
> "AM" <anonymous@.extraquest.com> wrote in message
> news:ufPXUHUOFHA.1176@.TK2MSFTNGP12.phx.gbl...
> > Hi all
> >
> > I cannot change anykind on tables , so I can not add triggers on the
> tables
> >
> > Thanks
> > AM
> > "Enric" <Enric@.discussions.microsoft.com> wrote in message
> > news:D4198061-8BD2-47D5-B2F2-6D117618D84A@.microsoft.com...
> > > hi,
> > > You can add to the strategic or central production's tables fields
such
> > as:
> > >
> > > -LastDML ('u': update, 'i': insert)
> > > -LastDMLuser (linked to tables as tblsysusers and tblsysrights)
> > >
> > >
> > > "CBretana" wrote:
> > >
> > > > You probably won;t want a stored proc, but a trigger on each of the
> > tables
> > > > you want to audit.. I believe there's a MS design pattern you can
> copy
> > foor
> > > > this. Check out MSDN... In general,
> > > > 1) decide what you want to record.. . some ideas are
> > > > Table, RecordIdentifier, Column, OldValue, NewValue,
WHoChangedit,
> > > > ChangeDatetime, Operation(Insert/Update/Delete), etc...
> > > >
> > > > 2) build a table to store the data
> > > > 3) write trigger in each table that inserts new record in audit
table
> > for
> > > > each row being modified...
> > > >
> > > > hth,
> > > > Charles
> > > >
> > > > "AM" wrote:
> > > >
> > > > > Hi all gurus
> > > > >
> > > > > I want to create a store procedure which will generate an audit
> table
> > and
> > > > > track all the changes done in any of my tables in any database of
> my
> > > > > server.
> > > > >
> > > > > Please guide me where to start
> > > > >
> > > > > Any guidance will be highly appreciated
> > > > >
> > > > > Thanks
> > > > >
> > > > >
> > > > >
> >
> >
>

audit log

Are there any sys tables that store audit logs for
commands such as sp_changeobjectowner? Is this a feature
that can be turned on? If so where?
Thanks in advance :-)
TylerTyler,
Check the Profiler tool. It comes with SQL Server and is described in Books
OnLine. You can check some 3rd party tools as well, for example check
www.lumigent.com.
--
Dejan Sarka, SQL Server MVP
FAQ from Neil & others at: http://www.sqlserverfaq.com
Please reply only to the newsgroups.
PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"tyler" <e_tyler@.netzero.net> wrote in message
news:0cc801c35c2b$5e5edc10$a501280a@.phx.gbl...
> Are there any sys tables that store audit logs for
> commands such as sp_changeobjectowner? Is this a feature
> that can be turned on? If so where?
> Thanks in advance :-)
> Tyler

Attribute Spliting? Design Question

I need to store address information in the database for validating
insurance risks. The problem with storing is that the information is
hierarchical and is also dependent on the territory. UK Postcode system
has four levels of hierarchy (Area, District, Sector and Unit), French
postcode system has two levels and so on. A territory can support more
than one address system ( like UK can also have a standard street
address comprising county, city and street name or the postcode system
which just consists one alphanumeric postcode).
1) To store the address information I am planning to restrict the
number of levels in the hierarchy to four and have four different
lookup tables that hold hierarchical data.
2) The other possibility is to hold all the information in just one
table with a self-referencing key called parent that will provide the
hierarchy. This table can become huge as we are planning to support a
number of territories.
Which approach is better? Having different tables for levels or one
table with all the levels? Also does method 1 constitute attribute
splitting?
ThanksHi S Chapman,
From my own background is more common to have that kind of information in a
separate tables. (Spain)
Please post DDL, DCL and DML statements as well as any error message in
order to understand better your request. It''''s hard to provide information
without seeing the code. location: Alicante (ES)
"S Chapman" wrote:

> I need to store address information in the database for validating
> insurance risks. The problem with storing is that the information is
> hierarchical and is also dependent on the territory. UK Postcode system
> has four levels of hierarchy (Area, District, Sector and Unit), French
> postcode system has two levels and so on. A territory can support more
> than one address system ( like UK can also have a standard street
> address comprising county, city and street name or the postcode system
> which just consists one alphanumeric postcode).
> 1) To store the address information I am planning to restrict the
> number of levels in the hierarchy to four and have four different
> lookup tables that hold hierarchical data.
> 2) The other possibility is to hold all the information in just one
> table with a self-referencing key called parent that will provide the
> hierarchy. This table can become huge as we are planning to support a
> number of territories.
> Which approach is better? Having different tables for levels or one
> table with all the levels? Also does method 1 constitute attribute
> splitting?
> Thanks
>|||On 24 Apr 2006 01:19:22 -0700, S Chapman wrote:

>I need to store address information in the database for validating
>insurance risks. The problem with storing is that the information is
>hierarchical and is also dependent on the territory. UK Postcode system
>has four levels of hierarchy (Area, District, Sector and Unit), French
>postcode system has two levels and so on.
Hi S,
Here, you need to ask yourself if the hierarchy levels in the postcode
systems are relevant for your application. What attributes that are
specific to e.g. the District or Unit level do you need to store? Why
are these same attributes not relevant for an address in France?
Maybe yoou are better off just storing the postcode as one column and
forget about the levels encoded in the postcode - they are designed for
the mail sorting centres, not for insurance risk assessment.

> A territory can support more
>than one address system ( like UK can also have a standard street
>address comprising county, city and street name or the postcode system
>which just consists one alphanumeric postcode).
>1) To store the address information I am planning to restrict the
>number of levels in the hierarchy to four and have four different
>lookup tables that hold hierarchical data.
>2) The other possibility is to hold all the information in just one
>table with a self-referencing key called parent that will provide the
>hierarchy. This table can become huge as we are planning to support a
>number of territories.
>Which approach is better? Having different tables for levels or one
>table with all the levels?
This is impossible to answer. I would need to know lots more about the
data you are dealing with - and even then, a gooo advice might not be
possible. Newsgroups are not a good channel for design advise.

>Also does method 1 constitute attribute
>splitting?
No. *IF* Area, District, Sector and Unit are all relevant entities in
your database, each with its own set of attributes, then you MUST have a
table for each of those entities. Even if they are not encoded in the
post code.
Hugo Kornelis, SQL Server MVP|||If you are doing this for bulk mailings, I would put each country in a
table and add different constraints to those tables postal codes. Then
create a UNION-ed VIEW if needed.

Attachment using extendet store procedures

I am trying to send SMTP email with attachment using cdosysmail and getting
error on this line:
EXEC @.hr = sp_OAMethod @.MailID, 'AddAttachment', NULL, @.FileSource
Error message:
Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command. The results, if any, should
be discarded.
this store procedure is working on SQL 2000.
any help please.Perhaps @.FileSource is not a valid path/file name or the SQL Server process
does not have permissions to that resource.
"viktor" <serguienkov@.hotmail.com> wrote in message news:O4uh44XOGHA.3732@.TK
2MSFTNGP10.phx.gbl...
I am trying to send SMTP email with attachment using cdosysmail and getting
error on this line:
EXEC @.hr = sp_OAMethod @.MailID, 'AddAttachment', NULL, @.FileSource
Error message:
Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command. The results, if any, should
be discarded.
this store procedure is working on SQL 2000.
any help please.|||the same procedure work fine on SQL 2000 permission is not the case
"JT" <someone@.microsoft.com> wrote in message
news:OayLaIYOGHA.1676@.TK2MSFTNGP09.phx.gbl...
Perhaps @.FileSource is not a valid path/file name or the SQL Server process
does not have permissions to that resource.
"viktor" <serguienkov@.hotmail.com> wrote in message
news:O4uh44XOGHA.3732@.TK2MSFTNGP10.phx.gbl...
I am trying to send SMTP email with attachment using cdosysmail and
getting error on this line:
EXEC @.hr = sp_OAMethod @.MailID, 'AddAttachment', NULL, @.FileSource
Error message:
Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command. The results, if any,
should be discarded.
this store procedure is working on SQL 2000.
any help please.