July 1, 2011 at 8:43 am
We have an audit where I have to show and active directory account as being 'denied'.
I usually run 'sp_helpuser' but that does not show my Active directory account as being 'denied' (permission to connect to database engine) access - Which is what I need to show
How do I show the account status using T-SQL?
July 1, 2011 at 9:07 am
How is it denied?
Have you denied it by placing it in an AD group that is denied at the login level?
Is the login itself listed as a login in SQL Server and then denied all access rights?
Did you do something in AD to deny it access to the database server?
Something else?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 1, 2011 at 9:10 am
No , in the GUI I click on the group then 'status' then I click 'deny' under permission to connect to database engine.
It shows 'deny' for the group...but when I ran any scrip against sys tables it shows 'Grant' for that group. Should say 'denied'
Here's some of the scripts I've been using ...pretty basic stuff..
SELECT
USER_NAME(dppriper.grantee_principal_id) AS [UserName],
dppri.type_desc AS principal_type_desc,
dppriper.class_desc,
OBJECT_NAME(dppriper.major_id) AS object_name,
dppriper.permission_name,
dppriper.state_desc AS permission_state_desc
FROM sys.database_permissions dppriper
INNER JOIN sys.database_principals dppri
ON dppriper.grantee_principal_id = dppri.principal_id
I can't seem to show it as denied in TSQL, when I open the GUI it shows as 'deny'....wierd..
thinking maybe the script Im using isn't right?..
July 1, 2011 at 4:16 pm
In you r first post you mention the database engine, but your query uses database specific permission tables.
You need to query sys.server_permissions. Look for DENY CONNECT permissions on any TSQL or SOAP ENDPOINTS for the AD user. DENY overrides any GRANT permissions, so the AD login should have DENY CONNECT on all TSQL and SOAP endpoints.
July 5, 2011 at 6:25 am
Permissions on tables shouldn't matter.
Try connecting using that login. Should be blocked.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply