Re: What type of Server Roles to assign for Backup and Recovery

If you have a number of servers, the script it out and use SQLCMD to connect
to each server and run the script.

Looks like you should create a Windows group that includes the logins that
should have the backup privilege and then add that group as a login to each
SQL Server. You can then grant read/write privileges to the domain group on
the appropriate drives. be sure that the domain account(s) under which your
SQL Server is running has read/write privileges on the drives.

--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau


"Aishu" <Aishu@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:9B9B82AF-7606-414A-8E4C-535D432E7FF7@xxxxxxxxxxxxxxxx
Hi Tom,

I can do that if I have one server , how abt 20 odd servers .........its
kinda painstaking job...
Have figure out assigning a limited Windows Domain acc but when login
created and put into Domain Users group still cant access the disk drives of
the servers.
So here is the questions what is the Domain Rights that I can give to the
group of user other then Domain Users and Domain Admins to access the disk
drives to not only perform sql backup but also recovery from the device....

Thanks,
Aishu


"Tom Moreau" wrote:

You may have to CREATE CREDENTIAL for that login, in order to use the file
system (if it's a SQL Serer authenticated login). Otherwise, try granting
NTFS permissions to that Windows login.

--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau


"Aishu" <Aishu@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:98AFCC31-29B5-4883-A7E6-ADF8A1EDC263@xxxxxxxxxxxxxxxx
Hi Tom,

Using the db_backupoperator and the db_creator roles for a sql server
login.Somehow when u try to perform a recovery from a device than it
requires
a NTFS permission on the Directory of where the backup file is
located...somehow to overcome all this I had to assign a sysadmin.....Is
any
workaround solution for this




Thanks,
Aishu


"Tom Moreau" wrote:

Any member of db_backupoperator can perform backups on the same
database.
Members of the fixed server role, dbcreator, can create databases. Just
add
and remove logins from these roles as required.

--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau


"Aishu" <Aishu@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:5C2A13C5-9B9A-421A-9295-73252B3C642F@xxxxxxxxxxxxxxxx
Hi there,

I have a question , what is the best practice to create a login to
perform
backup and recovery, Can I just have DB role as DB_backupoperator or I
have
to assign a sysadmin role to the user ( in SQL authentication/
windows).Coming up with a secure policy , so that when users go out from
the
company it wont affect the practice.

Thanks,
Aishu








.

Relevant Pages

  • Re: SQL2005: Cannot connect error 11001
    ... The famous Windows Firewall (turned on my Server from which I'm trying to ... Exception Details: System.Data.SqlClient.SqlException: Login failed for user ... Try starting the SQL Server ... if you changed the port ...
    (microsoft.public.sqlserver.connect)
  • Re: error logging on SQL 2008
    ... I was, though, hoping for some more recognizable Reason for that error (state 11, " Token-based server access validation failed with an infrastructure error. ... Suggests that state 11 means "Valid login but server access failure", which doesn't really seem to rhyme with your description. ... How can you login to your SQL Server and use that tool if you can't log into your SQL Server? ... at System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, Int64 timerExpire, SqlConnection owningObject) ...
    (microsoft.public.sqlserver.server)
  • Re: Clearer version of my earlier question re sp_grantlogin and sp_grantdbaccess
    ... > file while you do changes, so the script file in the end contains the ... >> Server, doesn't have the right to grant DB access to Domain Groups. ... > Tibor Karaszi, SQL Server MVP ... >> site where they let me login as Domain Admin. ...
    (microsoft.public.sqlserver.msde)
  • Re: SQL 2005 Express setup
    ... Connect to your SQL Server Instance, ... Double click on the Login that you want to add to the "sysadmin" server role or right click on it and choose properties from the popup menu, ... Login is a member of the "sysadmin" fixed server role now. ...
    (microsoft.public.sqlserver.setup)
  • Re: SQL 2005 Express setup
    ... Connect to your SQL Server Instance, ... Double click on the Login that you want to add to the "sysadmin" server ... Mark the "sysadmin" checkbox to make this Login a member of this Server ...
    (microsoft.public.sqlserver.setup)