December 16, 2014 at 12:43 pm
I am looking for scripts that are used to check security related issues in Microsoft SQL Server 2008/2008R2/2012
December 18, 2014 at 1:08 pm
You are being too vague... but... here is one to start with to see who has what type of 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
December 18, 2014 at 2:01 pm
A standard set of checks that is commonly used in some private sector environments is the IASE STIGs.
They used to have version for SQL Server 2005 and 2008 on the site but since they have upgraded the documents to SQL Server 2012 they ended up removing older versions.
The checklist are divided up by installation and then database each with their own checks for each. I used these constantly as a DBA contractor with DISA. I have not gone through all of the current version. You can also find checklist for Operating System versions as well on this site.
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
December 18, 2014 at 5:28 pm
Many Thanks. That is what I was looking exactly. Do you know any script that lists all users with all their permissions in all databases on a particular server/instance.
December 26, 2014 at 12:28 am
Please refer to the following article to get the script to get logins, databases users/roles and object level permission (T-SQL):-):
https://gallery.technet.microsoft.com/scriptcenter/Get-logins-databases-816f66b2
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply