Re: Disable or prevent Trusted Connection (-E) logins [DDL triggers]
- From: "Russell Fields" <russellfields@xxxxxxxxxx>
- Date: Wed, 22 Jul 2009 14:06:03 -0400
You could create a login trigger like this one:
CREATE TRIGGER LimitLogins
ON ALL SERVER
FOR LOGON
AS
BEGIN;
DECLARE @TrustedConnection INT
IF EXISTS (SELECT *
FROM sys.server_principals
WHERE name = SUSER_SNAME() AND type <> 's')
SET @TrustedConnection = 1
ELSE
SET @TrustedConnection = 0
IF IS_SRVROLEMEMBER ('sysadmin') = 0 AND
IS_MEMBER ('webadmins') = 0 AND
@TrustedConnection = 0
ROLLBACK;
END;
This would require you to create a role in master for webadmins and populate it with the trusted connection logins that should be allowed in. Something like this:
USE master
GO
CREATE ROLE webadmins
GO
CREATE LOGIN N'Domain\Login' FROM WINDOWS ...
GO
CREATE USER N'Domain\Login' FOR LOGIN ...
GO
EXEC sp_addrolemember N'webadmins', N'Domain\Login'
GO
Be sure and test a login trigger very carefully, since a mistake can lock you out of the server. (For example, do not close the connection used to create the trigger, so that you can drop it again if it does not work as you (or I) expected.)
FWIW,
RLF
"Uday" <Uday@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message news:EBA8ED31-F185-44C6-91FA-D68031C9C4A5@xxxxxxxxxxxxxxxx
Guys,
Is it possible to disable or prevent Trusted Connection logins to Sql Server
Express 2005 installed on a laptop for non-admin accounts using DDL triggers?
Also, could I restrict any login attempts, either Windows Authentication or
Sql Server authentication, to only admin and webserver accounts.
Any sample code or link to an article would help a lot.
thanks,
_t
.
- Follow-Ups:
- References:
- Prev by Date: Re: General database safety question
- Next by Date: FillFactor
- Previous by thread: Disable or prevent Trusted Connection (-E) logins [DDL triggers]
- Next by thread: Re: Disable or prevent Trusted Connection (-E) logins [DDL trigger
- Index(es):
Relevant Pages
|
