September 23, 2018 at 10:21 pm
How do we tackle rampant SA permission issues.
We have many sql servers and 1000's of accounts which have been given SA permission without any scrutiny.
Now we need to revoke SA from all of those accounts which don't need it and without breaking any process and zero interruptions.
What is the best way to tackle this situation?
September 24, 2018 at 10:16 am
Are these app accounts or user accounts?
September 24, 2018 at 1:31 pm
Make a list of accounts you want to retain SysAdmin and add these logins to a CTE (values or union all).
Using Registered Servers add all the servers to a server group.
Right click the group and select new query.
Create a cursor to iterate sys.server_principals and run a sp_executesql to remove them from the SysAdmin role using a left outer join where null against the CTE to exculed the login you want to retain as SysAdmins.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply