SQL Server Performance

SQL Server User id Restrictions

Discussion in 'ALL SQL SERVER QUESTIONS' started by Sayeekrishnan, Jun 16, 2012.

  1. Sayeekrishnan New Member

    I have a database with sensitive data. Users must query through an application and no one but me and my boss should be able to access directly to the database and query.
    None of the users have any user id or password. They use their network id to open the application and the application has its own SQL Server user id and connects to the database ( Coded in the Webconfig file)
    Now the question is that, How can I restrict user id which is coded in webconfig file to refrain from remote logon other than thru application.
    ( to be more clear, the user id in webconfig file, should not be used to remotely logon to sql server)

    Thanks
    Sayee
  2. Shehap MVP, MCTS, MCITP SQL Server

    You can't revoke access from a SQL Server login used for web config, you can workaround it easily by configuring the below job of a frequency basis of 10 sec (The minimum )to terminate any connection coming from that SQL login to SQL Server through SQL Server Management studio or SQL Server Management studio query or any other additional tools using the below script :

    USE[master]

    declare@Sessionsid int

    declare@sqlnvarchar (300)

    DeclareTablecursorcursorforselectt.session_idfromsys.dm_exec_connectionstinnerjoinsys.dm_exec_sessionssont.session_id=s.session_idwhere

    (s.program_name='Microsoft SQL Server Management Studio - Query'ors.program_name='Microsoft SQL Server Management Studio')ands.login_name='Login_Name'

    openTablecursor

    fetchnextfromTablecursorinto@Sessionsid

    while (@@FETCH_STATUS=0)

    BEGIN

    set@sql=N'Kill '+convert(nvarchar (10),@Sessionsid)+' '

    execsp_executesql@sql

    print ('The sessionid = '+CONVERT(NVARCHAR (50),@Sessionsid)+' has been killed')

    fetchnextfromTablecursorinto@Sessionsid

    end

    closeTablecursor

    DEALLOCATETablecursor

    Hence, I do believe none can use this login easily through SQL Server Management studio to conduct any of DML or DDL change …

    Please work out it and let m e know your feedback

Share This Page