April 26, 2006 at 8:36 am
Hi,
I have joined this company (not telling which) is in total mess with regards to SQL Server Security, over 50+ SQL Server have all logins as SYSADMIN (belive me, I am not joking, I have audited the servers) but thats not all, [domainname\Domain Users] also have sysadmin rights. Now u know what kind of mess I am talking about.
Now what should be my approch of clearing this mess. Remember, this is 24 x 7 shop, can't simply remove the groups, (users I can handle). I can't go to each and every user and ask about there work profile and decide on there privilages.
April 26, 2006 at 9:27 am
First document what you've found, then outline the issue, if you can get an assessment on risk and potential cost to the company, put that together, and take it to your management. Help them to understand the problem. If your company has to comply with SOX or SAS70 or any of the other compliance mechanisms, that should be a wake-up call. That would hopefully get them engaged, because you're going to need help from the business on this one.
I'd also start trying to get the documentation on the applications you support, especially vendor installation/configuration and your org's technical recovery documentation (if your organization doesn't have those, that's a bad sign), and scour them for permissions, etc.
That's where I'd start. You can audit all you want using SQL traces, but likely you're going to miss something because there is always that once a month, once a quarter, or once a year routine that gets executed when you aren't auditing. And you're going to need backup and support from management for if there is an issue when you start trying to tighten down security.
K. Brian Kelley
@kbriankelley
April 27, 2006 at 10:31 am
Agreed. I am challenged with similar issues here. If you can create roles, then make use of that as well. We have done that here and then you can assign permissions to the roles. For example, if you have a group of users who both need to view and update data, create a DataReaderWriter group (or something similar), then you can manage permissions from there. If this group needs permissions to execute procs, then you only need to grant perms to that role, not each individual. (A lifesaver for me!) Move slow and as bkelley said, make sure you have management support. You'll most likely run into someone who doesn't want to relinquish the permissions they have. Good luck.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply