June 20, 2011 at 12:41 am
Hi ,
I read this article http://support.microsoft.com/kb/932881
it stated that
"if SQL Server 2005 is started in single-user mode, any user who has membership in the BUILTIN\Administrators group can connect to SQL Server 2005 as a SQL Server administrator. The user can connect regardless of whether the BUILTIN\Administrators group has been granted a server login that is provisioned in the SYSADMIN fixed server role. This behavior is by design. This behavior is intended to be used for data recovery scenarios.
"
does anyone know a way to prevent system administrators from starting the database in single user mode so system administrators can never access the database with sql server administrator's rights.
thank you
June 20, 2011 at 12:46 am
By design its implied that if your are an OS level administrator you would anyway have access to database files and services which could be used to bring the server online or offline , things like allowing startup parameters etc are performed by logining to the server directly.
It would not be advisable to deny such a user from being able to login to the box. Esp since this could come back to hurt you when u really need a super user access to the server during DR.
However if your still intent on it , under security select the properties of the windows user and under roles unchecl sysadmin.
June 20, 2011 at 1:14 am
Hi,
I have removed all the windows logins from sql server but when I restarted the server in single user mode,
system administrators can still access the database even they do not have logins
I need a way to prevent system administrators from starting the sql server in single user mode
thank you
June 20, 2011 at 1:15 am
in other words, i need a way to prevent sql server from starting single user mode
thank you
June 20, 2011 at 5:47 am
So far as I know, there is no way to prevent it. Single User mode is used to prevent additional people from gaining access while certain maintenance activities are occurring. Or during disaster recovery. It would be dangerous to shut it off.
By definition, anyone who can turn on Single User mode has db_owner perms. If you want these people not to be able to turn on Single User mode, I suggest restricting their permissions instead of trying to break SQL Server.
EDIT: Or they have sysadmin / dbcreator perms.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply