Link to home
Start Free TrialLog in
Avatar of bryan oakley-wiggins
bryan oakley-wigginsFlag for United Kingdom of Great Britain and Northern Ireland

asked on

SQL Server 2005 Security Logins Audit

Hi

Environment:
Windows 2003 Forest (x86)
Windows 2003/2008 server (x86 / x64) STD and ENT editions
SQL 2005 (x86 / x64) STD and ENT editions

Maybe straight forward but I am in need of getting a report together pretty soon..!

What I am looking to do is use an input file of server hostnames (SQL servers) and run an audit on all the sql security logins on each server (including local groups and domain groups) and have the membership of these group made into an audit report?

I could take an input file from .csv and then a dsquery but I was hoping to see if there are better methods to run within sql to achieve this in a more automated manner?

I appreciate any guidance/pointers on where I may start (and yes, I have already began research on EE and Google ;-)

Cheers
Bry
Avatar of Paka
Paka

Are you after the local machine logins and groups or are you after the sql accounts themselves?   If you're after the sql accounts try:

select * from master.sys.server_principals
if you want to find sql account with principal, have a look at

http://www.sqlhub.com/2009/05/find-database-principal-and-its-member.html

SELECT
MemPri.Name as MemberPrincipal,     
RolePri.Name as RolePrincipal,
MemPri.Create_Date as DateCreated,
MemPri.Modify_date as DateModified,
MemPri.Type_Desc as Description
FROM
sys.database_role_members RoleMem
JOIN sys.database_principals RolePri ON RoleMem.role_principal_id = RolePri.principal_id
JOIN sys.database_principals MemPri ON RoleMem.member_principal_id = MemPri.principal_id
order by MemberPrincipal

Open in new window

Avatar of bryan oakley-wiggins

ASKER

Paka & RiteshShah: Thanks for your responses, very much appreciated.

What I am looking to do, is to be able to query a bunch of my SQL servers, say from a text file and find all security logins on all sql servers (local and domain) and then enumerate the members of these groups and then output all this data to a report format, HTML or .CSV or similar or even a flat text file would do for now?

any pointers appreciated :-)

Cheers
Bry

still not clear, all you want is, all login available in SQL server and output should be in CSV or HTML file.... is it something you want?
Hi RiteshShah

Apologies, i'll try and be more clear of my requirements.

For a security compliance audit, I want to audit several SQL servers for all sql logins, I.e BUILTIN\administrators, DOMAIN\GROUPA, DOMAINGROUPB etc and to report on who the members of each group are (these can be both local groups and domain groups in active directory).

This will help identify all groups membership within the sql login realm.

I hope this makes more sense.Cheers
Bry
well in that case my last script can give you "MemberPrincipal" which is login name and last column is "Decription" it shows you whether it is a SQL User or WIndows user. You can get that list in CSV file, have a look at how you can export sql results to CSV or TEXT file. have a look at my article.

http://www.sqlhub.com/2009/05/create-comma-separated-file-from-sql.html



I suspect that this task will be a bit more difficult that just a SQL query.  Since Active Directory Groups can be nested, this task will probably take a sql query and combined with some VBScript to expand the groups that come out of the sql query.
You only suspect but I am sure this can't be done with just a simple SQL Query, it needs to have support of scriptting language, all one can do is, get list of all user (may be with the query I provided) , sort out windows user and write script in C# or in VB.NET with the help of system.directoryservices namespace and found its group and node, have a look at small script for ADSI

http://www.willasrari.com/blog/query-active-directory-users-using-c/000133.aspx

In SQL server, one can add ADSI as linked server but it will be more difficult than writing the simple script in C#

Here's a VBScript that will iterate AD nested group membership:
http://www.tek-tips.com/viewthread.cfm?qid=1348699

If you don't have too many SQL servers, I'd recommend running the accounts query on each server manually, export them to separage files, clean the files up with Excel and then modify the script in the link above to expand the group membership.  If you could run the first three steps and give me a section of the output, I can help with the VBScript part of things.

Avatar of Chris Luttrell
I do not know if you were wanting a quick fix or how to set up a good audit routine to use going forward, but I think this will take several tools and steps and the best place to put it all together is probably with Powershell.  I am just getting into this myself so I can not provide the direct answer but here are some links to articles about Powershell starting with the first that is the list of articles by Muthusamy Anantha Kumar aka The MAK on Database Journal who has a plethora of information on Powershell, SMO and other SQL info and is the closest thing to an expert I have found so far.
http://www.databasejournal.com/article.php/3300441
http://www.simple-talk.com/sql/database-administration/why-this-sql-server-dba-is-learning-powershell/
HTH
CGL
Hi CGLuttrell:

Thanks for your response - I am just checking the links you sent over.
I too am just beginning on the powershell route, so will be interesting reading.

In the short term, I am looking probably for a 'quick fix', audit report but I will be working on developing a more automated and robust auditing model.

Basically, I want to use something like the following:
exec xp_logininfo
and dump all the data I get returned (including local windows groups and AD domain groups that have associated sql login) to a report file - Ideally would be to split out the command to separate the local and domain groups into seperate files if possible?

I have about 40 sql servers that I need to audit so I would like to use an input list of server names if possible.


@ Paka: - Thanks for your offer of vbscript help, very appreciated. I am a wintel/sys admin by trade so I am comfortable with the vbscript/AD side of things, I just wanted to explore the possibiity of running these queries within SQL.

On that note, if I run the following:
EXECUTE MASTER..XP_CMDSHELL 'DSQUERY.exe group'
I get a return of groups etc - Do you happen to now how I may put filters in the syntax within sql (I.e, DSQUERY group -name "example" | DSGET group -members)???

Again all, thanks so much for your responses..!

Cheers
Bry

Update:
I am now using the following powershell script (original script courtesy of  http://www.simple-talk.com/sql/database-administration/why-this-sql-server-dba-is-learning-powershell/ ) for which I am modifying to suit my needs ( (I have taken out the defined group to query back ALL groups on the sql server) and am working on running an AD search and enumerating all the nested domain groups memberships and also local windows groups members...

# Find a login or AD group on multiple servers
[reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | out-null
foreach ($svr in get-content "d:\scripts\AllServers.txt")
  {
            $srv=New-Object "Microsoft.SqlServer.Management.Smo.Server" "$svr"
 $srv.Logins | Select Parent, Name
  }


I shall update further when I get the query part working...
If anyone has any input for the query part, I would be most obliged..!

Cheers
Bry
@ CGLuttrell: - Thanks for the Simple-Talk.com URL link ;-)
Bryan, your welcome, glad to help.
whats the query part you are working on?  the xp_logininfo or the XP_CMDSHELL 'DSQUERY.exe group'?
I don't seem to have a system set up to try the xp_cmdshell but I can try to help with the xp_logininfo if you describe what you want from it.
cgl
Hi CGL

Thanks buddy.
I am now looking at working with powershell scripts for this, as I think it will enable me to develop a more defined process for mass auditing of my SQL estate (I am not a DBA by trade).

I have found SQLPSX (http://www.codeplex.com/SQLPSX) to extend powershell with SQL specifics, so I am investing time in looking at these commands to see if it gives me what I need.

As for xp_logininfo:
Can I pipe xp_logininfo into a DSQUERY statement within SQL do you happen to know?

Cheers
Bry

ASKER CERTIFIED SOLUTION
Avatar of Chris Luttrell
Chris Luttrell
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Hi CGL

Wow, this is really heading towards what I am after and I really and I mean really, appreciate your time and effort on this, hats off to you kind sir..!

I will have a look at the dsquery stuff.
One thing, would it be straight forward to output all this info into an excell sheet?

Once again, thanks so much
Cheers
Bry
good work CGLuttrell
oh yes, also it would be great if I could read in a bunch of server names from a text file? Would this be fairly easy to do in the SQL query as it is in vbscript, poweshell etc?

Cheers
Bry
I do not know of a way to put the output straight into excel, you would have to cut and paste from Management Studio.  If you are doing things aready in powershell and executing commands on the Sql boxes from there, what can you do with the output, can it be dumped to csv to use in excel?  
As for reading from a text file from inside the db, although that is definatelly possible, see http://www.simple-talk.com/sql/t-sql-programming/reading-and-writing-files-in-sql-server-using-t-sql/ , I would think managing it from the shell would be easier/better.
anyway, we will just keep building on this thing till we make it sing.
Hi CGL

Thanks again for your input and I really appreciate the "we will just keep building on this thing till we make it sing.", that is extrememly generous of you..!

Within powershell we can export to excel in a variety of ways, including output-csv, out-file etc...
Maybe I should look at calling the .sql query you are developing form within a powershell script and see if we can get the output directed to excel that way? I'll play around and see what I can get done.

Also on that, within powershell I can use a text file as input with the <get-content> cmdlet - I just need to see if I can pipe this info into the sql query you wrote???

I know there are lots of questions from me on this (as I say, i'm not a DBA by trade but wintel/ad/exchange engineer :-) but would you know how we may handle the windows local groups in the sql statement?

I.e when I run a command to get the sqllogins, it also list the local groups - I would also like to run a local query for members in these groups...

A big ask I know :-)
Anyhow, you need to get some rest..! So i'll catch up with you later...

thanks again buddy your input is most valuable...
Cheers
Bry
I haven't forgotten about this, just a work production issue came up and I have been busy with that :-(
I shall get back to this later today/tomorrow and update when I get something further.

Cheers
Bry
Bryan,
thanks for the update.  I am in sort of the same situation, the executive sponsor of our company's largest project is in town this week and I am having to devote most of my time durring and after work to these meetings so I am very busy myself.  Let me know when you have an update, I will monitor and get back as quick as I can.
cgl
Thanks CGLuttrell:

I will for sure update as soon as I get chance to have a further look at this, prod issue has developed and is swallowing up my time - I will be working on this again during this week (when I get the odd chance to) but will for sure be able to work on it this week-end without doubt..!

Again thanks for your involvement, very valuable and very appreciated..!

Cheers
Bry
Hi

apologies for delay, workload is just huge at the moment...
I will get back with some code later in the week, as a DBA I work with has been looking at this now and has come up with something - I am just helping him with some dsquery | dsget syntaxes and we should be good to go...

As soon as I can, I will upload the code/sql statements that are being used..!

and hopefully soon, I will get to look at the Powershell and SMO..

Cheers
Bry
Hi

Again apologies for delay - In the middle of a massive migration project and it's just trying to find the time to get this solution worked on..!

I will endeavour to get some further code up here v.soon...

Thanks again
Bry
Hi CGLuttrell

Sorry for delay - The DBA chap is a bit reluctant to hand-up the code he has done (for the moment anyhow) - so I guess I will award the points to you for your stellar effort and time in the initial phases...

It is very appreciated.

@ CGLuttrell - if you want to PM me so that I can notify *if* I get the full code from the DBA, pls contact me at (rockdabeatz AT gmail DOT com)

Thanks again all for your responses and assistence - I am just sorry I cannot upload a full process :-(

Cheers
bry