How to restrict permissions to a single database

Posted on 2009-04-21
Last Modified: 2012-05-06
I need to create a SQL user account (from a domain account) that has full access to do anything they want to new databases. They should be able to create, update, add, delete, backup, etc... any database as long as they are not explicitly denied access. I want to be able to selectively deny ALL access to certain databases. Is this possible? If so, how?
Question by:Russ Suter
    LVL 39

    Accepted Solution

    There is no explicit deny.  The best way to achieve this would be to do the following:  

    Give them db_creator writes.  db_creator allows users to create dbs and when you create a db you're automatically DBO (so they can modify the db.)  

    Grant DBO to exisiting dbs which are needed.

    Optional Security: Deny datareader/writer to any dbs that they shouldn't have access to.

    If you give them security admin or sa then they could overwrite any options you choose.

    LVL 13

    Assisted Solution

    Dont know if this idea fits your requirements completely; but you can do this by making this account dbo on the db's you wish; then in sql server properties (SSMS, R/Click, Props on the servername), select properties; find the user and hit 'Deny any database'

    LVL 19

    Author Closing Comment

    by:Russ Suter
    Both good suggestions. Thanks

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
    In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
    To add imagery to an HTML email signature, you have two options available to you. You can either add a logo/image by embedding it directly into the signature or hosting it externally and linking to it. The vast majority of email clients display l…
    Hi everyone! This is Experts Exchange customer support.  This quick video will show you how to change your primary email address.  If you have any questions, then please Write a Comment below!

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now