db files: sysadmin

Showing posts with label sysadmin. Show all posts

Wednesday, March 28, 2012

Running xp_cmdshell

Hi all,
Pls let me know how to run xp_cmdshell command for a user who does not belong to sysadmin role.
Rgds
Srinivas varanasiHi all,
Pls let me know how to run xp_cmdshell command for a user who does not belong to sysadmin role.
Rgds
Srinivas varanasi
the user should have execute permissions on the xtended procedure xp_cmdshell to run it .

Tuesday, February 21, 2012

Running SQL Jobs manually

Does anyone know why as a sysadmin I am unable to run jobs
manually? The SQL agent runs under my account and the jobs
run fine on schedule but when I try to run them manually
nothing happens and nothing is logged in Event Viewer.Silly question - Are you refreshing the jobs node to see what activity is
taking place?
Are you gettting any entries under "View job history" for the job you are
trying to run?
--
Simon Worth
"Robert" <anonymous@.discussions.microsoft.com> wrote in message
news:2f7d01c520e0$37410ba0$a501280a@.phx.gbl...
> Does anyone know why as a sysadmin I am unable to run jobs
> manually? The SQL agent runs under my account and the jobs
> run fine on schedule but when I try to run them manually
> nothing happens and nothing is logged in Event Viewer.|||Yes I am refreshing and no there is no history of the
attempts to run manually
>--Original Message--
>Silly question - Are you refreshing the jobs node to see
what activity is
>taking place?
>Are you gettting any entries under "View job history" for
the job you are
>trying to run?
>--
>Simon Worth
>
>"Robert" <anonymous@.discussions.microsoft.com> wrote in
message
>news:2f7d01c520e0$37410ba0$a501280a@.phx.gbl...
>> Does anyone know why as a sysadmin I am unable to run
jobs
>> manually? The SQL agent runs under my account and the
jobs
>> run fine on schedule but when I try to run them manually
>> nothing happens and nothing is logged in Event Viewer.
>
>.
>|||Did you try the stored proc 'sp_start_job' in query analyzer to see what the
output is or any error return values?
--
Simon Worth
<anonymous@.discussions.microsoft.com> wrote in message
news:2fa301c520e2$eb5b63e0$a501280a@.phx.gbl...
> Yes I am refreshing and no there is no history of the
> attempts to run manually
> >--Original Message--
> >Silly question - Are you refreshing the jobs node to see
> what activity is
> >taking place?
> >Are you gettting any entries under "View job history" for
> the job you are
> >trying to run?
> >
> >--
> >Simon Worth
> >
> >
> >"Robert" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:2f7d01c520e0$37410ba0$a501280a@.phx.gbl...
> >> Does anyone know why as a sysadmin I am unable to run
> jobs
> >> manually? The SQL agent runs under my account and the
> jobs
> >> run fine on schedule but when I try to run them manually
> >> nothing happens and nothing is logged in Event Viewer.
> >
> >
> >.
> >|||I ran the job using 'sp_start_job' and the message
said 'starting job successfully'. In EM when I refresh the
job it doesn't appear that the job ran. And I confirmed
that this particular job did not actually run.
>--Original Message--
>Did you try the stored proc 'sp_start_job' in query
analyzer to see what the
>output is or any error return values?
>--
>Simon Worth
>
><anonymous@.discussions.microsoft.com> wrote in message
>news:2fa301c520e2$eb5b63e0$a501280a@.phx.gbl...
>> Yes I am refreshing and no there is no history of the
>> attempts to run manually
>> >--Original Message--
>> >Silly question - Are you refreshing the jobs node to
see
>> what activity is
>> >taking place?
>> >Are you gettting any entries under "View job history"
for
>> the job you are
>> >trying to run?
>> >
>> >--
>> >Simon Worth
>> >
>> >
>> >"Robert" <anonymous@.discussions.microsoft.com> wrote in
>> message
>> >news:2f7d01c520e0$37410ba0$a501280a@.phx.gbl...
>> >> Does anyone know why as a sysadmin I am unable to run
>> jobs
>> >> manually? The SQL agent runs under my account and the
>> jobs
>> >> run fine on schedule but when I try to run them
manually
>> >> nothing happens and nothing is logged in Event
Viewer.
>> >
>> >
>> >.
>> >
>
>.
>|||what about in the SQL Server logs (you said you checked the event viewer
logs), are their any entries in there for failed jobs, or problems starting
jobs?
--
Simon Worth
<anonymous@.discussions.microsoft.com> wrote in message
news:43aa01c520ed$172250b0$a601280a@.phx.gbl...
> I ran the job using 'sp_start_job' and the message
> said 'starting job successfully'. In EM when I refresh the
> job it doesn't appear that the job ran. And I confirmed
> that this particular job did not actually run.
> >--Original Message--
> >Did you try the stored proc 'sp_start_job' in query
> analyzer to see what the
> >output is or any error return values?
> >
> >--
> >Simon Worth
> >
> >
> ><anonymous@.discussions.microsoft.com> wrote in message
> >news:2fa301c520e2$eb5b63e0$a501280a@.phx.gbl...
> >> Yes I am refreshing and no there is no history of the
> >> attempts to run manually
> >> >--Original Message--
> >> >Silly question - Are you refreshing the jobs node to
> see
> >> what activity is
> >> >taking place?
> >> >Are you gettting any entries under "View job history"
> for
> >> the job you are
> >> >trying to run?
> >> >
> >> >--
> >> >Simon Worth
> >> >
> >> >
> >> >"Robert" <anonymous@.discussions.microsoft.com> wrote in
> >> message
> >> >news:2f7d01c520e0$37410ba0$a501280a@.phx.gbl...
> >> >> Does anyone know why as a sysadmin I am unable to run
> >> jobs
> >> >> manually? The SQL agent runs under my account and the
> >> jobs
> >> >> run fine on schedule but when I try to run them
> manually
> >> >> nothing happens and nothing is logged in Event
> Viewer.
> >> >
> >> >
> >> >.
> >> >
> >
> >
> >.
> >|||Check the logs for SQL Agent as well to see if there is anything listed in
there.
--
Simon Worth
"Simon Worth" <REMOVEFIRST_simon.worth@.gmail.com> wrote in message
news:O21On$OIFHA.3628@.TK2MSFTNGP10.phx.gbl...
> what about in the SQL Server logs (you said you checked the event viewer
> logs), are their any entries in there for failed jobs, or problems
starting
> jobs?
> --
> Simon Worth
>
> <anonymous@.discussions.microsoft.com> wrote in message
> news:43aa01c520ed$172250b0$a601280a@.phx.gbl...
> > I ran the job using 'sp_start_job' and the message
> > said 'starting job successfully'. In EM when I refresh the
> > job it doesn't appear that the job ran. And I confirmed
> > that this particular job did not actually run.
> > >--Original Message--
> > >Did you try the stored proc 'sp_start_job' in query
> > analyzer to see what the
> > >output is or any error return values?
> > >
> > >--
> > >Simon Worth
> > >
> > >
> > ><anonymous@.discussions.microsoft.com> wrote in message
> > >news:2fa301c520e2$eb5b63e0$a501280a@.phx.gbl...
> > >> Yes I am refreshing and no there is no history of the
> > >> attempts to run manually
> > >> >--Original Message--
> > >> >Silly question - Are you refreshing the jobs node to
> > see
> > >> what activity is
> > >> >taking place?
> > >> >Are you gettting any entries under "View job history"
> > for
> > >> the job you are
> > >> >trying to run?
> > >> >
> > >> >--
> > >> >Simon Worth
> > >> >
> > >> >
> > >> >"Robert" <anonymous@.discussions.microsoft.com> wrote in
> > >> message
> > >> >news:2f7d01c520e0$37410ba0$a501280a@.phx.gbl...
> > >> >> Does anyone know why as a sysadmin I am unable to run
> > >> jobs
> > >> >> manually? The SQL agent runs under my account and the
> > >> jobs
> > >> >> run fine on schedule but when I try to run them
> > manually
> > >> >> nothing happens and nothing is logged in Event
> > Viewer.
> > >> >
> > >> >
> > >> >.
> > >> >
> > >
> > >
> > >.
> > >
>|||i did see one entry for my logon. Error: 15457, Severity:
0, State: 1
Configuration option 'show advanced options' changed from
1 to 1. Run the RECONFIGURE statement to install.
Do you know what this means?
>--Original Message--
>Check the logs for SQL Agent as well to see if there is
anything listed in
>there.
>--
>Simon Worth
>
>"Simon Worth" <REMOVEFIRST_simon.worth@.gmail.com> wrote
in message
>news:O21On$OIFHA.3628@.TK2MSFTNGP10.phx.gbl...
>> what about in the SQL Server logs (you said you checked
the event viewer
>> logs), are their any entries in there for failed jobs,
or problems
>starting
>> jobs?
>> --
>> Simon Worth
>>
>> <anonymous@.discussions.microsoft.com> wrote in message
>> news:43aa01c520ed$172250b0$a601280a@.phx.gbl...
>> > I ran the job using 'sp_start_job' and the message
>> > said 'starting job successfully'. In EM when I
refresh the
>> > job it doesn't appear that the job ran. And I
confirmed
>> > that this particular job did not actually run.
>> > >--Original Message--
>> > >Did you try the stored proc 'sp_start_job' in query
>> > analyzer to see what the
>> > >output is or any error return values?
>> > >
>> > >--
>> > >Simon Worth
>> > >
>> > >
>> > ><anonymous@.discussions.microsoft.com> wrote in
message
>> > >news:2fa301c520e2$eb5b63e0$a501280a@.phx.gbl...
>> > >> Yes I am refreshing and no there is no history of
the
>> > >> attempts to run manually
>> > >> >--Original Message--
>> > >> >Silly question - Are you refreshing the jobs node
to
>> > see
>> > >> what activity is
>> > >> >taking place?
>> > >> >Are you gettting any entries under "View job
history"
>> > for
>> > >> the job you are
>> > >> >trying to run?
>> > >> >
>> > >> >--
>> > >> >Simon Worth
>> > >> >
>> > >> >
>> > >> >"Robert" <anonymous@.discussions.microsoft.com>
wrote in
>> > >> message
>> > >> >news:2f7d01c520e0$37410ba0$a501280a@.phx.gbl...
>> > >> >> Does anyone know why as a sysadmin I am unable
to run
>> > >> jobs
>> > >> >> manually? The SQL agent runs under my account
and the
>> > >> jobs
>> > >> >> run fine on schedule but when I try to run them
>> > manually
>> > >> >> nothing happens and nothing is logged in Event
>> > Viewer.
>> > >> >
>> > >> >
>> > >> >.
>> > >> >
>> > >
>> > >
>> > >.
>> > >
>>
>
>.
>

Running SQL Jobs manually

running sp_updatestats is producing problems.

Hi to all,

1.I have first a question about one SP sp_updatestats.

Because the SP it is asking in the implementation about the sysadmin rights i got problems at running time.

Our user it is the db-owner but it is no sysadmin.

To give an example the sp : sp_createstats it is not asking for this rights, and this function it creates new objects in the db.

My first question is Why the sp_updatestats must ask for this rights what is the oficcial ansqear from Microsoft about this ?

I make a note this problem persist in the SQL Server 2005.

2.Now because of this problem i have tried to copy the sp from SQL Server 2000 and 2005 and to modify the SP to ask only for the owner rights.

So instead of

if not is_srvrolemember('sysadmin') = 1

I ask only for: if not IS_MEMBER ('db_owner') = 1.

In sql Server 2000 everything works fine in the SQL Server 2005 I got a very strange error:

'stats_ver_current' ist not identified as a known Function(translated error!).

Very strange I try to create this SP-copy using the sa account.

I have search for the function 'stats_ver_current' in SQL server books online and I found no documentation about. Google was no help too.

I was looking in the sysobjects too, no success.

What I am doing hier wrong ?

What should be a good solution for my rights problem with sp_updatestats ?

I'd first like to gain an understanding as to why you are trying to update statistics in the way that you are doing. Have you proven that out-of-date statistics are causing a performance problem after performing a particular action, or are you updating the statistics just to be sure?

If you absolutely must update statistics in this way then in SQL Server 2005 you can use EXECUTE AS to change execution context to another login or database user - this feature could help you out.

There's more detail here:

http://www.databasejournal.com/features/mssql/article.php/3596701

Chris

|||

The reason because I need to run the SP it is because I saw that some statisctics after some operations are out of date.

Of course this problem occurres not always it can depends of a lot of factors.

The idea is I need to update them. So in the SQL Server 2005 I wanted to use the new SP because as Microsoft says in the documentation it is something new to the SQL Server 2005. In the new implementation are not any more all the statistics reupdated just only that ones that are not any more up to date. So of course this will help the performance and the SP will run faster doing just what it is necesary.

About your idea of using exec as I cannot use it.

I dont have any account that have the sysadmin rights.The client does not allow us to have such an account.

Lets say on the Server are more databases. On our database we are owners so we should be able to do all the staff we need.As a sysadmin we will have the full control of all the databases and this it is a problem for our clients of course.

As I sayd I can run the SP sp_createstats 'indexonly' ' without problems. But he SP sp_updatestats I cannot run it.

Any other ideas to my questions ?

|||

Check out the following link:

http://msdn2.microsoft.com/en-us/library/ms187348.aspx

The UPDATE STATISTICS command will allow you to update statistics for a single table and requires only ALTER permissions on that table - this is actually the command that sp_updatestats uses.

You could create your own version of the existing sp_updatestats stored proc, that exists in the master database in SQL Server 2005, and make use of the 'STATS_DATE' function rather than the stats_ver_current function. This isn't ideal but it is better than doing nothing and should help get around your problem. If you decide to go down this route and don't use a user with database owner permissions to execute the stored proc then you will need to add ALTER permissions onto each of the tables you wish to update.

Hope this helps,

Chris

|||

Thanks for your answear.

I know the function 'STATS_DATE' that I can use instead of stats_ver_current. It had this idea too.It doenst have the same performance as the function stats_ver_current, but I can use it.

Or I can say I will use the SQL Server 2000 Stored Procedure, who knows what other bugs are in the date statistics info.

So updating allways the statistics maybe it is not as bad as we can think.

I still wait an official answear from a Microsoft Employee(or who it is answearing for this forum) to my 2 questions:

Why the stored procedure sp_updatestats(SQL2k and SQL2k5) it is asking for the sysadmin rights, and why the stored procedure sp_createstats not ?

And why I cannot use the function stats_ver_current in SQL Server 2k5 ?

|||

There seems to be an error in Books OnLine regarding sp_updatestats. It says:

"Requires membership in the sysadmin fixed server role, or ownership of the database (dbo)."

However, using an account in db_owner, I get this error when I run it:

"User does not have permission to perform this action."

Yet I am able to use 'update statistics' on each of the tables -- including the sys.queue_message_x tables -- that sp_updatestats affects when I run it under a sysadmin account.

Thanks,

Ron Rice

Wednesday, March 28, 2012

Running xp_cmdshell

Tuesday, February 21, 2012

Running SQL Jobs manually

Running SQL Jobs manually

Running SQL Jobs manually

running sp_updatestats is producing problems.

db files

Blog Archive

About Me