SQL Server Performance

login script

Discussion in 'SQL Server 2005 General Developer Questions' started by acki4711, Mar 22, 2007.

  1. acki4711 Member

    Hi all,
    Is it possible to write a stored procedure (or assembly) in sql server to handle some own login logic?

    if username = 'someuser' and used application = 'query analyzer' then
    deny login
    allow login
    end if

    Thx for pushing me in the right direction.
  2. Adriaan New Member

    In SQL 7.0/2000, you can query sysprocesses with the @@SPID function to get the current SPID under which the procedure is executing ...

    select program_name
    from master.dbo.sysprocesses where spid = @@spid

    ... but I have no idea what the equivalent is in SQL 2005.

    For the current user, use SUSER_SNAME() and similar.
  3. MohammedU New Member

    I don't think it possible to control the access bases on the application...

    Adrian query and functions works in sql 2005 also...

  4. Adriaan New Member

    Ah sorry - no, logins must be handled as logins - you just grant or deny access to the server, and/or specific databases, in specific server roles and/or database rolews, in the Security section.

    Stored procedures are defined within a database, so you must already have some degree of access to the database to run a stored procedure. Better to keep the Security tight.

Share This Page