October 10, 2011 at 3:15 am
Hi,
We would like to streamline access to production SQL Servers such that non-DBA users will not have sysadmin privileges but they should still be able to do regular operations like database creation/removal, backup and restore, login creation and granting permissions etc.
I understand that dbcreator role can be granted for operations like database creation/restore.
Do you have any practices for this?
October 10, 2011 at 4:16 am
praveen_vejandla (10/10/2011)
We would like to streamline access to production SQL Servers such that non-DBA users will not have sysadmin privileges
that is the recommended practice.
praveen_vejandla (10/10/2011)
but they should still be able to do regular operations like database creation/removal, backup and restore, login creation and granting permissions etc.I understand that dbcreator role can be granted for operations like database creation/restore.
Do you have any practices for this?
Ok, complete u turn. Just about all of those actions are general DBA tasks.
Allowing users to create and backup, and even more worryingly, remove or restore databases is frought with danger.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
October 10, 2011 at 5:18 am
praveen_vejandla (10/10/2011)
Hi,We would like to streamline access to production SQL Servers such that non-DBA users will not have sysadmin privileges but they should still be able to do regular operations like database creation/removal, backup and restore, login creation and granting permissions etc.
I understand that dbcreator role can be granted for operations like database creation/restore.
Do you have any practices for this?
Yes. Best practice is that non-DBA users don't do that kind of stuff. 😀
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.October 10, 2011 at 5:24 am
Pablo: Yes. Best practice is that non-DBA users don't do that kind of stuff.
I agree. If it is just a Development server where they can play you might grant them sa rights (if your SLA says that a Dev environment is not critical). If it is a production server, just keep them out.
October 10, 2011 at 10:47 am
Thanks for all the responses.
Actual problem is DBA is available only 8 hours a day but support environment is open 24*7.
So non-DBA support technicians has to deal with DB related backup/restore/creation activities when DBA is not around.
Other than granting dbcreator and enabling auditing/DDL trigger to track the changes, do you have any other suggestions to streamline such access by non-DBA users?
Will signed stored procedures be of any help in this case?
Thanks.
October 10, 2011 at 10:51 am
sounds very much like you need an on call DBA!
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
October 10, 2011 at 11:45 am
praveen_vejandla (10/10/2011)
Actual problem is DBA is available only 8 hours a day but support environment is open 24*7.
Then you need 24/7 DBA support. A second DBA (minimum) and an after hours on-call availability rotation.
So non-DBA support technicians has to deal with DB related backup/restore/creation activities when DBA is not around.
That's what the DBA does, in theory, amongst other things. Why would your supported users be constantly backing up and restoring anyway? The need to go to a backup should be firing off warning bells all over the place. If they need it for personal work so they don't tie up the main database, with huge queries for example, install SQLExpress on their desktops and give them SA on that.
Other than granting dbcreator and enabling auditing/DDL trigger to track the changes, do you have any other suggestions to streamline such access by non-DBA users?
One place where our marketing department had to do something like this I wrapped all the necessary restore steps into a proc with only a few exposed parameters to make their lives easier.
Will signed stored procedures be of any help in this case?
Errr, no, not really, unless you plan on exposing things via procedures these users couldn't normally do.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
October 10, 2011 at 5:15 pm
What horrifies me is that general users are able to backup and retrieve the companies data :w00t:
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
October 10, 2011 at 5:30 pm
Perry Whittle (10/10/2011)
What horrifies me is that general users are able to backup and retrieve the companies data :w00t:
That shouldn't horrify you. In most places you can download reports to excel, etc etc. The database isn't usually the sole place to get mass data, since most marketing departments need to do trending, not OLTP type of data manipulation. All depends on what the 'general user' is, I guess, and what's in the database.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
October 11, 2011 at 12:58 am
Not all users are allowed to do database operations. There is a specific group of 5/10 people (production/application support team) who supports 24*7 activities.
They are not DBAs but if there is a need, they have to create/restore databases.
October 11, 2011 at 6:14 am
That shouldn't horrify you. In most places you can download reports to excel, etc etc.
Well, I will say it should. Pulling data in reports is not that dangerous than giving the commands in hand of non-DBA. He might bring database down for hours or loss of critical data or something even worse.
October 11, 2011 at 6:18 am
They are not DBAs but if there is a need, they have to create/restore databases.
It’s too much risk. It may be fine on DEV environment but not on PROD.
October 11, 2011 at 10:06 am
As a DBA, we all know that it is dangerous to grant CREATE/BACKUP/RESTORE privileges to non-DBA users in production enviornments.
My intention behind this post is how do we streamline access and allow non-DBA people to perform such activities without granting sysadmin privileges?
I mean any specific best practices to narrow down the access through stored procedure signing etc.
October 11, 2011 at 10:13 am
the dbcreator server role will grant create drop and restore permissions to any database. Securables at the database level are used to grant the BACKUP permissions or just grant the DB_BackupOperator fixed database role.
To allow these users to grant permissions to other users you need to grant them permissions along with the "WITH GRANT OPTION". Again there fixed database roles which alos may be used. Depends on how granular you want to be!
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
October 12, 2011 at 6:06 pm
Our users have read-only access to all prod environments. At the same time our DBA team provide 24/7 support in all prod environments-meaning each valid prod request ( after getting approval) will be accomodated within reasonable time. But you have to keep in mind that even valid, approved request can be wrong. So you, as a DBA, have to monitor it and question it
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply