February 23, 2016 at 12:24 pm
HI,
I have a user that an A/D account that has no access to a production DB. But I caught that user today running a query on my production server!
If I look at that user, he has no roles, and no access! He does have read/write on other DB's but not the DB I caught him on.
How can I figure out how he is getting in?
February 23, 2016 at 12:26 pm
February 23, 2016 at 3:14 pm
Look for A/D groups that do have access that he belongs to.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 23, 2016 at 3:30 pm
Are you sure that user doesn't have access to accounts they shouldn't?
Your environment should have some sort of IP assignment control in the domain and IP filtering in the firewalls. There are numerous ways to watch/record/audit/alert on traffic and login attempts by IP or Machine Name to various layers of your sql box.
February 23, 2016 at 4:32 pm
The first thing is to figure out how they were connected when running the query.
They could have been using a SQL login they should or should not have.
They count be using their own AD account, or someone else's AD account. If it's their own account, you can check what AD roles the account has by using a DOS command:
net user username /domain
If they're using an AD account other than their own, you have a completely different problem on your hands. That's typically a pretty major offense.
February 24, 2016 at 12:14 am
Try this in your SQL
xp_logininfo 'Domain\account' the account might be in some groups
February 24, 2016 at 6:41 am
OKay I checked every single group and he is not getting in via group membership.
I also know that he is using his domain account (domain\user).
When I run - xp
account nametypeprivilegemapped login namepermission path
domain\useruseruser domain\user NULL
Can't figure out how he is getting in so I setup a trigger to alert me when he logs in. Not sure what else to do next.
February 24, 2016 at 6:50 am
It's not magic. It has to be something. Either a SQL login that he has the password for, or he's in a group that's in a group that has access. Also check all the roles on the server and the databases, not just the logins.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 24, 2016 at 7:00 am
If he's using his domain account, then either his account has been granted access, or he's a member of a group that has been granted access. Check the logins, check which are AD groups, who their members are.
And make sure you don't have something like <domain>\Authenticated Users as a group granted login access.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 24, 2016 at 7:17 am
Also consider the possibility of different logins. They could be using a SQL login, their own AD account or a different AD account.
February 24, 2016 at 8:22 am
What evidence do you have exactly?
Your user could be using impersonation to execute as another user.
February 24, 2016 at 11:05 am
i use this to enumerate all the members that belong to groups that have been granted access from AD:
i've found that some groups cannot be enumerated int he cases where you have trusts between two domains, and the group is from the foreign domain, or the local group contains foreign domain members.
IF OBJECT_ID('tempdb.dbo.#tmp') IS NOT NULL
DROP TABLE #tmp
CREATE TABLE [dbo].[#TMP] (
[ACCOUNT NAME] NVARCHAR(256) NULL ,
[TYPE] VARCHAR(8) NULL ,
[PRIVILEGE] VARCHAR(8) NULL ,
[MAPPED LOGIN NAME] NVARCHAR(256) NULL ,
[PERMISSION PATH] NVARCHAR(256) NULL )
DECLARE @groupname NVARCHAR(256)
DECLARE c1 CURSOR FOR SELECT name FROM master.sys.server_principals WHERE type_desc = 'WINDOWS_GROUP' AND CHARINDEX('$',name) = 0
OPEN c1
FETCH NEXT FROM c1 INTO @groupname
WHILE @@FETCH_STATUS <> -1
BEGIN
BEGIN TRY
INSERT INTO #tmp([ACCOUNT NAME],[TYPE],[PRIVILEGE],[MAPPED LOGIN NAME],[PERMISSION PATH])
EXEC master..xp_logininfo @acctname = @groupname,@option = 'members' -- show group members
END TRY
BEGIN CATCH
DECLARE @ErrorSeverity INT,
@ErrorNumber INT,
@ErrorMessage NVARCHAR(4000),
@ErrorState INT
SET @ErrorSeverity = ERROR_SEVERITY()
SET @ErrorNumber = ERROR_NUMBER()
SET @ErrorMessage = ERROR_MESSAGE()
SET @ErrorState = ERROR_STATE()
PRINT 'Msg ' + convert(varchar,@ErrorNumber) + ' Level ' + convert(varchar,@ErrorSeverity) + ' State ' + Convert(varchar,@ErrorState)
PRINT @ErrorMessage
END CATCH
FETCH NEXT FROM c1 INTO @groupname
END
CLOSE c1
DEALLOCATE c1
SELECT * FROM [#TMP]
--WHERE [MAPPED LOGIN NAME] = 'mydomain\Lowell'
Lowell
February 24, 2016 at 8:41 pm
Lowell (2/24/2016)
i use this to enumerate all the members that belong to groups that have been granted access from AD:i've found that some groups cannot be enumerated int he cases where you have trusts between two domains, and the group is from the foreign domain, or the local group contains foreign domain members.
IF OBJECT_ID('tempdb.dbo.#tmp') IS NOT NULL
DROP TABLE #tmp
CREATE TABLE [dbo].[#TMP] (
[ACCOUNT NAME] NVARCHAR(256) NULL ,
[TYPE] VARCHAR(8) NULL ,
[PRIVILEGE] VARCHAR(8) NULL ,
[MAPPED LOGIN NAME] NVARCHAR(256) NULL ,
[PERMISSION PATH] NVARCHAR(256) NULL )
DECLARE @groupname NVARCHAR(256)
DECLARE c1 CURSOR FOR SELECT name FROM master.sys.server_principals WHERE type_desc = 'WINDOWS_GROUP' AND CHARINDEX('$',name) = 0
OPEN c1
FETCH NEXT FROM c1 INTO @groupname
WHILE @@FETCH_STATUS <> -1
BEGIN
BEGIN TRY
INSERT INTO #tmp([ACCOUNT NAME],[TYPE],[PRIVILEGE],[MAPPED LOGIN NAME],[PERMISSION PATH])
EXEC master..xp_logininfo @acctname = @groupname,@option = 'members' -- show group members
END TRY
BEGIN CATCH
DECLARE @ErrorSeverity INT,
@ErrorNumber INT,
@ErrorMessage NVARCHAR(4000),
@ErrorState INT
SET @ErrorSeverity = ERROR_SEVERITY()
SET @ErrorNumber = ERROR_NUMBER()
SET @ErrorMessage = ERROR_MESSAGE()
SET @ErrorState = ERROR_STATE()
PRINT 'Msg ' + convert(varchar,@ErrorNumber) + ' Level ' + convert(varchar,@ErrorSeverity) + ' State ' + Convert(varchar,@ErrorState)
PRINT @ErrorMessage
END CATCH
FETCH NEXT FROM c1 INTO @groupname
END
CLOSE c1
DEALLOCATE c1
SELECT * FROM [#TMP]
--WHERE [MAPPED LOGIN NAME] = 'mydomain\Lowell'
Absolutely awesome! You have no idea how much this is going to help me. You should write an article on it, Lowell. Seriously!
--Jeff Moden
Change is inevitable... Change for the better is not.
February 25, 2016 at 5:47 am
Is it possible to run profiler and see what userid is connecting and running that query?
February 25, 2016 at 6:56 am
Markus (2/25/2016)
Is it possible to run profiler and see what userid is connecting and running that query?
Agreed. Setting it up to find the name of the proc in the "text data" using RPC Completed and Batch Completed will be mostly benign for resource usage, as well.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply