Post reply

Granting SA Privilege on a Server with Multiple Instances

  • May 31, 2007 at 9:46 am

    #98293

    I was having a discusssion about what were the implications of granting SA authority to a SQL Server User in a name instance on a server with mulitple instances, and we were having a difference of opnion.  I thought I would ask this user community for some clarification.

    Here is the setup

    I have a dedicated SQL Server Box that has multiple instances of SQL Server 2000 on it.  One of the Application vendors says that to be supported we need to give the SA privilege to a SQL Server account in the database.  By granting the user SA, do I open myself up to the risk of that user ID being able to connect to databases in the other instances on this server? Or, will the userid actually be limited to accessing only his own instance? 

    Thanks,

    TJP8

  • May 31, 2007 at 10:35 am

    #710077

    The account can only connect to the instance it has rights to.

    BTW, what's the app? Dynamics used to say this and I convinced them they were wrong.

    Follow me on Twitter: http://www.twitter.com/way0utwest
    Forum Etiquette: How to post data/code on a forum to get the best help
    My Blog: www.voiceofthedba.com

  • May 31, 2007 at 11:28 am

    #710097

    Steve, what about PeopleSoft?  Their documentation says their app needs SA privs to the database.

  • May 31, 2007 at 11:55 am

    #710100

    This is a BMC product.  It works without SA Privilege, b/c we have been running it that way.  It only became an issue when the 3rd Party Vendor was in helping the Application guy and they could not do somethings on the SQL Server (see and run jobs, etc).  He informed me that we were out of compliance for support, under BMC's guidelines if we didn't have these two users with SA authority.  I was thinking there may have been a way to use Linked Servers or something along that line to access the other instances, but maybe that only comes into play when it is an NT user with Administrator on the box which gives it SA.

    The 3rd Party guy admitted it was probably just a result of lazy Developing as to why they claim to need SA rights, but that they require it for support.

    TJP8

  • May 31, 2007 at 12:15 pm

    #710108

    TJP8,

    I think you're referring to the login being a member of the sysadmin server role?  If so, it only has administrative privilages in the instance where it's a member of sysadmin. 

    If the login is a local admin on the server and Builtin\Administrators is a member of sysadmin in all the instances, then it would have sysadmin privileges in all instances.

    Greg

    Greg

  • May 31, 2007 at 12:38 pm

    #710115

    BMC is not just one of the bunch

    Maybe just give the account sysadmin priv during the support case and search a workaround at the end.

    Indeed if the win-login is win-admin and you did not disable builtin\admin then that win-login is sysadmin for all instances that have builtin\admin enabled.

    We revoke builtin\admin at install time !

    (all our server have a win-domain service account with least privilage attitude)

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply