Security Audit: Why dosn't the dbo.secmst display the SUPERUSER as a Security Policy Type

[jeff.hamway01]

Hello Admins,

Every quarter,  we do  a security audit as part of or SOX compliance.  I noticed that the SuperUsers are not listed when I pull my report.

select dbo.usrmst.usrmst_fullname, usrmst_name, dbo.secmst.secmst_name
from dbo.usrmst
join dbo.secmst ON usrmst.secmst_id = dbo.secmst.secmst_id
order by usrmst_fullname asc

Does anyone have a script that actually shows who has superuser access? 

Thanks

Jeff

[brian_dickens · Accepted solution]

superuser rights are kept in a different column. I don't know why this is.

Column is usrmst_suser.

Values are Y, N, R, P.

Looks like Y indicates an interactive login with superuser rights. R is a runtime user. I don't have any records with a P value so I can't tell what that is. I think what you're looking for is usrmst_suser = 'Y'

[jeff.hamway01]

Thank you Brian,

I have updated my script to be more functional.  Much Appreciated. Hope this helps some other people.

select dbo.usrmst.usrmst_fullname as 'Full Name', usrmst_name as 'User Name', dbo.secmst.secmst_name as 'Security Profile', usrmst_suser as 'Super User'

from dbo.usrmst

join dbo.secmst ON usrmst.secmst_id = dbo.secmst.secmst_id

where usrmst_suser = 'y' or dbo.secmst.secmst_name like '%%'

order by usrmst_fullname asc