Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

User requirements for SQL 2008 Expand / Collapse
Author
Message
Posted Wednesday, December 11, 2013 5:15 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, December 17, 2013 3:15 AM
Points: 13, Visits: 16
Hi Guys, wonder if you can help me?

I have a user who is able to read information from production databases. Neither I nor my client want to grant any more access than this on these databases.

As part of his role now, he has suggested creating a DB for reporting purposes, pulling information on a schedule from the production databases. He has asked for access to the agent, which I will grant by adding him to the SQLAgentUserRole.

Is there a role I can add him to, or permissions that I can grant, that will allow him to create databases, but not amend the contents of any existing databases?
Post #1521842
Posted Wednesday, December 11, 2013 7:17 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, December 17, 2013 3:15 AM
Points: 13, Visits: 16
It's ok, discussed with the user. We'll create the database, and grant him db_owner on that database only.
Post #1521889
Posted Wednesday, December 11, 2013 7:19 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 6:38 AM
Points: 14,797, Visits: 27,271
Not really. If a user can create a database, they can access that database.

On the existing instance, you can set the user as data_reader only which will prevent them from doing anything to the database. But I agree that a secondary system should be done to allow them to write bad queries without affecting your production instance (and they will write bad queries). You should look into automated mechanisms that don't require them to create their own database such as replication, log shipping or mirroring. Any of these will allow a way for you to set up a read only database. Mirroring or Replication are the best bets.

If you're moving to sQL Server 2012, you can also look to Availability Groups and a read only replica as a solution.


----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1521892
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse