Re: dbackupoperators

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

  • Next message: Andres Taylor: "Re: Check integrity data in distributed database" 
    Date: Fri, 14 May 2004 10:33:27 +0200

    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.
> > > > > > > > > > > >
> > > > > > > > > > > >
> > > > > > > > > > >
> > > > > > > > > > >
> > > > > > > > > >
> > > > > > > > > >
> > > > > > > > >
> > > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > > >
> > > > > > >
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>
  • Next message: Andres Taylor: "Re: Check integrity data in distributed database"