Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

A trigger prevent user from update/delete a user login Expand / Collapse
Author
Message
Posted Wednesday, October 30, 2013 8:40 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Today @ 12:14 PM
Points: 18, Visits: 562
I need a little help my fella SQL DBA

I need a ddl trigger script to prevent user from update/delete a user login.

I prefer a policy but trigger will work just fine.
Post #1510007
Posted Thursday, October 31, 2013 3:40 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 3:29 PM
Points: 5,046, Visits: 10,571
Why don't you simply revoke permissions?

--
Gianluca Sartori

Get your two-cent-answer quickly
spaghettidba.com
@spaghettidba
Post #1510077
Posted Thursday, October 31, 2013 5:51 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Today @ 12:14 PM
Points: 18, Visits: 562
Yes, that would be simple but there are other SA on the team that would like to add/update/create accounts. I just need a trigger or script to prevent user from updating this one login.
Post #1510127
Posted Thursday, October 31, 2013 5:54 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 3:29 PM
Points: 5,046, Visits: 10,571
Sorry to insist, but if other DBAs (sysadmin) want to alter/drop a login, they have the rights to do it.
If they don't have that right, they shouldn't be sysadmin in the first place.


--
Gianluca Sartori

Get your two-cent-answer quickly
spaghettidba.com
@spaghettidba
Post #1510129
Posted Thursday, October 31, 2013 7:19 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Today @ 12:14 PM
Points: 18, Visits: 562
True... but in my environment for example it's certain accounts that don't require sysadmin rights or if the account is alter by another SQL Dba the application will shut down.
Post #1510162
Posted Thursday, October 31, 2013 8:35 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 3:29 PM
Points: 5,046, Visits: 10,571
I'm not sure I follow your logic.
As you wish, anyway.

DDL triggers can capture the ALTER LOGIN statements. You just need to ROLLBACK in the trigger code to prevent the action from being executed.


--
Gianluca Sartori

Get your two-cent-answer quickly
spaghettidba.com
@spaghettidba
Post #1510213
Posted Thursday, October 31, 2013 8:55 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Today @ 12:14 PM
Points: 18, Visits: 562
I use the two DDL TRIGGER statements below but script doesn't work. They only prevent a user from dropping or renaming a user account. I need to be able to rollback even if a user change the permission on a account.

Create
Trigger PermissionChange
ON
ALL SERVER
For
DDL_LOGIN_EVENTS
AS
PRINT
'You must disable Trigger "PermissionChange" to drop, logins!'
ROLLBACK
;

and

create trigger rob
on all server
for drop_login, alter login
as
Print ('')
rollback;
Post #1510238
Posted Thursday, October 31, 2013 10:00 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 3:29 PM
Points: 5,046, Visits: 10,571
I don't think there's a DDL event for GRANT/REVOKE permissions.
Let me reiterate: you're doing it wrong.
You either have permissions to do something or you don't.


--
Gianluca Sartori

Get your two-cent-answer quickly
spaghettidba.com
@spaghettidba
Post #1510295
Posted Thursday, October 31, 2013 11:03 AM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 12:10 PM
Points: 4,346, Visits: 3,399
Another thing that would be inconvenient to using a trigger would be that is a user is a sysadmin, they could also disable or drop the trigger and then do whatever they wanted anyway. Like was pointed out before:

spaghettidba (10/31/2013)
If they don't have that right, they shouldn't be sysadmin in the first place.



Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions
Post #1510342
Posted Thursday, October 31, 2013 11:08 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Today @ 12:14 PM
Points: 18, Visits: 562
I understand fellas. But your missing the point.
If the admin attempts to edit the logins. He would receive error message that states that this specific account only needs dbcreator permission.

If drops the trigger afterward it's fine but at least he got the error message.

Post #1510345
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse