Display Computer Name 2

[carolx]

If I log on to SQL server from the Server via a shortcut from a local computer on the network and run the script below which computer name would be displayed - the Server or the local computer:

DECLARE @uid INT,@loginname VARCHAR(70)
select DISTINCT @uid = uid,@loginname = loginame from master.dbo.sysprocesses WHERE dbid = db_id('mortgage')

select DISTINCT nt_username AS User_Name,uid AS User_Id, hostname AS Computer,loginame AS Login_Name,@loginname AS requester from master.dbo.sysprocesses WHERE dbid = db_id('mortgage') AND loginame = @loginname

 

[carolx]

I want users to log in to SQL Server from their local computer and not from the Server. How do you set up that?

 

[carolx]

Is this a solution:

-----------------------------------------------------------------------
-- MortgageUser Stored Procedure

DECLARE @loginname VARCHAR(70)
select DISTINCT @loginname = loginame from master.dbo.sysprocesses WHERE dbid = db_id('mortgage')

select DISTINCT nt_username AS User_Name,uid AS User_Id, hostname AS Computer,loginame AS Login_Name,@loginname AS requester from master.dbo.sysprocesses WHERE dbid = db_id('mortgage') AND loginame = @loginname

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

DECLARE @usertab TABLE(username VARCHAR(100),userid INT,computer VARCHAR(100),loginx VARCHAR(100),requester VARCHAR(100))

DECLARE @Server VARCHAR(100)
DECLARE @comp VARCHAR(100)
DECLARE @computer VARCHAR(100)

INSERT @usertab
EXEC MortgageUser

SELECT @computer = computer FROM @usertab
SELECT @Server = CAST(SERVERPROPERTY('Servername') AS VARCHAR(100))

--SELECT @Server = @@SERVERNAME

SELECT @comp = SUBSTRING(@Server,1,CHARINDEX('\',@Server)-1)

IF @comp = @computer
BEGIN
RAISERROR(50001,16,1,'You are not allowed to log in from the Server.','Server Login')
RETURN -- mortgage program is being executed from the Server
END

 

[yelworcm]

To see where the connection is coming from (or at least what the program is reporting as it's host name), you can just use the host_name() function:

select host_name()

So, you can use this (replace the print statements with probably your RAISERROR command):

if host_name() + '%' like @@servername
  begin
	print 'oops'
  end
else
  begin
	print 'hello'
  end

 

[philhege]

Try
if charindex(host_name(),@@servername)> 0

-----------
With business clients like mine, you'd be better off herding cats.

 

[carolx]

Thanks a lot.

Both solutions are similar and more straightforward than what I came up with.

carolx