Rights

I have a basic question regarding rights. What level of rights do I
have to have to grant another user update rights? I don't want to
give everyone owner rights. Can a person with update rights grant
another person update rights?

Thanks."Chris_M" <cmcclendon@.houston.rr.com> wrote in message
news:d6cef4db.0311260956.5893b978@.posting.google.c om...
> I have a basic question regarding rights. What level of rights do I
> have to have to grant another user update rights? I don't want to
> give everyone owner rights. Can a person with update rights grant
> another person update rights?
> Thanks.

You can grant a user update rights per table (and per column) if necessary.
If you specify WITH GRANT OPTION, then that user can grant the update right
to other users. There are examples in Books Online in the syntax for GRANT.

A general best practice is to manage permissions with roles, not by
individual user, as it makes things a lot easier. Also have a look at the
built-in roles, such as db_datareader and db_datawriter, which can be
useful.

In general, though, it's considered better to allow data access only through
stored procedures, if this is possible in your situation. This means that
users never require update permissions on tables, they only have execute
permissions on the procedures. That gives you more flexibility, as you can
add logic to the procedure for more control. Check out "Using Ownership
Chains" in Books Online for more details on how this works.

Simon

Revert to checkpoint

Hi,
Our application has an automatic software update feature.
We want to create a checkpoint (certain point in time) in the database
in order to revert to this checkpoint when somewhere in the software
update sequence error(s) occur...
Is this possible in MSSQL?
Transaction blocks are not possible because queries are executed from
different parts of our code...
Thanks in advance,
Greetz,
R. Oosterholt
Hi
Your code sounds to be a nightmare especially as this is a software upgrade.
I suggest that you backup the database at given points so that you can
restore to a known point, or instigate a mechanism that you can log the times
when you can recover to a known point in time.
John
"r.oosterholt@.gmail.com" wrote:

> Hi,
> Our application has an automatic software update feature.
> We want to create a checkpoint (certain point in time) in the database
> in order to revert to this checkpoint when somewhere in the software
> update sequence error(s) occur...
> Is this possible in MSSQL?
> Transaction blocks are not possible because queries are executed from
> different parts of our code...
> Thanks in advance,
> Greetz,
> R. Oosterholt
>
|||r.oosterholt@.gmail.com wrote:
> Hi,
> Our application has an automatic software update feature.
> We want to create a checkpoint (certain point in time) in the database
> in order to revert to this checkpoint when somewhere in the software
> update sequence error(s) occur...
> Is this possible in MSSQL?
> Transaction blocks are not possible because queries are executed from
> different parts of our code...
> Thanks in advance,
> Greetz,
> R. Oosterholt
>
If you're doing regular transaction log backups, you can restore a
database to any point in time using the STOPAT clause on the RESTORE
command...
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||<r.oosterholt@.gmail.com> wrote in message
news:1163779835.179635.247540@.b28g2000cwb.googlegr oups.com...
> Hi,
> Our application has an automatic software update feature.
> We want to create a checkpoint (certain point in time) in the database
> in order to revert to this checkpoint when somewhere in the software
> update sequence error(s) occur...
> Is this possible in MSSQL?
> Transaction blocks are not possible because queries are executed from
> different parts of our code...
>
Can you just take a full database backup, and restore the backup on failure?
Also on Enterprise Edition, you can take a Database Snapshot, and restore
from that.
David
|||Thanks all!
Snapshot is the best option for us.
Pity it is only available in 2005 EE...
Rick O.
On 17 nov, 17:25, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:[vbcol=seagreen]
> One option could be to set a log marker (BAGINF TRAN ... WITH MARK) so you later can restore to that
> point when you restore a transaction log backup. Assumes a sound backup strategy, of course.
> --
> Tibor Karaszi, SQL Server MVPhttp://www.karaszi.com/sqlserver/default.asphttp://www.solidqualitylearning.com/
> <r.oosterh...@.gmail.com> wrote in messagenews:1163779835.179635.247540@.b28g2000cwb.g ooglegroups.com...
>
>
>
>

Revert to checkpoint

Hi,
Our application has an automatic software update feature.
We want to create a checkpoint (certain point in time) in the database
in order to revert to this checkpoint when somewhere in the software
update sequence error(s) occur...
Is this possible in MSSQL?
Transaction blocks are not possible because queries are executed from
different parts of our code...
Thanks in advance,
Greetz,
R. OosterholtHi
Your code sounds to be a nightmare especially as this is a software upgrade.
I suggest that you backup the database at given points so that you can
restore to a known point, or instigate a mechanism that you can log the times
when you can recover to a known point in time.
John
"r.oosterholt@.gmail.com" wrote:
> Hi,
> Our application has an automatic software update feature.
> We want to create a checkpoint (certain point in time) in the database
> in order to revert to this checkpoint when somewhere in the software
> update sequence error(s) occur...
> Is this possible in MSSQL?
> Transaction blocks are not possible because queries are executed from
> different parts of our code...
> Thanks in advance,
> Greetz,
> R. Oosterholt
>|||r.oosterholt@.gmail.com wrote:
> Hi,
> Our application has an automatic software update feature.
> We want to create a checkpoint (certain point in time) in the database
> in order to revert to this checkpoint when somewhere in the software
> update sequence error(s) occur...
> Is this possible in MSSQL?
> Transaction blocks are not possible because queries are executed from
> different parts of our code...
> Thanks in advance,
> Greetz,
> R. Oosterholt
>
If you're doing regular transaction log backups, you can restore a
database to any point in time using the STOPAT clause on the RESTORE
command...
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||<r.oosterholt@.gmail.com> wrote in message
news:1163779835.179635.247540@.b28g2000cwb.googlegroups.com...
> Hi,
> Our application has an automatic software update feature.
> We want to create a checkpoint (certain point in time) in the database
> in order to revert to this checkpoint when somewhere in the software
> update sequence error(s) occur...
> Is this possible in MSSQL?
> Transaction blocks are not possible because queries are executed from
> different parts of our code...
>
Can you just take a full database backup, and restore the backup on failure?
Also on Enterprise Edition, you can take a Database Snapshot, and restore
from that.
David|||One option could be to set a log marker (BAGINF TRAN ... WITH MARK) so you later can restore to that
point when you restore a transaction log backup. Assumes a sound backup strategy, of course.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<r.oosterholt@.gmail.com> wrote in message
news:1163779835.179635.247540@.b28g2000cwb.googlegroups.com...
> Hi,
> Our application has an automatic software update feature.
> We want to create a checkpoint (certain point in time) in the database
> in order to revert to this checkpoint when somewhere in the software
> update sequence error(s) occur...
> Is this possible in MSSQL?
> Transaction blocks are not possible because queries are executed from
> different parts of our code...
> Thanks in advance,
> Greetz,
> R. Oosterholt
>|||Thanks all!
Snapshot is the best option for us.
Pity it is only available in 2005 EE...
Rick O.
On 17 nov, 17:25, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> One option could be to set a log marker (BAGINF TRAN ... WITH MARK) so you later can restore to that
> point when you restore a transaction log backup. Assumes a sound backup strategy, of course.
> --
> Tibor Karaszi, SQL Server MVPhttp://www.karaszi.com/sqlserver/default.asphttp://www.solidqualitylearning.com/
> <r.oosterh...@.gmail.com> wrote in messagenews:1163779835.179635.247540@.b28g2000cwb.googlegroups.com...
>
> > Hi,
> > Our application has an automatic software update feature.
> > We want to create a checkpoint (certain point in time) in the database
> > in order to revert to this checkpoint when somewhere in the software
> > update sequence error(s) occur...
> > Is this possible in MSSQL?
> > Transaction blocks are not possible because queries are executed from
> > different parts of our code...
> > Thanks in advance,
> > Greetz,
> > R. Oosterholt- Tekst uit oorspronkelijk bericht niet weergeven -- Tekst uit oorspronkelijk bericht weergeven -

Revert to checkpoint

Hi,
Our application has an automatic software update feature.
We want to create a checkpoint (certain point in time) in the database
in order to revert to this checkpoint when somewhere in the software
update sequence error(s) occur...
Is this possible in MSSQL?
Transaction blocks are not possible because queries are executed from
different parts of our code...
Thanks in advance,
Greetz,
R. OosterholtHi
Your code sounds to be a nightmare especially as this is a software upgrade.
I suggest that you backup the database at given points so that you can
restore to a known point, or instigate a mechanism that you can log the time
s
when you can recover to a known point in time.
John
"r.oosterholt@.gmail.com" wrote:

> Hi,
> Our application has an automatic software update feature.
> We want to create a checkpoint (certain point in time) in the database
> in order to revert to this checkpoint when somewhere in the software
> update sequence error(s) occur...
> Is this possible in MSSQL?
> Transaction blocks are not possible because queries are executed from
> different parts of our code...
> Thanks in advance,
> Greetz,
> R. Oosterholt
>|||One option could be to set a log marker (BAGINF TRAN ... WITH MARK) so you l
ater can restore to that
point when you restore a transaction log backup. Assumes a sound backup stra
tegy, of course.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<r.oosterholt@.gmail.com> wrote in message
news:1163779835.179635.247540@.b28g2000cwb.googlegroups.com...
> Hi,
> Our application has an automatic software update feature.
> We want to create a checkpoint (certain point in time) in the database
> in order to revert to this checkpoint when somewhere in the software
> update sequence error(s) occur...
> Is this possible in MSSQL?
> Transaction blocks are not possible because queries are executed from
> different parts of our code...
> Thanks in advance,
> Greetz,
> R. Oosterholt
>|||r.oosterholt@.gmail.com wrote:
> Hi,
> Our application has an automatic software update feature.
> We want to create a checkpoint (certain point in time) in the database
> in order to revert to this checkpoint when somewhere in the software
> update sequence error(s) occur...
> Is this possible in MSSQL?
> Transaction blocks are not possible because queries are executed from
> different parts of our code...
> Thanks in advance,
> Greetz,
> R. Oosterholt
>
If you're doing regular transaction log backups, you can restore a
database to any point in time using the STOPAT clause on the RESTORE
command...
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||<r.oosterholt@.gmail.com> wrote in message
news:1163779835.179635.247540@.b28g2000cwb.googlegroups.com...
> Hi,
> Our application has an automatic software update feature.
> We want to create a checkpoint (certain point in time) in the database
> in order to revert to this checkpoint when somewhere in the software
> update sequence error(s) occur...
> Is this possible in MSSQL?
> Transaction blocks are not possible because queries are executed from
> different parts of our code...
>
Can you just take a full database backup, and restore the backup on failure?
Also on Enterprise Edition, you can take a Database Snapshot, and restore
from that.
David|||Thanks all!
Snapshot is the best option for us.
Pity it is only available in 2005 EE...
Rick O.
On 17 nov, 17:25, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:[vbcol=seagreen]
> One option could be to set a log marker (BAGINF TRAN ... WITH MARK) so you
later can restore to that
> point when you restore a transaction log backup. Assumes a sound backup st
rategy, of course.
> --
> Tibor Karaszi, SQL Server MVPhttp://www.karaszi.com/sqlserver/default.asph
ttp://www.solidqualitylearning.com/
> <r.oosterh...@.gmail.com> wrote in messagenews:1163779835.179635.247540@.b28
g2000cwb.googlegroups.com...
>
>
>
>
>
>
>

Returning fields that have changed since last import

I have a few tables that update everyday with fresh data.
The contents of the archive table are truncated before the import starts, the current data is then copied to the archive table and then truncated ready for the fresh data.

so I have two tables, one with current data, one with yesterdays data. I need to write queries to show if any data has changed between the two.

CREATE TABLE [TblBond] (
[issuer] [varchar] (50) NOT NULL ,
[maturity_date] [datetime] NOT NULL ,
[coupon] [numeric](30, 10) NOT NULL ,
[currency] [char] (3) NOT NULL ,
[bond_type_code] [varchar] (12) NOT NULL ,
[sec_id] [int] NOT NULL ,
[otr] [int] NOT NULL ,
[identification_str] [varchar] (60) NULL ,
[description] [varchar] (30) NULL ,
[settlement_date] [datetime] NULL ,
[buy_sell] [varchar] (4) NOT NULL ,
[trade_amount] [numeric](38, 10) NOT NULL ,
[accrued_interest] [numeric](38, 10) NOT NULL ,
[remark] [varchar] (255) NOT NULL ,
[counterparty] [varchar] (20) NOT NULL ,
[booking_entity] [varchar] (20) NOT NULL ,
[keyword] [varchar] (255) NOT NULL ,
[trans_id] [int] NOT NULL ,
[trade_id] [int] NOT NULL ,
[trade_status] [varchar] (12) NULL ,
CONSTRAINT [PK_TblBondData] PRIMARY KEY CLUSTERED
(
[trade_id]
) WITH FILLFACTOR = 90 ON [PRIMARY]
) ON [PRIMARY]
GO

Trade_id is the PK, the archive table is the same structure but called TblBond_archive

I have got a query that works, but is very lengthy and will need amending if the columns change at all (and as im dealing with traders, im sure they will add/remove things!)

query is like dis :

SELECT
TblBond.trade_id,
'Issuer' AS Field_Changed,
cast(TblBond_Archive.Issuer as varchar) AS Old_Value,
cast(TblBond.Issuer as varchar) AS New_Value
FROM TblBond
INNER JOIN TblBond_Archive ON
TblBond.trade_id = TblBond_Archive.trade_id
WHERE TblBond.Issuer<>[tblbond_archive].[Issuer]

UNION

...... and goes onto next field. for another 19 columns!!

There must be a more eloquent way of coding this? anyone any ideas? im using SQL server 2000, so i do have some schema tables i can call on if needed.

Any ideas would be great as I have about 10 tables using similiar principals and tis a pain to code it all!!

RegardsThe by far easiest and fastest way for doing this would be the use of a timestamp column in your live table and an appropriate datetime in the archive table. It gets updated automatically on every update. You can get the updated records with a simple:

SELECT blablabla
FROM TblBond
INNER JOIN TblBond_Archive ON
TblBond.trade_id = TblBond_Archive.trade_id
WHERE TblBond.updated_timestamp > TblBond_Archive.updated_datetime|||Thanks Apel, in an ideal world this is what i would have done in the first place, but alas, this data is actually sourced from a rather orrible sybase system, which I can't make any DDL changes to, as all the sybase dev team where let go a few months back!!

so no go on the timestamp column! all I can do is import the tables from sybase into my sql server for the purposes of reporting.

Also that method wouldn't enable to keep an audit trail of what changes have happened to each trade. the way i described allows me to run that big mother query everytime data is imported and copy results to an audit table. Which stores the PK, fieldname, old value , new value , and the datetime the change was detected. I also tacked on 2 other union select queries to pick up new or deleted trades.

I ended up writing a wee bit of vba code (which i ran in MS access) to help me create the SQL code for the big query! worked nicely. but still think there must be a more eloquent way to code this.

Returning ERRORs from SP's

Hi all,
am using an access front end (XP) and ODBC. SQL Server 2k
When doing an update statement in Query Analyser, QA will tell me I have
violated a, eg. ForeignKey in table in database.
Is there a way to return that error description from a Stored Procedure (the
violation would be from a statement in the SP)? and therefore return it
to my front end.
thanksTake a look at RAISERROR and sysmessages system table
in Books on line
"SJ" wrote:

> Hi all,
> am using an access front end (XP) and ODBC. SQL Server 2k
> When doing an update statement in Query Analyser, QA will tell me I have
> violated a, eg. ForeignKey in table in database.
> Is there a way to return that error description from a Stored Procedure (t
he
> violation would be from a statement in the SP)? and therefore return i
t
> to my front end.
> thanks
>
>|||Wouldn't that be nice :) No, not in the 2k version of SQL Server. It is up
to the client to handle and deal with errors. If your program doesn't
cancel the batch, you can raise another error after the offending statement
so you can see where the error occurred though.
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Blog - http://spaces.msn.com/members/drsql/
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"SJ" <myocard@.hotmail.com> wrote in message
news:asq_d.10693$1S4.1124667@.news.xtra.co.nz...
> Hi all,
> am using an access front end (XP) and ODBC. SQL Server 2k
> When doing an update statement in Query Analyser, QA will tell me I have
> violated a, eg. ForeignKey in table in database.
> Is there a way to return that error description from a Stored Procedure
> (the violation would be from a statement in the SP)? and therefore
> return it to my front end.
> thanks
>