How to restrict permissions to a single database

Posted on 2009-04-21
Medium Priority
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 41

Accepted Solution

Kyle Abrahams earned 1000 total points
ID: 24197271
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

St3veMax earned 1000 total points
ID: 24197789
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 20

Author Closing Comment

by:Russ Suter
ID: 31572913
Both good suggestions. Thanks

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …

840 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