locked
Serveradmin-Role: Scope? Physical Server or Instance? RRS feed

  • Question

  • Hi,

    We've got a SQL 2005-Cluster with several Cluster-Groups and Instances.
    Now we've got an application that needs the "serveradmin"-Role in MS SQL 2005 for execution. The serveradmin-Role has the right to "SHUTDOWN". The question: Will this shut down the instance in which it's working, or will it shutdown the whole Server? In the latter case, our Admins won't grant the role.

    Or, to ask in another way: Is there a way to give a Login _all_ rights in one instance, so that the Login is not able to influence other Instances (Shutdown, consume all Hard-Disk Space or Memory...)

    Please excuse if i don't use the correct terminology, i'm not realy into Databases.

    Thank you for your Help!

    Filipp
    Wednesday, September 17, 2008 12:25 PM

Answers

  • SQL instances provide a security boundary from one instance to the next.  This means that a sysadmin of one instance does not have sysadmin privleges in another instance, unless the user or group is assigned to the sysadmin group, such as administrators who are assigned as sysadmins by default.

     

    The easiest example is to consider a web hosting company who provides its customers the ability to create and manage SQL Server databases.  If only one instance of SQL was used and all customers were given sysadmin rights then any customer could directly affect all other databases as the role is instance wide.  By creating separate instances then the sysadmin role for each instance will only have sysadmin rights to the instance in which they are assigned.

     

     

    Hope this helps.

    Wednesday, September 17, 2008 12:45 PM

All replies

  • SQL instances provide a security boundary from one instance to the next.  This means that a sysadmin of one instance does not have sysadmin privleges in another instance, unless the user or group is assigned to the sysadmin group, such as administrators who are assigned as sysadmins by default.

     

    The easiest example is to consider a web hosting company who provides its customers the ability to create and manage SQL Server databases.  If only one instance of SQL was used and all customers were given sysadmin rights then any customer could directly affect all other databases as the role is instance wide.  By creating separate instances then the sysadmin role for each instance will only have sysadmin rights to the instance in which they are assigned.

     

     

    Hope this helps.

    Wednesday, September 17, 2008 12:45 PM
  • Hi David,

    thank you, this helps!
    To be more exact: for me, this is a perfect answer. But do you have a link to an official documentation or something like that, that i could show our DB-Admins?

    Thanks

    Filipp
    Thursday, September 18, 2008 8:26 AM
  • Here is MSDN on line documentation that discusses reasons to use multiple instances.  As each instance will install its own sql server service, along with other selected sql services, each service runs alone in its own security context.  This can provide the security boundary that you may need, but also keep in mind that each service requires its own resources, cpu, memory, disk.  So further configuration may be necessary to assure that each instance runs optimally without affecting the other.

     

    http://msdn.microsoft.com/en-us/library/ms143531(SQL.90).aspx

     

    Hope this helps

     

     

    Thursday, September 18, 2008 11:38 AM
  • Hi David,

    thank you very much for your Help!

    Filipp
    Thursday, September 18, 2008 3:35 PM