December 11, 2013 at 5:15 am
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?
December 11, 2013 at 7:17 am
It's ok, discussed with the user. We'll create the database, and grant him db_owner on that database only.
December 11, 2013 at 7:19 am
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
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply