Results 1 to 9 of 9
  1. #1
    Join Date
    Mar 2009
    Posts
    27

    Unanswered: Message Users before Killing Spid

    SQL Server 2008 R2

    Hi Guys,

    I was just asked by a client to help out by setting up code to kick all the users out from a specific login prior to running the nightly upload of data. Easy enough to kill the spids & disable the login until the nightly process finishes.

    However, it seems courteous to notify the poor suckers before I do that. Moreover it will keep them from calling up the system owner in the middle of the night. I was just reading that Net Send is no longer a good option since it is very often disabled to prevent spam. My users are not all internal and could be anywhere around the globe so I have no control over how they are connecting and what platform they are using other than that they are on a particular application that hits my db.

    Does anyone out there do this in a nice way? These users are on a .NET application and they all connect using the same login which has no associated nt_username. I'm thinking that maybe the application can send a 15 minute warning but what about those that try to connect after I've disabled the login? I can't email them since I have no idea who they are. Is Service Broker the answer? Should I use a login trigger rather than disabling the login?

    A quick Google did not turn up a clear idea of the current best practice--assuming there is one!

    Thanks for your help!

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    If you can easily change the application (not likely, but it happens), then you have some choices. Because you have no way to identify the users and no consistant messaging options (NET SEND, MSN Messenger, Skype, etc), I can' think of any way to "broadcast" a message to the users.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Mar 2009
    Posts
    27
    Thanks, Pat. My thoughts as well--that the application is the best source. Just thought I'd see if anyone found a way to handle this on the server.

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Ain't no harm in trying

    Code:
    CREATE TABLE #WHO2 (
      SPID			varchar(255)
    , Status		varchar(255)
    , Login			varchar(255)
    , HostName		varchar(255)
    , BlkBy			varchar(255)
    , DBName		varchar(255)
    , Command		varchar(255)
    , CPUTime		varchar(255)
    , DiskIO		varchar(255)
    , LastBatch		varchar(255)
    , ProgramName	varchar(255)
    , SPID2			varchar(255)
    , REQUESTID		varchar(255))
    GO
    
    INSERT INTO #WHO2
    EXEC sp_who2 Active
    GO
    
    DECLARE @CMD varchar(8000), @Domain varchar(255)
    SET @Domain = 'PRUDENTIAL\'
    
    DECLARE myCursor99 CURSOR
    FOR
    SELECT 'NET SEND ' + REPLACE([Login],@Domain,'') + ' "Shutting Down Database in 5 minutes.  Please save your Work"' AS CMD
    FROM #WHO2
    WHERE CONVERT(int,SPID) > 50
    
    
    OPEN myCursor99
    
    FETCH NEXT FROM myCursor99 INTO @CMD
    
    WHILE @@FETCH_STATUS = 0
      BEGIN
    	EXEC master..xp_cmdshell @cmd
    	FETCH NEXT FROM myCursor99 INTO @CMD
      END
    
    CLOSE myCursor99
    DEALLOCATE myCursor99
    GO
    
    DROP TABLE #WHO2
    GO
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Mar 2009
    Posts
    27
    Hey Brett,

    Thanks, that is the way I've done it in the past. What I'm reading now is that for users such as mine, it's quite probable that Net Send will be disabled as part of spam filtering. Hence, I'm not confident that I'll get the message out via Net Send. Otherwise it is a grand solution!

    G

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    ...gotta read the ENTIRE Post...god bless america....@#$%$%^&#$%^#$%
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Mar 2009
    Posts
    27
    Dang details!

  8. #8
    Join Date
    Aug 2004
    Location
    Dallas, Texas
    Posts
    831
    Quote Originally Posted by FlyGirl View Post
    Hey Brett,

    Thanks, that is the way I've done it in the past. What I'm reading now is that for users such as mine, it's quite probable that Net Send will be disabled as part of spam filtering. Hence, I'm not confident that I'll get the message out via Net Send. Otherwise it is a grand solution!

    G
    Help me with the confusion here. NetSend is a network communication method. How is this considered spam on the local network? There is an smo method to send these types of notifications at the application layer. The ad is a good place to start.

  9. #9
    Join Date
    Mar 2009
    Posts
    27
    @corncrowe

    It's not that Net Send is spam, its that spammers have found ways to use it to create pop ups and so it is now commonly blocked by firewalls and anti spam software. Since I have no way to test whether my users will get a Net Send message, I'm looking for another option. I may use Net Send, just because I can, but my current Google search indicated that it is no longer an effective way to get 'the message' to users.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •