June 18, 2019 at 7:25 pm
Can someone please copy/paste or provide me the script for this?
The list of of servers and the account have been provided in a separate Excel Spreadsheet , where the 1st column is the "name" and the 2nd one is the user.
I have sysadmin on all the boxes.
How can I do this?
What I have in mind is just somehow, specify the path for the Excel file in the Power Shell command and just run it from one of my existing servers via PoweShell command interface.
Thanks in advance.
June 18, 2019 at 8:00 pm
this is called being lazy - this is a task that you would not be performing that often so just creating a sqlcmd execute query command line string on the excel itself and then pasting onto a command prompt would do the trick without the need for any scripting tool. and would only take a few mins to do!!!
and on top of that there are plenty of examples on the net with powershell reading excel files and performing operations on SQL Server with the resulting recordset.
One of those examples here - do the remaining work yourself as it is also a way of you learning something https://devblogs.microsoft.com/scripting/use-ace-drivers-and-powershell-to-talk-to-access-and-excel/
June 18, 2019 at 8:17 pm
1st... do NOT reply if you are not going to add anything useful to the thread. You're wasting my time.
2nd, this task will be done on several batches on a few thousands servers. It is NOT more efficient or better to do in the way you are suggesting. I may not know power shell very well, but you seem to be a very rookie DBA, based on your reply.
And Yes... there's Google but did not find what I am looking for, hence I am posting here. This is a very specific place to ask these type of questions.
June 18, 2019 at 10:43 pm
you were given 2 options - one easy and the other requiring you do do a tiny bit of code yourself - either option are useful even if not an answer that would mean you did not do any work at all.
June 19, 2019 at 2:23 pm
Like I said ...
If you are not going to give any useful information , which means, posting an actual code instead of links to other sites, do not bother to reply. As a matter of fact, I won't bother to reply your posts anymore, it is a waste of time.
June 19, 2019 at 4:54 pm
2nd, this task will be done on several batches on a few thousands servers. It is NOT more efficient or better to do in the way you are suggesting.
1st, do not be rude. He was trying to help but unless you're paying his time, he's not required (none of us are) to just hand you a full script.
2nd, why in heavens name would anyone grant someone sysadmin to a few thousand SQL Servers unless they were trying to hack other people's systems?
If it were a legitimate request in my organization, I'd do a great deal of pushback on this request. No single person should have sysadmin to that many servers. And if they truly need it, why can't they be added once to the Active Directory windows security group that is sysadmin on the servers? Single users shouldn't be added to SQL Server. Groups should be used to manage this type of access just so people don't have to deal with this type of maintenance headache.
3rd, Word is already going around not to help you because you're demanding a full script without showing you've made any effort to figure it out yourself (you never said anything in your first post about Googling and you haven't posted stuff you tried and failed at). Also, rudeness. So honestly I'll be surprised if anyone does respond to you. Why would they if you're going to slam them for making an effort?
June 19, 2019 at 4:58 pm
Gee, I was able to take the code example provided by Fredrico, and with a little trial and error because I am not that great with Power Shell, get it to connect to a list of servers that I pulled from an Excel spreadsheet, and then write the results to a new Excel spreadsheet.
What have you tried? What may is not working? Can you post the code you tried so that folks can actually help you, as opposed to doing your job?
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
June 19, 2019 at 5:46 pm
As the main moderator here, please refrain from arguments over responses. The first reply did give you an easy hint, caveating this as "unlikely to be done often". If that doesn't meet your requirements, a simple "I'd like to do this in PoSh" or ask for more information about Excel.
If you want to do this in PoSh, a few references that can help:
Though these days, I'd use dbatools and Set-DbaLogin ( https://docs.dbatools.io/#Set-DbaLogin)
If this won't be done once, which is likely, then either of these is a poor choice. Really, you ought to have an AD group that is for DBAs that need sysadmin. Whether they are actually DBAs, other system administrators for Windows, or devs, a role is the way to handle this. Then you would only add the AD group to the sysadmin role once and it would apply each time you add or remove a sysadmin. To do that, a similar process with Set-DbaLogin is what I'd use.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply