SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Windows Account with sysadmin when it shouldn't


Windows Account with sysadmin when it shouldn't

Author
Message
enriarg
enriarg
SSC Journeyman
SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)

Group: General Forum Members
Points: 95 Visits: 414
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?.
Erland Sommarskog
Erland Sommarskog
SSCertifiable
SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)

Group: General Forum Members
Points: 5222 Visits: 875
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
enriarg
enriarg
SSC Journeyman
SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)

Group: General Forum Members
Points: 95 Visits: 414
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.
Erland Sommarskog
Erland Sommarskog
SSCertifiable
SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)

Group: General Forum Members
Points: 5222 Visits: 875
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search