db_backupoperator question

I have assigned an user id to a role of db_backupoperator. But after I
created a scheduled job to do backup weekly, it won't run at all. No error
message in the event application. So I go to do the backup manually. I get
an error message when I click the "..." button to specify the backup file
location as following:
Microsoft SQL-DMO (ODBC SQLState: 42000)
"Error 229: Execute permission denied on object 'xp_availablemedia',
database 'master', owner 'dbo'."
Please help. Thanks.
Hi,
Its seems your login account does not have the permissions required to
create a backup
device in hard disk. Please consult your system administrator or database
administrator
to obtain the required permissions to write in to the hard disk (Write
permission on the folder you craete the backup file).
Thanks
Hari
MCDBA
"Eric Clapton" <no_spam@.bk.com> wrote in message
news:#bQGXdZMEHA.2628@.TK2MSFTNGP12.phx.gbl...
> I have assigned an user id to a role of db_backupoperator. But after I
> created a scheduled job to do backup weekly, it won't run at all. No error
> message in the event application. So I go to do the backup manually. I
get
> an error message when I click the "..." button to specify the backup file
> location as following:
> Microsoft SQL-DMO (ODBC SQLState: 42000)
> "Error 229: Execute permission denied on object 'xp_availablemedia',
> database 'master', owner 'dbo'."
> Please help. Thanks.
>
|||Thanks, Hari. I have created the user id previously and didn't assign a
local win 2k user id to it. May be this is the problem. Now I just created
a local win 2k user id with the access right. But I don't want to delete the
sql user id and re-create it again? How can I map the current sql 2k user id
with the local win 2k user? Please let me know. Thanks.
"Hari" <hari_prasad_k@.hotmail.com> wrote in message
news:uWGed8ZMEHA.808@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> Hi,
> Its seems your login account does not have the permissions required to
> create a backup
> device in hard disk. Please consult your system administrator or database
> administrator
> to obtain the required permissions to write in to the hard disk (Write
> permission on the folder you craete the backup file).
> Thanks
> Hari
> MCDBA
> "Eric Clapton" <no_spam@.bk.com> wrote in message
> news:#bQGXdZMEHA.2628@.TK2MSFTNGP12.phx.gbl...
error[vbcol=seagreen]
> get
file
>
|||Hi,
The user in which you start the SQL Server and SQL Agent service should have
the permission.
Thanks
Hari
MCDBA
"Eric Clapton" <no_spam@.bk.com> wrote in message
news:uTQM2WaMEHA.2824@.TK2MSFTNGP10.phx.gbl...
> Thanks, Hari. I have created the user id previously and didn't assign a
> local win 2k user id to it. May be this is the problem. Now I just
created
> a local win 2k user id with the access right. But I don't want to delete
the
> sql user id and re-create it again? How can I map the current sql 2k user
id[vbcol=seagreen]
> with the local win 2k user? Please let me know. Thanks.
>
> "Hari" <hari_prasad_k@.hotmail.com> wrote in message
> news:uWGed8ZMEHA.808@.tk2msftngp13.phx.gbl...
database[vbcol=seagreen]
> error
I
> file
>
|||Hari,
I found out that both services are started with local system
accounts. Does that mean whoever shut down and re-start the service is the
local system accounts? Or it mean something else? Do I need to re-start both
services with the user id I used to do backup?
"Hari" <hari_prasad_k@.hotmail.com> wrote in message
news:%23601RbaMEHA.3348@.TK2MSFTNGP09.phx.gbl...
> Hi,
> The user in which you start the SQL Server and SQL Agent service should
have[vbcol=seagreen]
> the permission.
> Thanks
> Hari
> MCDBA
>
> "Eric Clapton" <no_spam@.bk.com> wrote in message
> news:uTQM2WaMEHA.2824@.TK2MSFTNGP10.phx.gbl...
> created
> the
user[vbcol=seagreen]
> id
> database
I[vbcol=seagreen]
manually.
> I
>

db_backupoperator question

I have assigned an user id to a role of db_backupoperator. But after I
created a scheduled job to do backup weekly, it won't run at all. No error
message in the event application. So I go to do the backup manually. I get
an error message when I click the "..." button to specify the backup file
location as following:
Microsoft SQL-DMO (ODBC SQLState: 42000)
"Error 229: Execute permission denied on object 'xp_availablemedia',
database 'master', owner 'dbo'."
Please help. Thanks.Hi,
Its seems your login account does not have the permissions required to
create a backup
device in hard disk. Please consult your system administrator or database
administrator
to obtain the required permissions to write in to the hard disk (Write
permission on the folder you craete the backup file).
Thanks
Hari
MCDBA
"Eric Clapton" <no_spam@.bk.com> wrote in message
news:#bQGXdZMEHA.2628@.TK2MSFTNGP12.phx.gbl...
> I have assigned an user id to a role of db_backupoperator. But after I
> created a scheduled job to do backup weekly, it won't run at all. No error
> message in the event application. So I go to do the backup manually. I
get
> an error message when I click the "..." button to specify the backup file
> location as following:
> Microsoft SQL-DMO (ODBC SQLState: 42000)
> "Error 229: Execute permission denied on object 'xp_availablemedia',
> database 'master', owner 'dbo'."
> Please help. Thanks.
>|||Thanks, Hari. I have created the user id previously and didn't assign a
local win 2k user id to it. May be this is the problem. Now I just created
a local win 2k user id with the access right. But I don't want to delete the
sql user id and re-create it again? How can I map the current sql 2k user id
with the local win 2k user? Please let me know. Thanks.
"Hari" <hari_prasad_k@.hotmail.com> wrote in message
news:uWGed8ZMEHA.808@.tk2msftngp13.phx.gbl...
> Hi,
> Its seems your login account does not have the permissions required to
> create a backup
> device in hard disk. Please consult your system administrator or database
> administrator
> to obtain the required permissions to write in to the hard disk (Write
> permission on the folder you craete the backup file).
> Thanks
> Hari
> MCDBA
> "Eric Clapton" <no_spam@.bk.com> wrote in message
> news:#bQGXdZMEHA.2628@.TK2MSFTNGP12.phx.gbl...
> > I have assigned an user id to a role of db_backupoperator. But after I
> > created a scheduled job to do backup weekly, it won't run at all. No
error
> > message in the event application. So I go to do the backup manually. I
> get
> > an error message when I click the "..." button to specify the backup
file
> > location as following:
> >
> > Microsoft SQL-DMO (ODBC SQLState: 42000)
> >
> > "Error 229: Execute permission denied on object 'xp_availablemedia',
> > database 'master', owner 'dbo'."
> >
> > Please help. Thanks.
> >
> >
>|||Hi,
The user in which you start the SQL Server and SQL Agent service should have
the permission.
Thanks
Hari
MCDBA
"Eric Clapton" <no_spam@.bk.com> wrote in message
news:uTQM2WaMEHA.2824@.TK2MSFTNGP10.phx.gbl...
> Thanks, Hari. I have created the user id previously and didn't assign a
> local win 2k user id to it. May be this is the problem. Now I just
created
> a local win 2k user id with the access right. But I don't want to delete
the
> sql user id and re-create it again? How can I map the current sql 2k user
id
> with the local win 2k user? Please let me know. Thanks.
>
> "Hari" <hari_prasad_k@.hotmail.com> wrote in message
> news:uWGed8ZMEHA.808@.tk2msftngp13.phx.gbl...
> > Hi,
> >
> > Its seems your login account does not have the permissions required to
> > create a backup
> > device in hard disk. Please consult your system administrator or
database
> > administrator
> > to obtain the required permissions to write in to the hard disk (Write
> > permission on the folder you craete the backup file).
> >
> > Thanks
> > Hari
> > MCDBA
> >
> > "Eric Clapton" <no_spam@.bk.com> wrote in message
> > news:#bQGXdZMEHA.2628@.TK2MSFTNGP12.phx.gbl...
> > > I have assigned an user id to a role of db_backupoperator. But after I
> > > created a scheduled job to do backup weekly, it won't run at all. No
> error
> > > message in the event application. So I go to do the backup manually.
I
> > get
> > > an error message when I click the "..." button to specify the backup
> file
> > > location as following:
> > >
> > > Microsoft SQL-DMO (ODBC SQLState: 42000)
> > >
> > > "Error 229: Execute permission denied on object 'xp_availablemedia',
> > > database 'master', owner 'dbo'."
> > >
> > > Please help. Thanks.
> > >
> > >
> >
> >
>|||Hari,
I found out that both services are started with local system
accounts. Does that mean whoever shut down and re-start the service is the
local system accounts? Or it mean something else? Do I need to re-start both
services with the user id I used to do backup?
"Hari" <hari_prasad_k@.hotmail.com> wrote in message
news:%23601RbaMEHA.3348@.TK2MSFTNGP09.phx.gbl...
> Hi,
> The user in which you start the SQL Server and SQL Agent service should
have
> the permission.
> Thanks
> Hari
> MCDBA
>
> "Eric Clapton" <no_spam@.bk.com> wrote in message
> news:uTQM2WaMEHA.2824@.TK2MSFTNGP10.phx.gbl...
> > Thanks, Hari. I have created the user id previously and didn't assign a
> > local win 2k user id to it. May be this is the problem. Now I just
> created
> > a local win 2k user id with the access right. But I don't want to delete
> the
> > sql user id and re-create it again? How can I map the current sql 2k
user
> id
> > with the local win 2k user? Please let me know. Thanks.
> >
> >
> > "Hari" <hari_prasad_k@.hotmail.com> wrote in message
> > news:uWGed8ZMEHA.808@.tk2msftngp13.phx.gbl...
> > > Hi,
> > >
> > > Its seems your login account does not have the permissions required to
> > > create a backup
> > > device in hard disk. Please consult your system administrator or
> database
> > > administrator
> > > to obtain the required permissions to write in to the hard disk (Write
> > > permission on the folder you craete the backup file).
> > >
> > > Thanks
> > > Hari
> > > MCDBA
> > >
> > > "Eric Clapton" <no_spam@.bk.com> wrote in message
> > > news:#bQGXdZMEHA.2628@.TK2MSFTNGP12.phx.gbl...
> > > > I have assigned an user id to a role of db_backupoperator. But after
I
> > > > created a scheduled job to do backup weekly, it won't run at all. No
> > error
> > > > message in the event application. So I go to do the backup
manually.
> I
> > > get
> > > > an error message when I click the "..." button to specify the backup
> > file
> > > > location as following:
> > > >
> > > > Microsoft SQL-DMO (ODBC SQLState: 42000)
> > > >
> > > > "Error 229: Execute permission denied on object 'xp_availablemedia',
> > > > database 'master', owner 'dbo'."
> > > >
> > > > Please help. Thanks.
> > > >
> > > >
> > >
> > >
> >
> >
>

db_backupoperator question

I have assigned an user id to a role of db_backupoperator. But after I
created a scheduled job to do backup weekly, it won't run at all. No error
message in the event application. So I go to do the backup manually. I get
an error message when I click the "..." button to specify the backup file
location as following:
Microsoft SQL-DMO (ODBC SQLState: 42000)
"Error 229: Execute permission denied on object 'xp_availablemedia',
database 'master', owner 'dbo'."
Please help. Thanks.Hi,
Its seems your login account does not have the permissions required to
create a backup
device in hard disk. Please consult your system administrator or database
administrator
to obtain the required permissions to write in to the hard disk (Write
permission on the folder you craete the backup file).
Thanks
Hari
MCDBA
"Eric Clapton" <no_spam@.bk.com> wrote in message
news:#bQGXdZMEHA.2628@.TK2MSFTNGP12.phx.gbl...
> I have assigned an user id to a role of db_backupoperator. But after I
> created a scheduled job to do backup weekly, it won't run at all. No error
> message in the event application. So I go to do the backup manually. I
get
> an error message when I click the "..." button to specify the backup file
> location as following:
> Microsoft SQL-DMO (ODBC SQLState: 42000)
> "Error 229: Execute permission denied on object 'xp_availablemedia',
> database 'master', owner 'dbo'."
> Please help. Thanks.
>|||Thanks, Hari. I have created the user id previously and didn't assign a
local win 2k user id to it. May be this is the problem. Now I just created
a local win 2k user id with the access right. But I don't want to delete the
sql user id and re-create it again? How can I map the current sql 2k user id
with the local win 2k user? Please let me know. Thanks.
"Hari" <hari_prasad_k@.hotmail.com> wrote in message
news:uWGed8ZMEHA.808@.tk2msftngp13.phx.gbl...
> Hi,
> Its seems your login account does not have the permissions required to
> create a backup
> device in hard disk. Please consult your system administrator or database
> administrator
> to obtain the required permissions to write in to the hard disk (Write
> permission on the folder you craete the backup file).
> Thanks
> Hari
> MCDBA
> "Eric Clapton" <no_spam@.bk.com> wrote in message
> news:#bQGXdZMEHA.2628@.TK2MSFTNGP12.phx.gbl...
error[vbcol=seagreen]
> get
file[vbcol=seagreen]
>|||Hi,
The user in which you start the SQL Server and SQL Agent service should have
the permission.
Thanks
Hari
MCDBA
"Eric Clapton" <no_spam@.bk.com> wrote in message
news:uTQM2WaMEHA.2824@.TK2MSFTNGP10.phx.gbl...
> Thanks, Hari. I have created the user id previously and didn't assign a
> local win 2k user id to it. May be this is the problem. Now I just
created
> a local win 2k user id with the access right. But I don't want to delete
the
> sql user id and re-create it again? How can I map the current sql 2k user
id
> with the local win 2k user? Please let me know. Thanks.
>
> "Hari" <hari_prasad_k@.hotmail.com> wrote in message
> news:uWGed8ZMEHA.808@.tk2msftngp13.phx.gbl...
database[vbcol=seagreen]
> error
I[vbcol=seagreen]
> file
>|||Hari,
I found out that both services are started with local system
accounts. Does that mean whoever shut down and re-start the service is the
local system accounts? Or it mean something else? Do I need to re-start both
services with the user id I used to do backup?
"Hari" <hari_prasad_k@.hotmail.com> wrote in message
news:%23601RbaMEHA.3348@.TK2MSFTNGP09.phx.gbl...
> Hi,
> The user in which you start the SQL Server and SQL Agent service should
have
> the permission.
> Thanks
> Hari
> MCDBA
>
> "Eric Clapton" <no_spam@.bk.com> wrote in message
> news:uTQM2WaMEHA.2824@.TK2MSFTNGP10.phx.gbl...
> created
> the
user[vbcol=seagreen]
> id
> database
I[vbcol=seagreen]
manually.[vbcol=seagreen]
> I
>

db_backupoperator cannot issue dbcc commands

Not sure...but the manual says that a user with the db_backupoperator role
should be able to issue the dbcc commands. So I created a user, added the
role, and he cannot issue any dbcc commands. I even logged out and logged
back in without any success.
This is what I get:
1> dbcc checkdb(yada)
2> go
Msg 7983, Level 14, State 8, Server YADA, Line 1
User 'dbcc_user01' does not have permission to run DBCC CHECKDB for database
'yada'.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
I found this proc to check the role, and dbcc's aren't listed here...but I
am not sure if they would be...
1> sp_dbfixedrolepermission db_backupoperator
2> go
DbFixedRole
Permission
-- --
db_backupoperator
BACKUP DATABASE
db_backupoperator
BACKUP LOG
db_backupoperator
CHECKPOINT
I then granted the user dbo...and of course then he can do the dbcc's.
Thanks for any info!
Which manual and which DBCC commands does it say should work?
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:9A83EC25-BECC-4E86-B615-858EF58BF661@.microsoft.com...
> Not sure...but the manual says that a user with the db_backupoperator role
> should be able to issue the dbcc commands. So I created a user, added the
> role, and he cannot issue any dbcc commands. I even logged out and logged
> back in without any success.
> This is what I get:
> 1> dbcc checkdb(yada)
> 2> go
> Msg 7983, Level 14, State 8, Server YADA, Line 1
> User 'dbcc_user01' does not have permission to run DBCC CHECKDB for
database
> 'yada'.
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
>
> I found this proc to check the role, and dbcc's aren't listed here...but I
> am not sure if they would be...
>
> 1> sp_dbfixedrolepermission db_backupoperator
> 2> go
> DbFixedRole
> Permission
> ----
--
> -- --
> db_backupoperator
> BACKUP DATABASE
> db_backupoperator
> BACKUP LOG
> db_backupoperator
> CHECKPOINT
>
> I then granted the user dbo...and of course then he can do the dbcc's.
> Thanks for any info!
|||In BOL 2000 under roles (roles-SQL Server, overview) it states that DBCC's
can be run but does not list which ones.
Andrew J. Kelly SQL MVP
"Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
news:ud5VUY1yEHA.3552@.TK2MSFTNGP10.phx.gbl...
> Which manual and which DBCC commands does it say should work?
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> "Chris" <Chris@.discussions.microsoft.com> wrote in message
> news:9A83EC25-BECC-4E86-B615-858EF58BF661@.microsoft.com...
> database
> --
>
|||Which manual says that? It's not correct.
In your example of executing dbcc checkdb, if you look up
the permissions section of this command in books online, it
will indicate that only sysadmins and db_owners can execute
this.
The fixed database role db_backupoperator can issue backup
statements for the current database. Not much more with that
role.
-Sue
On Mon, 15 Nov 2004 09:28:02 -0800, "Chris"
<Chris@.discussions.microsoft.com> wrote:

>Not sure...but the manual says that a user with the db_backupoperator role
>should be able to issue the dbcc commands. So I created a user, added the
>role, and he cannot issue any dbcc commands. I even logged out and logged
>back in without any success.
>This is what I get:
>1> dbcc checkdb(yada)
>2> go
>Msg 7983, Level 14, State 8, Server YADA, Line 1
>User 'dbcc_user01' does not have permission to run DBCC CHECKDB for database
>'yada'.
>DBCC execution completed. If DBCC printed error messages, contact your
>system administrator.
>
>I found this proc to check the role, and dbcc's aren't listed here...but I
>am not sure if they would be...
>
>1> sp_dbfixedrolepermission db_backupoperator
>2> go
> DbFixedRole
> Permission
>----
> -- --
> db_backupoperator
> BACKUP DATABASE
> db_backupoperator
> BACKUP LOG
> db_backupoperator
> CHECKPOINT
>
>I then granted the user dbo...and of course then he can do the dbcc's.
>Thanks for any info!
|||I just found the page you are referring to - it's not too
well documented is it.
I think the only dbcc that role can execute is checkcatalog
- as well as checkpoint and backup statements. .
-Sue
On Mon, 15 Nov 2004 16:09:15 -0500, "Andrew J. Kelly"
<sqlmvpnooospam@.shadhawk.com> wrote:

>In BOL 2000 under roles (roles-SQL Server, overview) it states that DBCC's
>can be run but does not list which ones.

db_backupoperator cannot issue dbcc commands

Not sure...but the manual says that a user with the db_backupoperator role
should be able to issue the dbcc commands. So I created a user, added the
role, and he cannot issue any dbcc commands. I even logged out and logged
back in without any success.
This is what I get:
1> dbcc checkdb(yada)
2> go
Msg 7983, Level 14, State 8, Server YADA, Line 1
User 'dbcc_user01' does not have permission to run DBCC CHECKDB for database
'yada'.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
I found this proc to check the role, and dbcc's aren't listed here...but I
am not sure if they would be...
1> sp_dbfixedrolepermission db_backupoperator
2> go
DbFixedRole
Permission
----
--
-- --
db_backupoperator
BACKUP DATABASE
db_backupoperator
BACKUP LOG
db_backupoperator
CHECKPOINT
I then granted the user dbo...and of course then he can do the dbcc's.
Thanks for any info!Which manual and which DBCC commands does it say should work?
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:9A83EC25-BECC-4E86-B615-858EF58BF661@.microsoft.com...
> Not sure...but the manual says that a user with the db_backupoperator role
> should be able to issue the dbcc commands. So I created a user, added the
> role, and he cannot issue any dbcc commands. I even logged out and logged
> back in without any success.
> This is what I get:
> 1> dbcc checkdb(yada)
> 2> go
> Msg 7983, Level 14, State 8, Server YADA, Line 1
> User 'dbcc_user01' does not have permission to run DBCC CHECKDB for
database
> 'yada'.
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
>
> I found this proc to check the role, and dbcc's aren't listed here...but I
> am not sure if they would be...
>
> 1> sp_dbfixedrolepermission db_backupoperator
> 2> go
> DbFixedRole
> Permission
> ----
--
> -- --
> db_backupoperator
> BACKUP DATABASE
> db_backupoperator
> BACKUP LOG
> db_backupoperator
> CHECKPOINT
>
> I then granted the user dbo...and of course then he can do the dbcc's.
> Thanks for any info!|||In BOL 2000 under roles (roles-SQL Server, overview) it states that DBCC's
can be run but does not list which ones.
Andrew J. Kelly SQL MVP
"Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
news:ud5VUY1yEHA.3552@.TK2MSFTNGP10.phx.gbl...
> Which manual and which DBCC commands does it say should work?
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> "Chris" <Chris@.discussions.microsoft.com> wrote in message
> news:9A83EC25-BECC-4E86-B615-858EF58BF661@.microsoft.com...
> database
> --
>|||Which manual says that? It's not correct.
In your example of executing dbcc checkdb, if you look up
the permissions section of this command in books online, it
will indicate that only sysadmins and db_owners can execute
this.
The fixed database role db_backupoperator can issue backup
statements for the current database. Not much more with that
role.
-Sue
On Mon, 15 Nov 2004 09:28:02 -0800, "Chris"
<Chris@.discussions.microsoft.com> wrote:

>Not sure...but the manual says that a user with the db_backupoperator role
>should be able to issue the dbcc commands. So I created a user, added the
>role, and he cannot issue any dbcc commands. I even logged out and logged
>back in without any success.
>This is what I get:
>1> dbcc checkdb(yada)
>2> go
>Msg 7983, Level 14, State 8, Server YADA, Line 1
>User 'dbcc_user01' does not have permission to run DBCC CHECKDB for databas
e
>'yada'.
>DBCC execution completed. If DBCC printed error messages, contact your
>system administrator.
>
>I found this proc to check the role, and dbcc's aren't listed here...but I
>am not sure if they would be...
>
>1> sp_dbfixedrolepermission db_backupoperator
>2> go
> DbFixedRole
> Permission
>----
--
> -- --
> db_backupoperator
> BACKUP DATABASE
> db_backupoperator
> BACKUP LOG
> db_backupoperator
> CHECKPOINT
>
>I then granted the user dbo...and of course then he can do the dbcc's.
>Thanks for any info!|||I just found the page you are referring to - it's not too
well documented is it.
I think the only dbcc that role can execute is checkcatalog
- as well as checkpoint and backup statements. .
-Sue
On Mon, 15 Nov 2004 16:09:15 -0500, "Andrew J. Kelly"
<sqlmvpnooospam@.shadhawk.com> wrote:

>In BOL 2000 under roles (roles-SQL Server, overview) it states that DBCC's
>can be run but does not list which ones.sql

db_backupoperator cannot issue dbcc commands

Not sure...but the manual says that a user with the db_backupoperator role
should be able to issue the dbcc commands. So I created a user, added the
role, and he cannot issue any dbcc commands. I even logged out and logged
back in without any success.
This is what I get:
1> dbcc checkdb(yada)
2> go
Msg 7983, Level 14, State 8, Server YADA, Line 1
User 'dbcc_user01' does not have permission to run DBCC CHECKDB for database
'yada'.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
I found this proc to check the role, and dbcc's aren't listed here...but I
am not sure if they would be...
1> sp_dbfixedrolepermission db_backupoperator
2> go
DbFixedRole
Permission
----
-- --
db_backupoperator
BACKUP DATABASE
db_backupoperator
BACKUP LOG
db_backupoperator
CHECKPOINT
I then granted the user dbo...and of course then he can do the dbcc's.
Thanks for any info!Which manual and which DBCC commands does it say should work?
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:9A83EC25-BECC-4E86-B615-858EF58BF661@.microsoft.com...
> Not sure...but the manual says that a user with the db_backupoperator role
> should be able to issue the dbcc commands. So I created a user, added the
> role, and he cannot issue any dbcc commands. I even logged out and logged
> back in without any success.
> This is what I get:
> 1> dbcc checkdb(yada)
> 2> go
> Msg 7983, Level 14, State 8, Server YADA, Line 1
> User 'dbcc_user01' does not have permission to run DBCC CHECKDB for
database
> 'yada'.
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
>
> I found this proc to check the role, and dbcc's aren't listed here...but I
> am not sure if they would be...
>
> 1> sp_dbfixedrolepermission db_backupoperator
> 2> go
> DbFixedRole
> Permission
> ----
--
> -- --
> db_backupoperator
> BACKUP DATABASE
> db_backupoperator
> BACKUP LOG
> db_backupoperator
> CHECKPOINT
>
> I then granted the user dbo...and of course then he can do the dbcc's.
> Thanks for any info!|||In BOL 2000 under roles (roles-SQL Server, overview) it states that DBCC's
can be run but does not list which ones.
--
Andrew J. Kelly SQL MVP
"Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
news:ud5VUY1yEHA.3552@.TK2MSFTNGP10.phx.gbl...
> Which manual and which DBCC commands does it say should work?
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> "Chris" <Chris@.discussions.microsoft.com> wrote in message
> news:9A83EC25-BECC-4E86-B615-858EF58BF661@.microsoft.com...
>> Not sure...but the manual says that a user with the db_backupoperator
>> role
>> should be able to issue the dbcc commands. So I created a user, added
>> the
>> role, and he cannot issue any dbcc commands. I even logged out and
>> logged
>> back in without any success.
>> This is what I get:
>> 1> dbcc checkdb(yada)
>> 2> go
>> Msg 7983, Level 14, State 8, Server YADA, Line 1
>> User 'dbcc_user01' does not have permission to run DBCC CHECKDB for
> database
>> 'yada'.
>> DBCC execution completed. If DBCC printed error messages, contact your
>> system administrator.
>>
>> I found this proc to check the role, and dbcc's aren't listed here...but
>> I
>> am not sure if they would be...
>>
>> 1> sp_dbfixedrolepermission db_backupoperator
>> 2> go
>> DbFixedRole
>> Permission
>> ----
> --
>> -- --
>> db_backupoperator
>> BACKUP DATABASE
>> db_backupoperator
>> BACKUP LOG
>> db_backupoperator
>> CHECKPOINT
>>
>> I then granted the user dbo...and of course then he can do the dbcc's.
>> Thanks for any info!
>|||Which manual says that? It's not correct.
In your example of executing dbcc checkdb, if you look up
the permissions section of this command in books online, it
will indicate that only sysadmins and db_owners can execute
this.
The fixed database role db_backupoperator can issue backup
statements for the current database. Not much more with that
role.
-Sue
On Mon, 15 Nov 2004 09:28:02 -0800, "Chris"
<Chris@.discussions.microsoft.com> wrote:
>Not sure...but the manual says that a user with the db_backupoperator role
>should be able to issue the dbcc commands. So I created a user, added the
>role, and he cannot issue any dbcc commands. I even logged out and logged
>back in without any success.
>This is what I get:
>1> dbcc checkdb(yada)
>2> go
>Msg 7983, Level 14, State 8, Server YADA, Line 1
>User 'dbcc_user01' does not have permission to run DBCC CHECKDB for database
>'yada'.
>DBCC execution completed. If DBCC printed error messages, contact your
>system administrator.
>
>I found this proc to check the role, and dbcc's aren't listed here...but I
>am not sure if they would be...
>
>1> sp_dbfixedrolepermission db_backupoperator
>2> go
> DbFixedRole
> Permission
>----
> -- --
> db_backupoperator
> BACKUP DATABASE
> db_backupoperator
> BACKUP LOG
> db_backupoperator
> CHECKPOINT
>
>I then granted the user dbo...and of course then he can do the dbcc's.
>Thanks for any info!|||I just found the page you are referring to - it's not too
well documented is it.
I think the only dbcc that role can execute is checkcatalog
- as well as checkpoint and backup statements. .
-Sue
On Mon, 15 Nov 2004 16:09:15 -0500, "Andrew J. Kelly"
<sqlmvpnooospam@.shadhawk.com> wrote:
>In BOL 2000 under roles (roles-SQL Server, overview) it states that DBCC's
>can be run but does not list which ones.