Block reporting from a database

  • We have a production OLTP database and a copy of that database to be used for reporting, since this is a internal application that all users access, all users have access to both databases. I am having a problem that certain users are mining the data using Crystal Reports and Office Products and causing extensive blocking that is in effect halting the use of the application. Marketing attempts to tell the reporting users to use the reporting database have been ineffective. Is there a way I can block access to the prod database from all applications except SSMS and the expected application?

    Thank you,

  • I would suggest to use Roles in the Database and create a Role for reporting purpose and deny that role access to OLTP DB.

    Otherwise you can create a trigger on LOGON and deny access to programs in the trigger code.

  • I suggest changing the security model of the application so that end users do not have direct access to the database.

  • Michael Valentine Jones (5/17/2013)


    I suggest changing the security model of the application so that end users do not have direct access to the database.

    +100

    or, leave access to database but deny direct access to all tables and views in your database to all non-application logins (creating specific role should help).

    Allow data access only via "published" stored procedures.

    _____________________________________________
    "The only true wisdom is in knowing you know nothing"
    "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help[/url]

  • unfortunately, it is a purchased piece of software, and we are at the mercy of the vendor.

  • MichaelDep the problem with a logon trigger is it hits them with a hammer without regard to which database, so if both the OLTP and the reporting database is on the same server, the logon trigger would not be an option.

    if they are on different servers, then you could filter on application name and prevent anything like EXCEL/Access/Crystal from connecting at all.

    I hate the security-after-they-have-access, but sometimes it's required.

    maybe an ugly job that runs sp_who2, and anything that is connected to the OLTP database, and also has a hostname like the offending programs gets killed , and run the job every minute or so?

    something like this?

    --2005/08

    CREATE PROCEDURE PR_No_Tools_Touch_The_SandBox

    AS

    BEGIN

    SET NOCOUNT ON

    CREATE TABLE #Results (

    [ID] INT IDENTITY(1,1) NOT NULL PRIMARY KEY,

    [SPID] CHAR (5) NULL,

    [INSERTDT] DATETIME NULL DEFAULT(GETDATE()),

    [STATUS] VARCHAR(30) NULL,

    [LOGIN] VARCHAR(30) NULL,

    [HOSTNAME] VARCHAR(30) NULL,

    [BLKBY] VARCHAR(30) NULL,

    [DBNAME] VARCHAR(30) NULL,

    [COMMAND] VARCHAR(30) NULL,

    [CPUTIME] INT NULL,

    [DISKIO] INT NULL,

    [LASTBATCH] VARCHAR(30) NULL,

    [PROGRAMNAME] VARCHAR(200) NULL,

    [SPIDINT] INT NULL,

    [REQUESTID] INT NULL

    )

    --table exists, insert some data

    INSERT INTO #Results(SPID,Status,Login,HostName,BlkBy,DBName,Command,CPUTime,DiskIO,LastBatch,ProgramName,SPIDINT,REQUESTID)

    EXEC sp_who2

    --kill any spids that have dbName = SandBox and the ProgramName like Access/Excel/Crystal

    declare @cmd VARCHAR(50)

    declare @spid int

    declare c1 cursor for

    -------------------------------------------------------------------------------------------

    select *

    FROM #Results

    WHERE (SPID > 50

    AND DBNAME = 'SandBox')

    AND (ProgramName LIKE '%Excel%'

    OR ProgramName LIKE '%Access%'

    OR ProgramName LIKE '%Crystal%'

    OR ProgramName LIKE '%Report%')

    -------------------------------------------------------------------------------------------

    open c1

    fetch next from c1 into @spid

    While @@fetch_status <> -1

    begin

    print 'Killing spid which is connected to a protected database and also connecting with an offending application'

    SET @cmd = 'kill ' + CONVERT(VARCHAR,@spid )

    EXEC (@cmd)

    fetch next from c1 into @spid

    end

    close c1

    deallocate c1

    END

    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!

Viewing 6 posts - 1 through 5 (of 5 total)

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