April 29, 2014 at 6:59 am
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?.
April 29, 2014 at 7:44 am
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.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
April 29, 2014 at 7:52 am
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.
April 29, 2014 at 8:49 am
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
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply