Board index » delphi » How to check for rights in a SP (MSSQL)

How to check for rights in a SP (MSSQL)

Hello

I'm doing a manual backup in a stored proc.

How do I check if I have permission to do this?
The stored proc will fail if I do not have permissions,
so I need to skip the backup in this case.

Thanks
--
Jacob Pedersen
AK Techotel
J.Peder...@nospamtechotel.dk
(Remove nospam when replying!)

 

Re:How to check for rights in a SP (MSSQL)


In response to both of your questions... I think you're trying to go about
this in a wrong way. Backups are not client job and should not be performed
from there. If you insist, you'll run into problems you cannot possibly
anticipate before hand.
My advice is to create appropriate backup job on the server (enterprise
manager comes to mind) and using SQL Agent, create a schedule for backups.
If you need something special, create special jobs on the server and teach
one of the users (tech support?) to do it. I must add though, that SQL
server backups are not all that usually meets the eye (or ear for that
matter). There's much more to them and one should be familiar with a thing
or two before one even tries to do backups.

rb

Quote
"Jacob Pedersen" <J.Peder...@techotel.dk> wrote in message

news:Xns919F6C6F74B50jpedersentechotel@207.105.83.65...
Quote
> Hello

> I'm doing a manual backup in a stored proc.

> How do I check if I have permission to do this?
> The stored proc will fail if I do not have permissions,
> so I need to skip the backup in this case.

> Thanks
> --
> Jacob Pedersen
> AK Techotel
> J.Peder...@nospamtechotel.dk
> (Remove nospam when replying!)

Re:How to check for rights in a SP (MSSQL)


"rb" <ra...@killspam-videotron.ca> wrote in news:3c4e940a$1_1@dnews:

Quote

> In response to both of your questions... I think you're trying to go
> about this in a wrong way. Backups are not client job and should not be
> performed from there. If you insist, you'll run into problems you
> cannot possibly anticipate before hand.
> My advice is to create appropriate backup job on the server (enterprise
> manager comes to mind) and using SQL Agent, create a schedule for
> backups. If you need something special, create special jobs on the
> server and teach one of the users (tech support?) to do it. I must add
> though, that SQL server backups are not all that usually meets the eye
> (or ear for that matter). There's much more to them and one should be
> familiar with a thing or two before one even tries to do backups.

> rb

We already have a 4 hour backup job set up.

But at night our system starts a batch job which does alot
of work on the database. If something goes wrong we need to
have a backup of the database from just before this batch job.
The problem might not be visible just after the batch job. The
customer might first notice this some time later.

With the 4 hour interval backup, there might be work done by
the user between last backup and start of batch job. This is
why it is needed and my question remains :)  But thanks for
your time and input anyways :)

--
Jacob Pedersen
AK Techotel
J.Peder...@nospamtechotel.dk
(Remove nospam when replying!)

Re:How to check for rights in a SP (MSSQL)


Isn't it a simple matter of assigning priviliges to the backup SP itself?

Rob

Quote
"Jacob Pedersen" <J.Peder...@techotel.dk> wrote in message

news:Xns919F6C6F74B50jpedersentechotel@207.105.83.65...
Quote
> Hello

> I'm doing a manual backup in a stored proc.

> How do I check if I have permission to do this?
> The stored proc will fail if I do not have permissions,
> so I need to skip the backup in this case.

> Thanks
> --
> Jacob Pedersen
> AK Techotel
> J.Peder...@nospamtechotel.dk
> (Remove nospam when replying!)

Re:How to check for rights in a SP (MSSQL)


Quote
"Jacob Pedersen" <J.Peder...@techotel.dk> wrote in message >
> We already have a 4 hour backup job set up.

> But at night our system starts a batch job which does alot
> of work on the database. If something goes wrong we need to
> have a backup of the database from just before this batch job.
> The problem might not be visible just after the batch job. The
> customer might first notice this some time later.

> With the 4 hour interval backup, there might be work done by
> the user between last backup and start of batch job. This is

I still don't understand why do you have to do a manual backup, nor when
would you start it in this scenario. I know you can set up a scheduled job
on SQL Server that consists of many steps. For each job you can set the next
step/action based on its outcome. Those steps don't have to be backup only.
They can be anything. Which means that you could create a job that starts
the backup; if successful, run 4 hour batch; if successful, run a backup
again, or whatever is scenario you need.

The other possibility is to give a certain user enough rights to do the
backup. IMHO, that wouldn't be exactly prudent, but I suppose it's not
always possible to exercise prudent options only. Either way, backup is one
of the most important functions of a database server and shouldn't be left
opened to "ordinary" users.

rb

Re:How to check for rights in a SP (MSSQL)


"rb" <ra...@killspam-videotron.ca> wrote in news:3c4f893f$1_1@dnews:

Quote
> I still don't understand why do you have to do a manual backup, nor
> when would you start it in this scenario. I know you can set up a
> scheduled job on SQL Server that consists of many steps. For each job
> you can set the next step/action based on its outcome. Those steps
> don't have to be backup only. They can be anything. Which means that
> you could create a job that starts the backup; if successful, run 4
> hour batch; if successful, run a backup again, or whatever is scenario
> you need.

> The other possibility is to give a certain user enough rights to do the
> backup. IMHO, that wouldn't be exactly prudent, but I suppose it's not
> always possible to exercise prudent options only. Either way, backup is
> one of the most important functions of a database server and shouldn't
> be left opened to "ordinary" users.

> rb

Question :)

Can I execute a job from a stored procedure or my delphi code.

The ordinary user starts this program which then runs my stored proc
which does the backup. This is done once per day at any time. This
can differ from customer to customer and day to day. That's why it'
s not good enough if my auto backup job starts at 0 and 4, but the
user runs this batch job at 2. All work from 0 to 2, will not be in
the backup, and if I have to go back to look at data from right before
the batch job, I will have a problem. So I absolutely need to manually
start the backup when I need to.

--
Jacob Pedersen
AK Techotel
J.Peder...@nospamtechotel.dk
(Remove nospam when replying!)

Re:How to check for rights in a SP (MSSQL)


"Robby Tanner" <rtan...@cls.usask.ca> wrote in news:3c4ee793$1_1@dnews:

Quote
> Isn't it a simple matter of assigning priviliges to the backup SP itself?

> Rob

You tell me. Sadly I know nothing about these thing.

But still if I call the SP from Delphi and there aren'r
rights, then wouldn't it cause an exception?

--
Jacob Pedersen
AK Techotel
J.Peder...@nospamtechotel.dk
(Remove nospam when replying!)

Re:How to check for rights in a SP (MSSQL)


Quote
"Jacob Pedersen" <J.Peder...@techotel.dk> wrote in message

news:Xns91A05D8B88B65jpedersentechotel@207.105.83.65...

Quote
> Can I execute a job from a stored procedure or my delphi code.

Sure you can. Frankly, I thought you've been doing this already.

Quote
> The ordinary user starts this program which then runs my stored proc
> which does the backup. This is done once per day at any time. This
> can differ from customer to customer and day to day. That's why it'
> s not good enough if my auto backup job starts at 0 and 4, but the
> user runs this batch job at 2. All work from 0 to 2, will not be in
> the backup, and if I have to go back to look at data from right before
> the batch job, I will have a problem. So I absolutely need to manually
> start the backup when I need to.

And the option not to allow batch to bu run between hour and hour is
allowed? That shouldn't be a case. Backups don't last that long. The length
of backup obviously depends on the size of a database, but they also depend
on the hardware. On a DDS4, a 10GB database backs up in under half an hour.
Half an hour! It cannot be problem to block access or batch job for 30
minutes. Do businesses always have to experience how it feels when they
loose day or two worth of data? After such an incident, backups turn up on
Company's Policies document and become one of "don't f... with this".

Back to backup. SQL Server allows you to do on-line backups with certain
limitations. Here's a paragraph from BOL:
SQL Server uses online backup to allow a database backup while the database
is still in use. However, here are some operations that are not allowed
during a database or transaction log backup:

  a.. File management operations such as the ALTER DATABASE statement with
either the ADD FILE or REMOVE FILE options; INSERT, UPDATE, or DELETE
statements are allowed during a backup operation.
  b.. Shrink database or shrink file. This includes autoshrink operations.
  c.. CREATE INDEX. This restriction applies to database backup, not log
backup.
  d.. Nonlogged operations such as bulk load, SELECT INTO, WRITETEXT, and
UPDATETEXT. The database option select into/bulkcopy must be enabled for
these operations to be nonlogged.
If a backup is started when one of these operations is in progress, the
backup ends. If a backup is running and one of these operations is
attempted, the operation fails.

....

Anyhow, I wouldn't do backups the way you plan to in million years. Good
luck.

rb

Re:How to check for rights in a SP (MSSQL)


"rb" <ra...@killspam-videotron.ca> wrote in news:3c500010$1_2@dnews:

Quote
> And the option not to allow batch to bu run between hour and hour is
> allowed? That shouldn't be a case. Backups don't last that long. The
> length of backup obviously depends on the size of a database, but they
> also depend on the hardware. On a DDS4, a 10GB database backs up in
> under half an hour. Half an hour! It cannot be problem to block access
> or batch job for 30 minutes. Do businesses always have to experience
> how it feels when they loose day or two worth of data? After such an
> incident, backups turn up on Company's Policies document and become one
> of "don't f... with this".

You misunderstand. The batch is started at any given time. SQL server
maintenance plan is run at 4 hour interval regardless of a batch job is
being run by a user. Other users applications are run, but not while
batch is running.

Batch starts out with reasuring that no user is using the database.
Then it proceeds to do a manual backup. Then it does it's job and
then it exits allowing users to start using the system again.

Quote
> Back to backup. SQL Server allows you to do on-line backups with
> certain limitations. Here's a paragraph from BOL:
> SQL Server uses online backup to allow a database backup while the
> database is still in use. However, here are some operations that are
> not allowed during a database or transaction log backup:

>   a.. File management operations such as the ALTER DATABASE statement
>   with
> either the ADD FILE or REMOVE FILE options; INSERT, UPDATE, or DELETE
> statements are allowed during a backup operation.
>   b.. Shrink database or shrink file. This includes autoshrink
>   operations. c.. CREATE INDEX. This restriction applies to database
>   backup, not log
> backup.
>   d.. Nonlogged operations such as bulk load, SELECT INTO, WRITETEXT,
>   and
> UPDATETEXT. The database option select into/bulkcopy must be enabled
> for these operations to be nonlogged.
> If a backup is started when one of these operations is in progress, the
> backup ends. If a backup is running and one of these operations is
> attempted, the operation fails.

All we do throughout the day is Insert, Update and Delete.

Quote
> Anyhow, I wouldn't do backups the way you plan to in million years.
> Good luck.

Thanks, but my question remains as I do not see any other way to do this...

--
Jacob Pedersen
AK Techotel
J.Peder...@nospamtechotel.dk
(Remove nospam when replying!)

Re:How to check for rights in a SP (MSSQL)


Yup, and you can catch that exception and tell the user they aren't
authorized to do backups.
Is that what you're after?

Rob

Quote
"Jacob Pedersen" <J.Peder...@techotel.dk> wrote in message

news:Xns91A083BA7FD01jpedersentechotel@207.105.83.65...
Quote
> "Robby Tanner" <rtan...@cls.usask.ca> wrote in news:3c4ee793$1_1@dnews:

> > Isn't it a simple matter of assigning priviliges to the backup SP
itself?

> > Rob

> You tell me. Sadly I know nothing about these thing.

> But still if I call the SP from Delphi and there aren'r
> rights, then wouldn't it cause an exception?

> --
> Jacob Pedersen
> AK Techotel
> J.Peder...@nospamtechotel.dk
> (Remove nospam when replying!)

Re:How to check for rights in a SP (MSSQL)


Quote
"Jacob Pedersen" <J.Peder...@techotel.dk> wrote in message

news:Xns91A083BA7FD01jpedersentechotel@207.105.83.65...

Quote
> "Robby Tanner" <rtan...@cls.usask.ca> wrote in news:3c4ee793$1_1@dnews:

> > Isn't it a simple matter of assigning priviliges to the backup SP
itself?

> > Rob

> You tell me. Sadly I know nothing about these thing.

> But still if I call the SP from Delphi and there aren'r
> rights, then wouldn't it cause an exception?

To throw my 2 cents in, the way that SQL Server handles stored procedure
rights is different from the way it handles tables. For a table, you can set
each table to allow insert, update, etc. For a stored procedure, you can
only set execute. What this means is that the permissions are based on the
owner of the stored procedure. You could (I'm sure noone ever would do this,
though) allow a guest account access to the DB, but no permissions into any
tables, yet allow them access to a stored procedure that will delete all
tables in the database. As long as the owner of the stored proc has the
rights, this will kill em all.

And yes, when you try and run the proc, you'll get an exception if the user
can't do it.

On another point, how is your backup job set up? Based on the thread with
rb, it looks like you do a full db backup every 4 hours? This seems like a
bit of an over kill. I'd probably use a maintenance plan (like he suggests)
that backs up the full db at specified intervals (can't be more specific
without knowing your schedule), with more periodic log backups. This gives
you the ability to restore the db to any of the log backups start time. If
you noticed that something had gone wrong, but wasn't caught for a while,
you could put the db back to how is was before the error. Depending on your
db size, amount of updates, processor, drive speed, phase of the moon, etc,
you could set up a schedule that allow for as many log backups as you want.
I usually use 1 hour increments on my clients. This allows them to recover
in the event of a crash to within 1 hour of the crash.

Mike Walsh

Re:How to check for rights in a SP (MSSQL)


"Mike Walsh" <te...@msllib.com> wrote in news:3c5078c7_2@dnews:

 yes, when you try and run the proc, you'll get an exception if the

Quote
> user can't do it.

> On another point, how is your backup job set up? Based on the thread
> with rb, it looks like you do a full db backup every 4 hours? This
> seems like a bit of an over kill. I'd probably use a maintenance plan
> (like he suggests) that backs up the full db at specified intervals
> (can't be more specific without knowing your schedule), with more
> periodic log backups. This gives you the ability to restore the db to
> any of the log backups start time. If you noticed that something had
> gone wrong, but wasn't caught for a while, you could put the db back to
> how is was before the error. Depending on your db size, amount of
> updates, processor, drive speed, phase of the moon, etc, you could set
> up a schedule that allow for as many log backups as you want. I usually
> use 1 hour increments on my clients. This allows them to recover in the
> event of a crash to within 1 hour of the crash.

> Mike Walsh

Actually it is a full backup. Don't know why this was chosen. I guess I
would set it up as differential if I was doing it. It sure takes up a
lot of space as it is now.

I think I will do a try except and see what I can do with the exception.

Thanks!

--
Jacob Pedersen
AK Techotel
J.Peder...@nospamtechotel.dk
(Remove nospam when replying!)

Re:How to check for rights in a SP (MSSQL)


"Robby Tanner" <rtan...@cls.usask.ca> wrote in news:3c505c62_2@dnews:

Quote
> Yup, and you can catch that exception and tell the user they aren't
> authorized to do backups.
> Is that what you're after?

I just wanted to do it in a better way than try except, but I guess it
will do the trick.
I still have to figure out how to create the folder if it does not exist...
And no I cannot do this from delphi as the app might be called from a
client
and not the actual sql server.

--
Jacob Pedersen
AK Techotel
J.Peder...@nospamtechotel.dk
(Remove nospam when replying!)

Other Threads