Auditing Logins with sysadmin role

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Auditing Logins with sysadmin role

Author

Topic

TonyTheDBA
Posting Yak Master

121 Posts

Posted - 2009-03-30 : 07:44:00

Hi all, I would like to audit the use of sysadmin level accounts on my Sql Servers (Mostly 2000, a couple of 2K5, and most recently 2K8), as I suspect (but can't prove) that our developers are adding themselves to the SQL Administrators group, changing the Live servers, then removing themselves . . . No denying access to the group would not be acceptable . . . unless I can get proof.

I have a stored procedure based on sp_who that does audit particular logins, but want to change this to include any members of the sysadmin server role. .. . mostly working apart from one thing Logins granted access via an AD Group do not show up . . .

I'm using

SELECT IS_SRVROLEMEMBER( 'sysadmin' , p.loginame ) IsSysAdmin
     , * FROM master.sys.sysprocesses p

to get the current logged in users, but IS_SRVROLEMEMBER returns NULL for any user that has been granted a login via an AD Group.

For myself if I use

SELECT IS_SRVROLEMEMBER( 'sysadmin' ) -- Current Logged in User

I get a 1 returned . . . The current user is a member of sysadmin . . . and I do not have an explicit login on that box only as a member of DOMAIN\SQLServerAdmins. if I use

SELECT IS_SRVROLEMEMBER( 'sysadmin' , 'DOMAIN\MyLogin') -- A specific login User

I get a NULL returned . . . The documentation implies that SQL Server is unable to resolve the indirect login ???

Any Ideas?

--
Regards
Tony The DBA

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-03-30 : 10:47:43

If they can add themselves to the admin group, then they must have admin privileges already which probably means they already have sysadmin access and do not need to go through these steps. Could you explain how it would be possible to do what you think they are doing if they don't already have access?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

revdnrdy
Posting Yak Master

220 Posts

Posted - 2009-03-30 : 15:07:11

Also;

You can run the query below (works for SQL2005 but untested on other SQL flavors). This query will tell you who is a sysadmin. It will allow you to purge anyone who should not have the privilege. You can check back after the purge (a week later maybe) to see who has been added to the list without authorization.

--WHO ARE THE SYSADMINS

SELECT rol.name, mem.name,mem.type_desc
FROM sys.server_role_members AS srm
INNER JOIN sys.server_principals AS rol ON rol.principal_id = srm.role_principal_id
INNER JOIN sys.server_principals AS mem ON mem.principal_id = srm.member_principal_id
WHERE rol.name = 'sysadmin'

By the way I had this same issue and its how we identified and solved it. We found a user that was getting sysadmin through an old account.

r&r

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources