September 10, 2015 at 8:26 am
Hello community!
Edit: Sorry I meant how to prohibit select on a certain table.
I have seen, that on MySql you can define to which database a user can connect from a certain host.
Example; User A can connect from machine A, but not from machine B
Now I have the problem, that I need a similar solution for the SQL Server. I only want to allow that the SQL Server user can select data from a certain table if the user is logged on from the local machine.
I would write a trigger on "select before" for this table.
I get the current connections via sp_who/sp_who2 and I can determine the current machine name via "@@servername".
Is there a possibility to find out in the trigger which "spid" tries to execute the sql statement? I could then check the hostname with the @@servername value and if they are not equal return an empty result set or throw an error.
If the user connection is not from the local instance, I would like to return an empty result without any error.
Thank you for your assistance.
September 10, 2015 at 8:50 am
I think I found a solution for my problem
SET NOCOUNT ON
DECLARE @userHost varchar(50)
DECLARE @hostName varchar(50)
DECLARE @result bit
CREATE TABLE #sp_who2
(
SPID INT,
Status VARCHAR(1000) NULL,
Login SYSNAME NULL,
HostName SYSNAME NULL,
BlkBy SYSNAME NULL,
DBName SYSNAME NULL,
Command VARCHAR(1000) NULL,
CPUTime INT NULL,
DiskIO INT NULL,
LastBatch VARCHAR(1000) NULL,
ProgramName VARCHAR(1000) NULL,
SPID2 INT,
REQUESTEID INT
)
INSERT INTO #sp_who2
EXEC sp_who2
select @userHost = HostName from #sp_who2 where spid = @@SPID
SET @hostName = SUBSTRING(@@servername, 0, charindex('\', @@servername))
SET @hostName = LTRIM(RTRIM(@hostname))
IF (LTRIM(RTRIM(@userHost)) = @hostName )
BEGIN
SET @result = 1
END
ELSE
BEGIN
SET @result = 0
END
SELECT @result
Or does anyone know of a better way?
Thank you
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply