Post reply

Change the logon security for different applications

  • November 30, 2010 at 11:15 pm

    #388339

    Hi All,

    Is there a way to set the following on a trigger or something like that

    - if user 'usr_priv' using sqlcmd to connect to the database 'db_trigger_test' needs to have read/write/update to all the tables on the database

    - if the same user 'usr_priv' using other apps like 'SSMS' only have the select to all the tables on the database

    I have created a logon trigger where I can restrict the user using other apps, but could not figure-out a solution for the above request.

    Thank you in advance

    this is what i've got ATM

    CREATE TRIGGER [application_limit_trigger]

    ON ALL SERVER WITH EXECUTE AS 'sa'

    FOR LOGON

    AS

    BEGIN

    IF ORIGINAL_LOGIN()= 'usr_priv' AND (SELECT count(*) FROM sys.dm_exec_sessions WHERE login_name IN ('usr_priv') AND program_name not IN ('SQLCMD')) > 0

    rollback

    END

    GO

  • December 5, 2010 at 5:56 pm

    #1258861

    Is it only me have the same requirement :w00t:

  • December 5, 2010 at 6:06 pm

    #1258865

    What are you trying to accomplish here? The rights a login has do not change based on the application. That's fundamentally different than how SQL Server as a platform is designed.

    This is handled by using separate accounts to log in to SQL Server with different rights.

  • December 5, 2010 at 6:27 pm

    #1258869

    This is the requirement,

    Let’s say I got the Application "A1", an admin user who can insert/update data using the application and the application handles the some part of the business logic.

    I do not need that smart user to use some ODBC connection or other application to insert/update data.

    At the moment I have locked the user connecting from other applications (as the above trigger).

    But I do not mind if he uses his username for reporting prepose (only a query user) from other applications such as SSMA or other third party report tools. (At the moment I have created a different query user to accomplish this)

    My question is, is there a way to handle this using the same user dynamically?

  • December 6, 2010 at 7:25 am

    #1259009

    There isn't a good way. Since the user can change the name in the connection string of the application, there is nothing you can do here to prevent it. What you can do is give the user changes permissions (insert/update/delete) through an application role and then invoke that in your application. That way if they connect with other applications, they can only read.

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

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