locked
Creating Custom Server Role in SQL Server 2005 RRS feed

  • Question

  • In SQL Server 2005 environment I am looking for a server role that can allow a junior DBA to backup the database. Currently SYSADMINS server role or db_backupoperator or db_owner database roles allow for this. We do not want to give SYSADMIN access or allowing each database on all servers will be cumbersome to do and manage. Ideally we want junior DBA to look after DB backups and manage security (SecurityAdmin server role can do it). Is it possible to create a custom server role that can simply allow all DB backups and manage security on the SQL Server 2005..

    Friday, January 7, 2011 5:00 PM

Answers

  • Hi Don,

    I tried the script. It works for individual database. I was looking for something at the server level that grants permission to backup all the databases on each SQL Server 2005.

    Hi Malik,

    Right now we don't have any such option in SQL server 2005,2008 and 2008 R2 to have such level of permission granted to backup all database in an instance unless a sysadmin access is granted . What you can do with this is create a new login and add him as an user in each database and grant that user db_backupoperator or grant backup database permission.


    Thanks, Leks


    Hi,

    I agree with Leks, this might be the only way within SQL Server 2005 since only members of the sysadmin fixed server role and the db_owner and db_backupoperator fixed database roles can bakcup databases. Luckily, this feature (custom server role) will be included in SQL Server "Denali" (Version 11), you can check it in SQL Server "Denali" CTP 1.

    Thanks,
    Chunsong


    Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
    Monday, January 10, 2011 3:18 AM
  • Hi Don,

    I tried the script. It works for individual database. I was looking for something at the server level that grants permission to backup all the databases on each SQL Server 2005.

    Hi Malik,

    Right now we don't have any such option in SQL server 2005,2008 and 2008 R2 to have such level of permission granted to backup all database in an instance unless a sysadmin access is granted . What you can do with this is create a new login and add him as an user in each database and grant that user db_backupoperator or grant backup database permission.


    Thanks, Leks
    Sunday, January 9, 2011 12:22 AM

All replies

  • Hi Malik,

    We can't create custom Server Roles in SQL 2005. What you can do is while creating a login under system roles you can give Security Admin role and in securables you can give DB_BACKUPOPERATOR role (which you already know).

     


    Regards Gursethi Blog: http://ms-gursethi.blogspot.com/ ++++ Please mark "Propose As Answer" if my answer helped ++++
    Friday, January 7, 2011 5:28 PM
  • Hi Gursethi : Thanks for the feedback. I tried to create a new login with SecurityAdmin role and tried Securables, I can not see DB_BackupOperator role in there except Servers, Endpoints, Logins but in the User Mapping I will need to select each database and check DB_BackupOperator. Because we have more than 50 SQL Servers instances each with quite a number of DBs, it will be difficult to manage. Any thoughts.

    Friday, January 7, 2011 8:31 PM
  • I tried something like this -

    CREATE

     

     

    LOGIN myLogin WITH PASSWORD = '1StrongPassw0rd'

    CREATE

     

     

    USER myUser for Login myLogin

    grant

     

     

    BACKUP DATABASE to myUser

    Alternatively you can also grant to groups and add the user to the group.

    Hope this helps.


    Don Pinto, Microsoft, SQL Server Engine. ---------------------------------------------------------- This posting is provided "AS IS" with no warranties, and confers no rights. ---------------------------------------------------------
    Friday, January 7, 2011 10:57 PM
  • Hi Don,

    I tried the script. It works for individual database. I was looking for something at the server level that grants permission to backup all the databases on each SQL Server 2005.

    Friday, January 7, 2011 11:32 PM
  • Hi Don,

    I tried the script. It works for individual database. I was looking for something at the server level that grants permission to backup all the databases on each SQL Server 2005.

    Hi Malik,

    Right now we don't have any such option in SQL server 2005,2008 and 2008 R2 to have such level of permission granted to backup all database in an instance unless a sysadmin access is granted . What you can do with this is create a new login and add him as an user in each database and grant that user db_backupoperator or grant backup database permission.


    Thanks, Leks
    Sunday, January 9, 2011 12:22 AM
  • Please vote here
    https://connect.microsoft.com/SQLServer/feedback/details/535846/creating-custom-server-level-roles


    Balmukund Lakhani | Please mark solved if I've answered your question, vote for it as helpful to help other user's find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog: http://blogs.msdn.com/blakhani
    Team Blog: http://blogs.msdn.com/sqlserverfaq
    Sunday, January 9, 2011 6:55 AM
  • Hi Don,

    I tried the script. It works for individual database. I was looking for something at the server level that grants permission to backup all the databases on each SQL Server 2005.

    Hi Malik,

    Right now we don't have any such option in SQL server 2005,2008 and 2008 R2 to have such level of permission granted to backup all database in an instance unless a sysadmin access is granted . What you can do with this is create a new login and add him as an user in each database and grant that user db_backupoperator or grant backup database permission.


    Thanks, Leks


    Hi,

    I agree with Leks, this might be the only way within SQL Server 2005 since only members of the sysadmin fixed server role and the db_owner and db_backupoperator fixed database roles can bakcup databases. Luckily, this feature (custom server role) will be included in SQL Server "Denali" (Version 11), you can check it in SQL Server "Denali" CTP 1.

    Thanks,
    Chunsong


    Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
    Monday, January 10, 2011 3:18 AM
  • Chunsong is correct.

    More information here
    http://www.sqlsoldier.com/wp/sqlserver/customserverrolesindenali
    http://www.straightpathsql.com/archives/2010/11/create-your-own-sql-server-server-roles/

     


    Balmukund Lakhani | Please mark solved if I've answered your question, vote for it as helpful to help other user's find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog: http://blogs.msdn.com/blakhani
    Team Blog: http://blogs.msdn.com/sqlserverfaq
    Monday, January 10, 2011 6:13 AM