Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Windows Account with sysadmin when it shouldn't Expand / Collapse
Author
Message
Posted Tuesday, April 29, 2014 6:59 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Yesterday @ 12:57 PM
Points: 21, Visits: 239
Hello guys, I am sorry if the question is fairly simple but I haven't been able to find any solution.

We found out an account that was given issues, and recreate it, but we where still able to connect, I have already check all AD groups in the instance and I haven't found this account to be a member of any of them, is there another way for this account to gain the sysadmin permissions bedsides direct assignation or be a member of a group with sysadmin permissions?.
Post #1565895
Posted Tuesday, April 29, 2014 7:44 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, December 12, 2014 1:02 AM
Points: 823, Visits: 753
This query

SELECT u.name
FROM sys.server_principals u
JOIN sys.server_role_members rm ON u.principal_id = rm.member_principal_id
JOIN sys.server_principals r ON r.principal_id = rm.role_principal_id
WHERE r.name = 'sysadmin'

Will give you all members of sysadmin. If there are any server roles listed, you can unwind them the same way.

What particularly comes to mind is that BUILTIN\Administrators may be there and the account is a administrator on the machine itself.


Erland Sommarskog, SQL Server MVP, www.sommarskog.se
Post #1565930
Posted Tuesday, April 29, 2014 7:52 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Yesterday @ 12:57 PM
Points: 21, Visits: 239
I have already check the local administrator, I was more looking like

Result = "..\AdminTest"

A way to find if there is an impersonation method or another way to grant this permission to accounts.
Post #1565933
Posted Tuesday, April 29, 2014 8:49 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, December 12, 2014 1:02 AM
Points: 823, Visits: 753
Can you run this:

EXECUTE AS LOGIN = 'Domain\grouplogin'
go
SELECT u.name, u.type_desc, CASE WHEN u.type = 'G' THEN is_member(u.name) END AS ismeber
FROM sys.server_principals u
JOIN sys.server_role_members rm ON u.principal_id = rm.member_principal_id
JOIN sys.server_principals r ON r.principal_id = rm.role_principal_id
WHERE r.name = 'sysadmin'
go
REVERT



Erland Sommarskog, SQL Server MVP, www.sommarskog.se
Post #1565981
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse