I'm going to start off apologizing since this is my first post and looking at other questions they don't quite match my own situation, also I'm a SysAdmin, not a DBA so my understanding of SQL is rusty and limited.
My organization frequently encounters vendors who in order for their application to run they do what we refer to as lazy programming and request SA credentials or demand that their custom account has sysadmin rights. My organization also has data protection/compliance policies which require us to grant only required access and rights.
Most recently a vendor that I am working with wants their account to be a sysadmin, but it actually only needs access to a single database. If we give the AD account (because their application only works with Windows Authentication) sysadmin rights I can use the application's GUI to browse and select the database, but if we only make that same account the DBOwner we can't find anything.
Is there a way to find out if it is trying to poll other databases or determine what the lowest level of rights needed for the required database(s) would be? When I tried asking the vendor I was told, "It requires sysadmin rights, but its ok because it only accesses the one database." Which isn't ok when it comes to our policies.
Thanks for any advice.
Best Answer
If the vendor is providing their own database you could make their account apart of the owner role at the database level and all you need to do at the system level is grant the account login permissions to their database. This will allow them to have full control over their database and will prevent them from using the account to access other databases you have. If they tell you they need the sysadmin database role for tempdb access that is not true as every user has all the access they need to tempdb by default no other steps needed unless you made changes to the defaults for tempdb. Setting up their account this way will also prevent them from enabling other extended features in sql like mail and xp_cmd_shell which would give them access to the underlying OS.
There is other hardening you can do at the sql server service level to ensure that any sysadmin accounts in sql don't have admin rights on the OS. These include running the sql service and sql agent service as just a user on the box. Then adding the windows group mssql$....users to the Disk Maintenance and Log on as a batch policy. This will give the two accounts access to run as a service and will also allow sql server to do instant file initialization when it needs to grow the underlying data/log files. You will also need to grant full control at the NTFS level of the folders for the same group where the data and log files. Use the SQL Server configuration tool to change the accounts as it sets most of the needed permissions for you.
Here is a basic guide from MSDN while I try to find a more step by step on of the basics that I've used in the past