what would be the right permission to allow everything else but overwriting or creating a database?
4
votes
2
answers
3262
views
I have a test environment where the AD group
mycompany\developers
is currently sysadmin
.
Recently I have had some issues, specially regarding to people restoring databases. My concerns are:
1. sometimes the database is involved in replication
2. the permissions are overwritten - the correct way is to save the
current permissions before the restore and re-apply them after the restore.
3. Have you checked the current available disk space in all drivers,
specially those used in this restore?
4. there might be other people using or working on that specific
database, and restoring over it without communication can cause
someone's else work being lost
5. in the dev environment the databases can be in simple recovery mode.
Have you shrunk the log and changed the database to simple recovery
mode.
6. in this particular case, the full backup for every user database is
going to run automatically every night. but anyways, have you
checked for scheduled backups?
7. have you deleted from the server the backup that you used to do the
restore?
8. the folders and drives or data and logs are different in live and
test. are you sure you moved the files to the right places?
9. Have you had a look at orphaned users and logins?
I particularly have concerns when developers create new databases, even in the dev environment. why?
because they start by creating the database in dev and then just ask me to copy it to live.
have you done any capacity planning for this database? how big it should be and how much it would grow in a month?
1. have you isolated or considered different filegroups for demainding
objects\indexes?
2. Have you checked the current available disk space in all drivers,
specially those used in this DB creation?
3. how big is the transaction log and why? Initially I like to set it
1.5 times the biggest clustered index.
4. what are the autogrowth settings?
5. what are the current permissions?
I have realised some people are not so easy to deal with, I rather reduce their power over that server.
I don't want to do their jobs, but I would like to restrict them from creating new databases, or overwriting existing ones, either by restoring or by attaching files, etc.
They still should be able to create and run jobs, create\alter any object withing user databases, create logins, grant permissions etc...
Basically the question is:
How to limit the power of a current sysadmin?
I thought about not granting sysadmin but a set of server permissions (excluding those to create/alter databases) that would allow them to do everything else.
what would that set of server permissions be?
Asked by Marcello Miorelli
(17274 rep)
Nov 21, 2017, 11:01 AM
Last activity: Nov 26, 2019, 10:09 AM
Last activity: Nov 26, 2019, 10:09 AM