SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Periodically Reviewing SQL Server Permissions


Periodically Reviewing SQL Server Permissions

Author
Message
defyant_2004
defyant_2004
Valued Member
Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)

Group: General Forum Members
Points: 69 Visits: 146
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!
Andrew G
Andrew G
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2523 Visits: 2254
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.
defyant_2004
defyant_2004
Valued Member
Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)

Group: General Forum Members
Points: 69 Visits: 146
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?
Shawn Melton
Shawn Melton
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2395 Visits: 3513
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
PS C:\>(Find-Me).TwitterURL
@wsmelton
PS C:\>(Find-Me).BlogURL
meltondba.wordpress.com
Markus
Markus
Hall of Fame
Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)

Group: General Forum Members
Points: 3161 Visits: 3778
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



defyant_2004
defyant_2004
Valued Member
Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)

Group: General Forum Members
Points: 69 Visits: 146
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?
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search