NOT GOOD WITH SQL COMMANDS

Question

NOT GOOD WITH SQL COMMANDS

morrismcp

Valued Member

Points: 64
More actions
December 17, 2020 at 7:52 pm

#3821739

Hi,
Was seeking help.

Trying to create a sql job that will remove systemadmin from all users except sa and two others. Anyone know of a script that will do that?

Thanks
December 22, 2020 at 11:24 am

This was removed by the editor as SPAM

Viewing 8 posts - 1 through 7 (of 7 total)

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

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 1

The first step would be to list the logins that are a member of the syadmin role - to get that you query sys.server_principals and sys.server_role_members:

 Select *
   From sys.server_principals            sp
  Inner Join sys.server_role_members    srm On srm.member_principal_id = sp.principal_id
  Inner Join sys.server_principals      sp2 On sp2.principal_id = srm.role_principal_id
  Where sp2.name = 'sysadmin';

Once you have that you can either use a cursor or build a string to be executed that executes 'Alter Server Role'.

With that said...why do you need to do this? How are additional sysadmin accounts being created and/or logins being added to the sysadmin role? You need to identify that process and disable it or block it from making those changes.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Mr. Brian Gale SSC-Insane Points: 24800 More actions · Answer 2

Like Jeffrey, I would be cautious about removing sysadmin via an automated task. One typo in that script and you could blow out all sysadmins.

Permissions are something I prefer not to automate. Far too easy for some weird edge case cropping up (like a ; character in a login name) or malicious actor who knows your process to screw the whole thing up.

And it becomes a pain in the behind if those 2 others end up changing. For example, what if one of those 2 others needs to be disabled as they leave the company? Or you want to add a 3rd person to that list.

It would be much better to use the script Jeffrey gave to find the users who are sysadmins and then remove them and have a report or process in place to watch for the number of sysadmins to go over 3 and if it is more than 3, send an alert to the DBA's and they can investigate if it is valid or not. Still run into the problem of needing to know how many sysadmins there should be, but if this is a monthly report that is emailed out, it gives you a chance to review it and correct as needed.

The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

morrismcp Valued Member Points: 64 More actions · Answer 3

Hi,

Thanks everyone,

To answer the first question, the reason it's needed is because the software being used requires systemadmin rights to install(yea I know) so what is happing when the site is built an account with systemadmin rights needs to be added to do the install. What I'm finding is site builders are forgetting to take away systemadmin rights once the site/database is built. So want to have a script that would scan and if any login other than SA or our management account remove that role.

Thanks.

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 4

This would be much better as a daily/weekly report - that you can then take action on if there are new sysadmin accounts. There are other accounts besides sa that have sysadmin rights - and those accounts must have sysadmin.

If you run the script I provided you will see the additional accounts.

I would create an email that sends me a notification on a schedule - and if there are new accounts found I can then login to that server and issue the appropriate alter server role - or disable or remove the accounts.

Additionally, I would be very concerned with any application that allows end users (site builders) the ability the create sysadmin accounts. There is no reason for that requirement and most likely it is because the system has not been set up correctly.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

morrismcp Valued Member Points: 64 More actions · Answer 5

Hi Jeffrey,

Thanks so the basic process is this; Employee gets a new site install, 1st step is to create a sql account for that client with SA rights. Run the installer that creates the database using that account. Once the site is up and running go back in and remove sysamin rights from the user created and just give db owner.

So what is happening is employees are forgetting to make that change. Was looking for a script to run automatically each day instead of having to manually go back in and look as we have over 15 sql servers.

Really appreciate your assistance.

Sergiy SSC Guru Points: 110208 More actions · Answer 6

I'd suggest having a job running through all 15 servers, identifying such sysadmin users left behind and sending out a notification with details of such users, if found.

But altering the user rights better to be done by a human.

_____________
Code for TallyGenerator