db_owner role

I am getting this error message when disabling a job. The user is not a SA.

TITLE: Microsoft.SqlServer.Smo

Alter failed for Job 'XYZ'.

ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

EXECUTE permission denied on object 'sp_help_operator', database 'msdb', owner 'dbo'. (Microsoft SQL Server, Error: 229)

The user can diasble the job if i give db_owner permission on msdb.

Is there a way i can do this without making the user db_owner?

Thanks for any help

There is no specific db_role for job administation, but you can create one. Grant the appropiate permission to the role that you need and assign the user to that role. You sure can also only grant the specific user the rights for that, but the next time you want another user to do the job you will have to repeat your work for that.

HTH, jens Suessmeyer.

http://www.sqlserver2005.de

|||

Thanks.

Could you describe the "Grant the appropriate permission to the role that you need" in as little more detail as to what has to be done?

Thanks for the help

|||

I don′t know the complete list of permissions you will need for your work to be done, so you would have to go the iterative way to get your work done, here are the steps to complete:

-Create a db Role in the Database msdb (as you want to administer the alerts and things related to the SQL Agent)
-Assign users to this role that should do the administrative work.
-Assign the appropiate permissions to that db role (where the users are currently in) Put in the execute right for that procedure you get an error for.

HTH, Jens SUessmeyer.

http://www.sqlserver2005.de

DB Restore FAIL

In my SQL SERVER 2000, my Database(DB) say for e.g. XYZ got corrupted
getting message SUSPECT.
So i created new DB with initial size of 5GB to try to restore 50GB the
original DB & it failed.
Finally i was able to restore, by increasing size to substantial level where
my restore was succesfull. i want to know what i did wrong & why finally it
succeed after increasing size. any help will be highly appreciated
Regarding the corruption:
http://www.karaszi.com/SQLServer/inf...suspect_db.asp
As for restore:
You cannot restore into a smaller database. SQL Server need to create or already have database files
with same size as you had when you took the backup.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Jay" <sqldba@.abc.com> wrote in message news:fa00ab1c82794850b98da6a02af94be4@.ureader.com. ..
> In my SQL SERVER 2000, my Database(DB) say for e.g. XYZ got corrupted
> getting message SUSPECT.
> So i created new DB with initial size of 5GB to try to restore 50GB the
> original DB & it failed.
> Finally i was able to restore, by increasing size to substantial level where
> my restore was succesfull. i want to know what i did wrong & why finally it
> succeed after increasing size. any help will be highly appreciated
|||Hi Tibor,
Will an autogrow option in 5GB DB work in this scenario?
Thanks,
Sree
"Tibor Karaszi" wrote:

> Regarding the corruption:
> http://www.karaszi.com/SQLServer/inf...suspect_db.asp
> As for restore:
> You cannot restore into a smaller database. SQL Server need to create or already have database files
> with same size as you had when you took the backup.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Jay" <sqldba@.abc.com> wrote in message news:fa00ab1c82794850b98da6a02af94be4@.ureader.com. ..
>
|||No. But why did you create the database before the restore. The restore operation does a safety
check. If the database which you are to restore into exists, but the file layout doesn't match, you
get an error. This is so you don't do a mistake. But the restore process can create the database for
you when you perform the restore operation, it doesn't have to exist. Or, use the REPLACE option of
the restore command.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Sreejith G" <SreejithG@.discussions.microsoft.com> wrote in message
news:2C05DE26-DD1A-429F-A8CF-8997481C4B81@.microsoft.com...[vbcol=seagreen]
> Hi Tibor,
> Will an autogrow option in 5GB DB work in this scenario?
> Thanks,
> Sree
>
> "Tibor Karaszi" wrote:
|||Ya thats correct, but still didnt try this option yet thats why.
Thanks, Tibor.
"Tibor Karaszi" wrote:

> No. But why did you create the database before the restore. The restore operation does a safety
> check. If the database which you are to restore into exists, but the file layout doesn't match, you
> get an error. This is so you don't do a mistake. But the restore process can create the database for
> you when you perform the restore operation, it doesn't have to exist. Or, use the REPLACE option of
> the restore command.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Sreejith G" <SreejithG@.discussions.microsoft.com> wrote in message
> news:2C05DE26-DD1A-429F-A8CF-8997481C4B81@.microsoft.com...
>

DB Restore FAIL

In my SQL SERVER 2000, my Database(DB) say for e.g. XYZ got corrupted
getting message SUSPECT.
So i created new DB with initial size of 5GB to try to restore 50GB the
original DB & it failed.
Finally i was able to restore, by increasing size to substantial level where
my restore was succesfull. i want to know what i did wrong & why finally it
succeed after increasing size. any help will be highly appreciatedRegarding the corruption:
http://www.karaszi.com/SQLServer/info_corrupt_suspect_db.asp
As for restore:
You cannot restore into a smaller database. SQL Server need to create or already have database files
with same size as you had when you took the backup.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Jay" <sqldba@.abc.com> wrote in message news:fa00ab1c82794850b98da6a02af94be4@.ureader.com...
> In my SQL SERVER 2000, my Database(DB) say for e.g. XYZ got corrupted
> getting message SUSPECT.
> So i created new DB with initial size of 5GB to try to restore 50GB the
> original DB & it failed.
> Finally i was able to restore, by increasing size to substantial level where
> my restore was succesfull. i want to know what i did wrong & why finally it
> succeed after increasing size. any help will be highly appreciated|||Hi Tibor,
Will an autogrow option in 5GB DB work in this scenario?
Thanks,
Sree
"Tibor Karaszi" wrote:
> Regarding the corruption:
> http://www.karaszi.com/SQLServer/info_corrupt_suspect_db.asp
> As for restore:
> You cannot restore into a smaller database. SQL Server need to create or already have database files
> with same size as you had when you took the backup.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Jay" <sqldba@.abc.com> wrote in message news:fa00ab1c82794850b98da6a02af94be4@.ureader.com...
> > In my SQL SERVER 2000, my Database(DB) say for e.g. XYZ got corrupted
> > getting message SUSPECT.
> >
> > So i created new DB with initial size of 5GB to try to restore 50GB the
> > original DB & it failed.
> >
> > Finally i was able to restore, by increasing size to substantial level where
> > my restore was succesfull. i want to know what i did wrong & why finally it
> > succeed after increasing size. any help will be highly appreciated
>|||No. But why did you create the database before the restore. The restore operation does a safety
check. If the database which you are to restore into exists, but the file layout doesn't match, you
get an error. This is so you don't do a mistake. But the restore process can create the database for
you when you perform the restore operation, it doesn't have to exist. Or, use the REPLACE option of
the restore command.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Sreejith G" <SreejithG@.discussions.microsoft.com> wrote in message
news:2C05DE26-DD1A-429F-A8CF-8997481C4B81@.microsoft.com...
> Hi Tibor,
> Will an autogrow option in 5GB DB work in this scenario?
> Thanks,
> Sree
>
> "Tibor Karaszi" wrote:
>> Regarding the corruption:
>> http://www.karaszi.com/SQLServer/info_corrupt_suspect_db.asp
>> As for restore:
>> You cannot restore into a smaller database. SQL Server need to create or already have database
>> files
>> with same size as you had when you took the backup.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "Jay" <sqldba@.abc.com> wrote in message news:fa00ab1c82794850b98da6a02af94be4@.ureader.com...
>> > In my SQL SERVER 2000, my Database(DB) say for e.g. XYZ got corrupted
>> > getting message SUSPECT.
>> >
>> > So i created new DB with initial size of 5GB to try to restore 50GB the
>> > original DB & it failed.
>> >
>> > Finally i was able to restore, by increasing size to substantial level where
>> > my restore was succesfull. i want to know what i did wrong & why finally it
>> > succeed after increasing size. any help will be highly appreciated
>>|||Ya thats correct, but still didnt try this option yet thats why.
Thanks, Tibor.
"Tibor Karaszi" wrote:
> No. But why did you create the database before the restore. The restore operation does a safety
> check. If the database which you are to restore into exists, but the file layout doesn't match, you
> get an error. This is so you don't do a mistake. But the restore process can create the database for
> you when you perform the restore operation, it doesn't have to exist. Or, use the REPLACE option of
> the restore command.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Sreejith G" <SreejithG@.discussions.microsoft.com> wrote in message
> news:2C05DE26-DD1A-429F-A8CF-8997481C4B81@.microsoft.com...
> > Hi Tibor,
> >
> > Will an autogrow option in 5GB DB work in this scenario?
> >
> > Thanks,
> > Sree
> >
> >
> >
> > "Tibor Karaszi" wrote:
> >
> >> Regarding the corruption:
> >> http://www.karaszi.com/SQLServer/info_corrupt_suspect_db.asp
> >>
> >> As for restore:
> >> You cannot restore into a smaller database. SQL Server need to create or already have database
> >> files
> >> with same size as you had when you took the backup.
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >> Blog: http://solidqualitylearning.com/blogs/tibor/
> >>
> >>
> >> "Jay" <sqldba@.abc.com> wrote in message news:fa00ab1c82794850b98da6a02af94be4@.ureader.com...
> >> > In my SQL SERVER 2000, my Database(DB) say for e.g. XYZ got corrupted
> >> > getting message SUSPECT.
> >> >
> >> > So i created new DB with initial size of 5GB to try to restore 50GB the
> >> > original DB & it failed.
> >> >
> >> > Finally i was able to restore, by increasing size to substantial level where
> >> > my restore was succesfull. i want to know what i did wrong & why finally it
> >> > succeed after increasing size. any help will be highly appreciated
> >>
> >>
>

DB Restore FAIL

In my SQL SERVER 2000, my Database(DB) say for e.g. XYZ got corrupted
getting message SUSPECT.
So i created new DB with initial size of 5GB to try to restore 50GB the
original DB & it failed.
Finally i was able to restore, by increasing size to substantial level where
my restore was succesfull. i want to know what i did wrong & why finally it
succeed after increasing size. any help will be highly appreciatedRegarding the corruption:
http://www.karaszi.com/SQLServer/in..._suspect_db.asp
As for restore:
You cannot restore into a smaller database. SQL Server need to create or alr
eady have database files
with same size as you had when you took the backup.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Jay" <sqldba@.abc.com> wrote in message news:fa00ab1c82794850b98da6a02af94be4@.ur
eader.com...

> In my SQL SERVER 2000, my Database(DB) say for e.g. XYZ got corrupted
> getting message SUSPECT.
> So i created new DB with initial size of 5GB to try to restore 50GB the
> original DB & it failed.
> Finally i was able to restore, by increasing size to substantial level whe
re
> my restore was succesfull. i want to know what i did wrong & why finally i
t
> succeed after increasing size. any help will be highly appreciated|||Hi Tibor,
Will an autogrow option in 5GB DB work in this scenario?
Thanks,
Sree
"Tibor Karaszi" wrote:

> Regarding the corruption:
> http://www.karaszi.com/SQLServer/in..._suspect_db.asp
> As for restore:
> You cannot restore into a smaller database. SQL Server need to create or a
lready have database files
> with same size as you had when you took the backup.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Jay" <sqldba@.abc.com> wrote in message news:fa00ab1c82794850b98da6a02af94
be4@.ureader.com...
>|||No. But why did you create the database before the restore. The restore oper
ation does a safety
check. If the database which you are to restore into exists, but the file la
yout doesn't match, you
get an error. This is so you don't do a mistake. But the restore process can
create the database for
you when you perform the restore operation, it doesn't have to exist. Or, us
e the REPLACE option of
the restore command.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Sreejith G" <SreejithG@.discussions.microsoft.com> wrote in message
news:2C05DE26-DD1A-429F-A8CF-8997481C4B81@.microsoft.com...[vbcol=seagreen]
> Hi Tibor,
> Will an autogrow option in 5GB DB work in this scenario?
> Thanks,
> Sree
>
> "Tibor Karaszi" wrote:
>|||Ya thats correct, but still didnt try this option yet thats why.
Thanks, Tibor.
"Tibor Karaszi" wrote:

> No. But why did you create the database before the restore. The restore op
eration does a safety
> check. If the database which you are to restore into exists, but the file
layout doesn't match, you
> get an error. This is so you don't do a mistake. But the restore process c
an create the database for
> you when you perform the restore operation, it doesn't have to exist. Or,
use the REPLACE option of
> the restore command.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Sreejith G" <SreejithG@.discussions.microsoft.com> wrote in message
> news:2C05DE26-DD1A-429F-A8CF-8997481C4B81@.microsoft.com...
>