April 30, 2014 at 1:11 pm
Hello,
I am very interested in knowing how experienced DBAs go about periodically reviewing who has access their SQL Servers and associated databases. While specific scripts and/or instructions would be appreciated, I am also interested in general advice on best practices to periodically reviewing server and database permissions.
The basis of my question is trying to prepare for possible security audits in the future. I would like to know how experienced DBAs go about this.
Thanks for any advice, information, scripts, or links you would be willing to share!
May 1, 2014 at 1:43 am
In my opinion, it's up to the system owner/admin to review permissions. I can help generate a list of who has access, but then its up to you to know who, what, and why people have that level of access.
The other way of doing this is with AD groups.. name the groups appropriately, then who has access is not my problem. Maybe setup an automatic report to goto the system owner either on a schedule or when users are added to roles.
May 1, 2014 at 8:18 am
It would seem to me that with laws like Sarbanes-Oxley, that a lot of DBAs would be faced with providing information/reviews of who has access to the databases they administer in order to prove/satisfy requirements that they are properly managing server and database access. Are there some standard reports or scripts that are accepted by the SQL Server community for these situations? Are there some 3rd party tools available for these situations?
May 1, 2014 at 8:46 am
Experienced DBAs go about this area by checking with their management and find out what is required of them. With PCI, HIPPA, and any other acronym that involves security standards for a particular industry, they each have their own requirements for periodically verifying user access and status. You also include review of the security logs, trace files if captured, and any other logging to ensure no one is attempting unauthorized access with an account that either does or doesn't exist.
It may be up to the application or system owner to review and approve permissions but I can guarantee you most DBAs are going to know who has permissions to the data they are responsible for protecting. I may not be able to tell an auditor why that account is there but I can tell you what it can and can't do at the server level and database level.
Each company/agency is different but most I have been involved with have a security group that are responsible for this type of stuff for the company as a whole, or for each department. They could also just be the middle-man for the external auditors that come in on an annual basis or every certain number of years.
That same group may know or be responsible for gathering the documentation mandated by particular security standards that have to be presented to the auditors. It is not enough to just show them through SSMS an account has certain permissions. I managed database security for servers as a DoD contractor and I did not directly write the documentation but provided the information to the group of individuals that needed it.
With DoD they follow the IASE Security Technical Information Guides (STIGS) that you can find here.
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
May 1, 2014 at 8:55 am
Here is a script that all DBA's should run periodically even if you think you know who may have 'sa' and or other rights within SQL Server. Once in a great while a new install will have a userid need these rights to install and then you may forget to drop this higher rights.
Select
'Login Name'= Substring(upper(SUSER_SNAME(SID)),1,40),
'Login Create Date'=Convert(Varchar(24),CreateDate),
'System Admin' = Case SysAdmin
When 1 then 'YES (VERIFY)'
When 0 then 'NO'
End,
'Security Admin' = Case SecurityAdmin
When 1 then 'YES (VERIFY)'
When 0 then 'NO'
End,
'Server Admin' = Case ServerAdmin
When 1 then 'YES (VERIFY)'
When 0 then 'NO'
End,
'Setup Admin' = Case SetupAdmin
When 1 then 'YES (VERIFY)'
When 0 then 'NO'
End,
'Process Admin' = Case ProcessAdmin
When 1 then 'YES (VERIFY)'
When 0 then 'NO'
End,
'Disk Admin' = Case DiskAdmin
When 1 then 'YES (VERIFY)'
When 0 then 'NO'
End,
'Database Creator' = Case DBCreator
When 1 then 'YES (VERIFY)'
When 0 then 'NO'
End
from Master..SysLogins order by 3 Desc
May 1, 2014 at 9:39 am
Thanks for all the replies. I am going to throw this question out there.
If an auditor came to you and said "I need to see who has access to your SQL Servers and the databases on them". How would you fulfill this request?
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply