Post reply

Blocking users out of the system for a specified time range?

  • January 24, 2012 at 10:57 am

    #254226

    Greetings all 🙂

    I have currently had an issue with users utilizing Data Cubes and Reporting Services during our ETL load times between 2am and 5am. The night before last this caused some deadlocking issues in my data load for obvious reasons 🙂

    I am sure there is a simple way to do this and I can google it fairly easily but I want to know what the best practice is here, and what the best method is as I don't want to accidentally lock users out more than I need to.

    Link to my blog http://notyelf.com/

  • January 24, 2012 at 11:11 am

    #1437834

    I think a logon trigger would do the trick:

    CREATE TRIGGER TR_UserShutdown ON ALL SERVER

    FOR LOGON

    AS

    BEGIN

    IF DATEPART(hour,GETDATE()) BETWEEN 4 AND 5

    AND SUSER_NAME() NOT IN ('sa', 'SomeOtherAuthorizedUser')

    BEGIN

    RAISERROR('No user logon between 4 AM and 6 AM!!!', 16, 1);

    ROLLBACK

    END

    END

    -- Gianluca Sartori

  • January 24, 2012 at 12:13 pm

    #1437888

    Will that block Cube users?

    Also if they are already logged on, that wouldn't kick them off would it?

    Link to my blog http://notyelf.com/

  • January 25, 2012 at 2:28 am

    #1438138

    No, it would block SQLServer users and wouldn't drop existing connections.

    -- Gianluca Sartori

  • January 25, 2012 at 2:48 am

    #1438147

    Gianluca Sartori (1/25/2012)

    No, it would block SQLServer users and wouldn't drop existing connections.

    It would block however the SQL Server user that is used by the cube to browse the data (if you are using the ROLAP storage model), unless that user was already logged in.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • January 26, 2012 at 12:16 pm

    #1438993

    Yeah I need to cut users off and block them for that time frame.

    Wouldn't restarting the service do it, then I could activate that Trigger?

    Link to my blog http://notyelf.com/

  • January 26, 2012 at 12:31 pm

    #1438998

    can't you just have your process change each database to RESTRICTED_USER WITH ROLLBACK IMMEDIATE

    OR

    SINGLE_USER WITH ROLLBACK IMMEDIATE

    to kick them out, do your work or maintenance, and then put each database back to multi user?

    is it more than one database, really?

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • February 1, 2012 at 3:37 pm

    #1441469

    Lowell that sounds like a good solution.

    It is only one database. Well the main datawarehouse is one database but I imagine if I put the report server database in single user mode that would work as well for SSRS correct?

    The only 'real' way users can access the data is through SSAS Cubes or SSRS. SSAS connects to the datawarehouse and SSRS connects to the normal reporting services database.

    Link to my blog http://notyelf.com/

  • February 3, 2012 at 4:05 pm

    #1442685

    Single User mode does not work as my ETL process is parallel and fails because it connects to the database multiple times to run multiple scripts. Also, single user mode does not prevent the usage of the OLAP Cubes.

    I am not sure what restricted user does because I did this and absolutely nothing changed...

    I can't imagine this is an unprecedented issue. Anyone else have ideas?

    Link to my blog http://notyelf.com/

  • February 3, 2012 at 5:21 pm

    #1442700

    We did something similar to Gianluca's trigger code. The solution works well for us but allow me to make a couple of suggestions:

    1) We define the set of Users who can pass the trigger's logic in a table. It just makes modifications easier to make; we change the Users defined in the table rather than change the code.

    2) If you absolutely need to cut off extraneous processing at the magic time, you could get a list of current activity using sp_who2 and, based on whether the User was found in the table of legitimate users, extract the spid and use KILL to terminate that processing.

  • February 3, 2012 at 5:35 pm

    #1442704

    The Trigger code is great for limiting access to sql server and reporting services, but it does absolutely nothing for the data cubes. I can still log in and use them...

    I think as a precaution I will just restart the analysis service before the etl starts then limit its memory to like 1GB so people can still 'use' it but it will be much slower!

    Link to my blog http://notyelf.com/

Viewing 11 posts - 1 through 10 (of 10 total)

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