May 9, 2013 at 7:11 am
Hi All,
I have a scenario where i am trying to deny permission to specific login while connecting to server.I came across the following query and was able to block the access when the user is trying to connect through Sql server.Please find the query below
CREATE TRIGGER Login_Deny
ON ALL SERVER
FOR LOGON
AS
BEGIN
IF ( (ORIGINAL_LOGIN()= 'abc_def')
AND APP_NAME() IN ('Microsoft SQL Server Management Studio - Query')
)
ROLLBACK
END
The problem i am currently facing is the same login id is used in ETL process which runs in informatica.I want to deny the permission only if the connection is made through SQL server and the same login should work when they are trying to connect from informatica.Is this possible?
May 9, 2013 at 7:17 am
modified from a similar post on the same subject, i think this would do exactly what you are asking:
--Prevent access from SSMS
--drop TRIGGER logon_trigger_not_from_SSMS on all server
CREATE TRIGGER logon_trigger_not_from_SSMS
ON ALL SERVER FOR LOGON
AS
BEGIN
IF APP_NAME() LIKE '%Microsoft SQL Server%'
BEGIN
IF suser_name() IN ('abc_def','BillyHaxor') --the only persons not allowed to use SSMS.
BEGIN
--only allowed from my host machine, because I might need to login and test it myself
IF host_name() !='STORMDEV'
BEGIN
RAISERROR('SSMS connections for user abc_def Is Restricted.', 16, 1)
ROLLBACK
END --host name check
END --suser_name check
END --app name check
END --trigger
ENABLE TRIGGER logon_trigger_not_from_SSMS on all server
Lowell
May 9, 2013 at 11:43 pm
Thanks for Sharing the script.
I would also like to if there are any guidelines or best practices to be followed while setting up a security model for a data warehouse.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply