Re: dbackupoperators

From: Tibor Karaszi (tibor_please.no.email_karaszi_at_hotmail.nomail.com)
Date: 05/15/04 

Date: Sat, 15 May 2004 19:44:29 +0200

Eric,

The same GRANT as for other permissions (as documented in Books Online) :-)

GRANT EXEC ON xp_whatever TO username 

-- 
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Eric Clapton" <no_spam@bk.com> wrote in message news:eEr3xViOEHA.308@TK2MSFTNGP11.phx.gbl...
> Tibor,
>
>               Can you please let me know the grant EXEC permissions
> statement?
>
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@hotmail.nomail.com> wrote in
> message news:e1aje4YOEHA.2920@tk2msftngp13.phx.gbl...
> > You could create a user in master for the login and grant EXEC permissions
> on the xp to that user. I picked up
> > the thread rather late, so I don't know the requirements etc. Hence I
> cannot comment on why this is to be used
> > etc. It is undocumented however!
> >
> > -- 
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> >
> >
> > "Eric Clapton" <no_spam@bk.com> wrote in message
> news:urj4pKXOEHA.2480@tk2msftngp13.phx.gbl...
> > > I found that I might need to grant sysadmin role to the user profile. My
> > > concern is I want to assign this user to do schedule backup only but not
> > > access to the tables. So I grant this user to be a sysadmin role, it
> will
> > > allow this user to have access to the tables and others. Please help me
> to
> > > solve this issue. Many thanks.
> > >
> > >
> > > "Eric Clapton" <no_spam@bk.com> wrote in message
> > > news:%23v4fJ1UOEHA.2780@TK2MSFTNGP09.phx.gbl...
> > > > I tried to run EXEC master..xp_availablemedia this time with
> > > > dbackupoperators login, however I got the following message:
> > > >
> > > > Server: Msg 229, Level 14, State 5, Procedure xp_availablemedia, Line
> 1
> > > > EXECUTE permission denied on object 'xp_availablemedia', database
> > > 'master',
> > > > owner 'dbo'.
> > > >
> > > >
> > > > BTW, what does xp_availablemedia do?
> > > >
> > > >
> > > > "Tibor Karaszi" <tibor_please.no.email_karaszi@hotmail.nomail.com>
> wrote
> > > in
> > > > message news:OxxU9EPOEHA.3712@TK2MSFTNGP10.phx.gbl...
> > > > > Strange... What service pack and build number?
> > > > > What does below return?
> > > > >
> > > > > sp_helpextendedproc 'xp_availablemedia'
> > > > >
> > > > >
> > > > > -- 
> > > > > Tibor Karaszi, SQL Server MVP
> > > > > http://www.karaszi.com/sqlserver/default.asp
> > > > >
> > > > >
> > > > > "Hari Prasad" <hari_prasad_k@hotmail.com> wrote in message
> > > > news:u4mU6sOOEHA.484@TK2MSFTNGP10.phx.gbl...
> > > > > > Hi,
> > > > > >
> > > > > > EXEC master..xp_availablemedia, same thing work for me as well.
> > > > > >
> > > > > > Thanks
> > > > > > Hari
> > > > > > MCDBA
> > > > > >
> > > > > >
> > > > > > "Tibor Karaszi" <tibor_please.no.email_karaszi@hotmail.nomail.com>
> > > wrote
> > > > in
> > > > > > message news:#MuvqINOEHA.3312@tk2msftngp13.phx.gbl...
> > > > > > > Did you execute it in master? Below work on my machine:
> > > > > > >
> > > > > > > EXEC master..xp_availablemedia
> > > > > > >
> > > > > > > --
> > > > > > > Tibor Karaszi, SQL Server MVP
> > > > > > > http://www.karaszi.com/sqlserver/default.asp
> > > > > > >
> > > > > > >
> > > > > > > "Eric Clapton" <no_spam@bk.com> wrote in message
> > > > > > news:uWkQa3LOEHA.3744@TK2MSFTNGP11.phx.gbl...
> > > > > > > > Hari,
> > > > > > > >
> > > > > > > >           xp_availablemedia is not available in SQL2000. Do
> you
> > > mean
> > > > > > > > something else? Please help. Many thanks.
> > > > > > > >
> > > > > > > > Super
> > > > > > > >
> > > > > > > >
> > > > > > > > "Hari" <hari_prasad_k@hotmail.com> wrote in message
> > > > > > > > news:u6YyU8wNEHA.1388@TK2MSFTNGP09.phx.gbl...
> > > > > > > > > Hi,
> > > > > > > > >
> > > > > > > > > Not required, If you are tryng to backup the database to
> local
> > > > server
> > > > > > hard
> > > > > > > > > disk , Local system admin is more than enough. That user got
> > > admin
> > > > > > rights
> > > > > > > > in
> > > > > > > > > local machine.
> > > > > > > > >
> > > > > > > > > Open query analyzer and execute the below command:-
> > > > > > > > >
> > > > > > > > > xp_availablemedia
> > > > > > > > >
> > > > > > > > > Check the drives, the command is displaying. Based on this
> > > create
> > > > the
> > > > > > > > backup
> > > > > > > > > files. I think you are trying to backup to a remote server
> or a
> > > > media
> > > > > > not
> > > > > > > > > available. If the execution gives you an error try giving
> > > execute
> > > > prev
> > > > > > to
> > > > > > > > > this extended procedure to your backup user.
> > > > > > > > >
> > > > > > > > > grant exec on xp_availablemedia to <user>
> > > > > > > > >
> > > > > > > > >
> > > > > > > > > Still if you have errors then try below:-
> > > > > > > > > Can you please assign "SYSADMIN" server fixed role to this
> user
> > > > and
> > > > > > try
> > > > > > > > > backup the database.
> > > > > > > > >
> > > > > > > > > Thanks
> > > > > > > > > Hari
> > > > > > > > > MCDBA
> > > > > > > > >
> > > > > > > > > "Eric Clapton" <no_spam@bk.com> wrote in message
> > > > > > > > > news:OapZeLvNEHA.3944@tk2msftngp13.phx.gbl...
> > > > > > > > > >
> > > > > > > > > >             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.
> > > > > > > > > > > > > >
> > > > > > > > > > > > > >
> > > > > > > > > > > > >
> > > > > > > > > > > > >
> > > > > > > > > > > >
> > > > > > > > > > > >
> > > > > > > > > > >
> > > > > > > > > > >
> > > > > > > > > >
> > > > > > > > > >
> > > > > > > > >
> > > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > > >
> > > > > > >
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>