May 24, 2018 at 10:44 am
Is it possible in SQL Server 2008r2 to give a user sysadmin level access to the SQL Server instance while preventing them from restarting the DB engine itself?
We currently host SQL 2008R2 Ent Edition on our local network but will soon be moving to a private SASS where the software vendor (not Microsoft) will host SQL Server. As the DBA I want to retain sysadmin access over the SQL Server and its DB but the counter argument is that doing so risks the vendor being unable to follow the SAL since it will mean the client could restart the DB engine when they feel like it. What I want to know is if there is some way to meet half way here and give us ssyadmin level access to SQL Server while addressing the vendors argument that we could restart the DB engine on a whim and thus force the vendor to not full fill their Service Level Agreement.
NOTE : The use of SQL 2008R2 is a temporary measure. Eventually we will be moving to the latest SQL server but we must stick with 2008R2 in the immediate short term; 1-2 years.
Kindest Regards,
Just say No to Facebook!May 24, 2018 at 10:53 am
It might be possible to have a sysadmin login that didn't have rights to start the service. Starting SQL Server is a Windows level function, not something inside of SQL . I'm not sure of if you can trigger a restart, but you can't start the service unless you are a local Windows admin, or have rights to do that.
This talks about that, and I'm pretty sure it's the same in 2008.
https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/start-stop-pause-resume-restart-sql-server-services?view=sql-server-2017
However, if you are sysadmin, you can certainly pause/stop the access to the database. Shutdown in T-SQL will work and you can certainly change to single user or restricted user.
What I'd suggest is you enable auditing. Look for certain things that a sysadmin can do and the vendor is worried about. Track those in a insert only/read file location.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply