Restore Permissions. RRS feed

  • Question

  • Hi Team,

    how we can get the logins & Users who have to the restore permissions on the instance.



    Tuesday, April 10, 2012 1:52 PM


All replies

  • From http://msdn.microsoft.com/en-us/library/ms186858.aspx  dbcreator,sysadmin have restore permissions also dbo(owner) of the database

    you could run this.

      spl1.name Principal,spl2.name as ServerRole
    FROM sys.server_principals spl1
    inner join sys.server_role_members srm
    on spl1.principal_id = srm.member_principal_id
    inner join sys.server_principals spl2
    on spl2.principal_id = srm.role_principal_id
    WHERE spl2.name in ('sysadmin','dbcreator')
    select spl.name OwnerofDB,d.name DbName from sys.databases d
    inner join sys.server_principals spl
    on d.owner_sid = spl.sid
    • Edited by SQL_Jay Wednesday, April 11, 2012 2:02 AM query change
    Wednesday, April 11, 2012 1:56 AM
  • SQL_Jay's answer is the sysadmin and dbcreator part of the answer. But also search for those users or database roles that have the CREATE DATABASE PERMISSION which is a database level permission in the master database. Query sys.database_permissions while in master. Also, look at this Permissions Poster. The CREATE ANY DATABASE, ALTER ANY DATABASE, and CONTROL SERVER permissions also include the CREATE DATABASE permission. Logins, and server roles that have those server level permissions are listed in sys.server_permissions.

    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

    Wednesday, April 11, 2012 3:09 PM
  • Thanks Rick
    Wednesday, April 11, 2012 3:27 PM
  • Hi Rick,

    Its really good poster in MS SQL server 2008 R2 Database Engine Permissions. All most every thing was covered in security side.

    Few days back i had installed sql server 2012 is there any good stuff to learn all Secuirty NEW thigns in 2012 can you please help me this. From basic to High level in Security Env. could you plase guide me on this.

    Thanks in advance.



    Thursday, April 12, 2012 1:03 PM
  • For a brief list of new security features in SQL Server 2012 (with some links to more info), see Security Enhancements (Database Engine) http://msdn.microsoft.com/en-us/library/cc645578.aspx

    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

    Thursday, April 12, 2012 3:21 PM
  • Hi Rick,

    Thanks your reply..

    I will start from today onward to work with these stuff as per your suggestion, in case i have any quries i will catch you.

    Could you please suggest me From where can i start..in Security Env..

    Step 1:

    Step 2: ... etc..  Really thanks for your valuble suggestions..


    ...Every success is start from step one only..


    Thursday, April 12, 2012 3:29 PM
  • Just adding my two cents

    Being a member of dbcreator server role could a dangeous because it gives you ability to drop a database that is not owned by  that user.Please read my blog about the issue


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

    • Marked as answer by Maggie Luo Thursday, April 26, 2012 6:49 AM
    Friday, April 13, 2012 9:12 AM
  • Suggestions regarding more information on security, first, there are lots of books out there. They are very helpful. I like Securing SQL Server by Denny Cherry.

    Some Web links:

    Database Engine Permission Basics

    SQL Server 2005 Security Best Practices - Operational and Administrative Tasks

    Database Engine Fixed Server and Fixed Database Roles

    Database Engine Security Checklist: Database Engine Security Configuration

    Checklist: Enhancing the Security of Database Engine Connections
    Checklist: Limiting Access to Data
    Checklist: Encrypting Sensitive Data

    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

    • Marked as answer by Maggie Luo Thursday, April 26, 2012 6:49 AM
    Friday, April 13, 2012 4:15 PM
  • Hi Rick,

    Thanks for your reply...really greate information.



    Saturday, April 14, 2012 4:26 PM