Results 1 to 2 of 2

Thread: Need to understand how sysadmin granted to all users

  1. #1
    Join Date
    Sep 2012
    Posts
    1

    Need to understand how sysadmin granted to all users

    I am NOT a DB admin or SQL pro by any means. Recently I was brought into an issue where it seems that some how during a cluster failover all users were granted sysadmin access. Now I am not sure how this would even be possible sounds kinda strange right off the bat to me. Moving on, in this SQL environment there are 70+ DB's (there a a number of them that folks don't even know what they are), and 150+ users, again this looks strange to me. Now, that all of these users were granted sysadmin, when we take away that permission we have application specific users that can not authenticated to the SQL environment. Our DBA (forth person to hold this role in 3years) has, for the time being, granted sysadmin back to all users and has created a script the redirects, on login to the apps correct DB. My question is can anyone think of anyway there could have been a permission change that wide spread done during a failed patch? how easy is it to make environment wide change like that? what are the chances that the DB users were sysadmins all along and no one knew. Can anybody suggest a way I could check the old state of these users. I can pull server level and prob db environment backups but I am worried about doing restores to the SQL environment and losing the data written since the backup I want to look at.

    O and a few days before this issue came up we suspect that the host of the cluster was bounced without a failover. I am leaning towards that as a likely reason for this strange permission issue?

    Completely lost and in need of outside guidance,
    Bryan
    PS I thank anyone who is willing to help out in advance.

  2. #2
    Join Date
    Sep 2002
    Posts
    5,938
    In sql2k5, windows local admin group on sql server machine is part of sysadmin by default. You are better to revoke that.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •