We have been having issues where the Application DBA's are creating files to expand their databases and using up all the space.
The two-part question is does the db_ddladmin role allow the user to alter a dB to add files? And what sort of lockdowns are best practices? dB Owner? Just DataReader/DataWriter?
Can we prevent them from adding files at all?
The db_ddladmin role does not permit a user to add files to a database.
The db_owner role, dbcreater server role or sysadmin role do permit files to be added to a database.
What's best practice depends on what your Application DBAs actually need to do. It seems like you really have a management or training issue rather than a security requirement. Someone needs to be responsible for allocating storage. That person or those people need to understand what the correct procedure is. In many cases this will be the responsibility of a storage management group who have to allocate space on storage arrays before the DBA gets to use it.
