SQL Server Performance Forum – Threads Archive

login script

Hi all,
Is it possible to write a stored procedure (or assembly) in sql server to handle some own login logic? Ex:
if username = ‘someuser’ and used application = ‘query analyzer’ then
deny login
else
allow login
end if Thx for pushing me in the right direction.
acki

---

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.

---

I don’t think it possible to control the access bases on the application… Adrian query and functions works in sql 2005 also…
MohammedU.
Moderator
SQL-Server-Performance.com

---

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.

---

]]>