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

Hi Tom,

Thanks and I manage to do it in this manner , just to share this with other
users.......
I have created a Windows acc add in the Backup Operators Group..This gives u
explicit permissions to the disk drives in a way to perform recovery
(accessing the disk drives).U need this acc to access the SSMS or entreprise
mgr,Then creating a SQL login in servers with sysadmin server roles cause
have test it with db_creator and db_backupoperator it doesnt work with it
only with sysadmin server role alone it does.

Thanks,
Aishu
MCSE,MCDBA,MCSA

"Tom Moreau" wrote:

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: login 101..
    ... On Windows 2003, SQL Server 2005 can enforce the Windows password complexity ... Windows authentication - SQL Server uses a special protocol to ask ... user is in the list of allowed logins, ...
    (microsoft.public.sqlserver.security)
  • RE: How to create a trusted connection
    ... You need to grant access for the Windows login by referring to the books ... is set to use Windows authentication to be able to do trusted connection. ... There are two modes of authentication in SQL Server: ...
    (microsoft.public.sqlserver.security)
  • RE: How to create a trusted connection
    ... What do you mean by "creating a Windows login"? ... To login SQL Server 2000 using trusted connection, grant the Windows ... Group" in SQL Server Books Online to get the concept, ...
    (microsoft.public.sqlserver.security)
  • Re: Connecting to SQL from a Windows Service
    ... There is probably some way to debug this, but that's deeper Windows knowledge than I can dredge up right now. ... I looked into the Event Log and found that when my service ... 1st Login succeeds. ... You can try turning on login auditing in SQL Server and then check the ...
    (microsoft.public.sqlserver.connect)
  • Re: Trusted SQL Server connection (SQL 2005)
    ... To be able to login to SQL Server using a Windows account, then a login must be created in SQL Server Security\Login for that account or for a Windows group which consists that Windows account. ... Create login for the Windows group who you want them to be able to login to your SQL Server and assign them necessary permissions for your database objects or whatever. ...
    (microsoft.public.sqlserver.security)