-4

I need to write a script [in anything, assumed powershell would be best] that checks to see if the given Windows or SQL Server credentials have sysadmin rights for each server in a list. I would like to loop through list pf SQL Servers within my environment and produce a log that tells me which servers I have sysadmin privileges for.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Trey Cox
  • 13
  • 2
  • 2
    SO is not a script writing service, what have you tried already? – techguy1029 Sep 09 '19 at 20:44
  • Along with the problem you should **include the code you have up until this point** (*make some effort to answer your problem/question as [so] is not a code writing service*). After [doing more research](http://meta.stackoverflow.com/questions/261592) if you have a problem you can **post what you've tried** with a **clear explanation of what isn't working** and providing a **[mcve]**. I suggest reading [*How do I ask a Good Question*](/help/how-to-ask) and [*Writing the Perfect Question*](http://codeblog.jonskeet.uk/2010/08/29/writing-the-perfect-question/). Also, be sure to take the [tour]. – Igor Sep 09 '19 at 20:45
  • Please provide an example what you have attempted and where you need assistance. [How to ask](https://stackoverflow.com/help/how-to-ask) Most members on SO will help you as long as you put in the effort. – jrider Sep 09 '19 at 20:46
  • @techguy1029 I havent started yet, I need more of a high-level direction. My problem is i need to automate the checking of sysadmin privilege rights for a list of sql servers – Trey Cox Sep 09 '19 at 20:50

1 Answers1

1

What you're looking for is IS_SRVROLEMEMBER.

IS_SRVROLEMEMBER ( 'role' [ , 'login' ] )

Arguments

' role ' Is the name of the server role that is being checked. role is sysname.

Valid values for role are user-defined server roles, and the following fixed server roles:

sysadmin serveradmin dbcreator setupadmin bulkadmin securityadmin diskadmin

' login ' Is the name of the SQL Server login to check. login is sysname, with a default of NULL. If no value is specified, the result is based on the current Execution context. If the parameter contains the word NULL will return NULL.

| Return value | Description |

0 login is not a member of role.

1 login is a member of role

NULL role or login is not valid, or you do not have permission to view the role membership.

Ty Mayfield
  • 11
  • 1