Stupid question? How to exclude 'SA' using sp_who

Question

Stupid question? How to exclude 'SA' using sp_who

richlion2

SSCrazy

Points: 2484
More actions
November 16, 2016 at 2:13 am

#401066

Hello,
I am not acutally a beginner, but a simple task like filtering users seems to be a daunting task using the SP_WHO package.
To list sessions of the user SA we would run:
EXEC sp_who @loginame = 'sa'
go
So how do I list all users who are not 'sa' ?
I tried somthing pure SQL understands a human like me:
EXEC sp_who @loginame != 'sa'
go
but no, I tried Goole as usual, but not a single example. Please help?
Thank you
Richard

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

I don't think you can do it with sp_who out of the box. What you can do is look at the definition for sp_who and create a new stored procedure (sp_who3?) that has a parameter that excludes the specified login name.

John

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

Don't use sp_who (it dates back to pre-SQL 2000 and has not, afaik, been updated for the new DMVs and new information available)

Build your own query from the DMVs (sys.dm_exec_sessions and sys.dm_exec_requests would be the most likely to contain the info you need) and then you can filter as you like.

Or, if you don't want to write your own query from scratch, look up Glenn Berry's diagnostic scripts and use them as a template.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Neil Burton SSC-Insane Points: 22905 More actions · Answer 3

CREATE TABLE #temp_sp_who2

(

SPID INT,

Status VARCHAR(1000) NULL,

Login SYSNAME NULL,

HostName SYSNAME NULL,

BlkBy SYSNAME NULL,

DBName SYSNAME NULL,

Command VARCHAR(1000) NULL,

CPUTime INT NULL,

DiskIO INT NULL,

LastBatch VARCHAR(1000) NULL,

ProgramName VARCHAR(1000) NULL,

SPID2 INT

, rEQUESTID INT NULL --comment out for SQL 2000 databases

)

INSERT INTO #temp_sp_who2

EXEC sp_who2

SELECT * FROM #temp_sp_who2

WHERE LOGIN <> 'sa'

DROP TABLE

#temp_sp_who2

You could do something like that too.

On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
—Charles Babbage, Passages from the Life of a Philosopher

How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537

richlion2 SSCrazy Points: 2484 More actions · Answer 4

Thank you all for such a quick response. That looks like MS making life easier for us. I'll mark all answers as "answers", although the CREATE MY OWN TABLE seems like a funny solution, because in order to check what's going on every 1 minute it means DELETE/INSERT multiple times. However, I may go for it as more robust and simple. 😀

richlion2 SSCrazy Points: 2484 More actions · Answer 5

Ha, I decided to put the sp_who into an SQL script and run this in CMD:

sqlcmd -i who.sql | find /v "sa" | find /V "SQLSERVERAGENT" | find /v "Administrator" | find /V "NT SERVICE"

At least I got all my regular users listed.

Jeff Moden SSC Guru Points: 1003851 More actions · Answer 6

Why on Earth do you need to run such a thing once per minute?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Eric M Russell SSC Guru Points: 125519 More actions · Answer 7

When running sp_who2 or DMV queries from SSMS, rather than filtering on 'SA', perhaps excluding your own SPID is what you really want. It's entirely possible that another DBA (or even another user), is logged in as 'SA'.

WHERE SPID <> @@SPID

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho