October 30, 2013 at 8:40 pm
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.
October 31, 2013 at 3:40 am
Why don't you simply revoke permissions?
-- Gianluca Sartori
October 31, 2013 at 5:51 am
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.
October 31, 2013 at 5:54 am
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
October 31, 2013 at 7:19 am
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.
October 31, 2013 at 8:35 am
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
October 31, 2013 at 8:55 am
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
'You must disable Trigger "PermissionChange" to drop, logins!'
ROLLBACK
;
and
create trigger rob
on all server
for drop_login, alter login
as
Print ('')
rollback;
October 31, 2013 at 10:00 am
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
October 31, 2013 at 11:03 am
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.
October 31, 2013 at 11:08 am
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.
October 31, 2013 at 11:14 am
There's no DDL event for that as far as I know. Sorry.
Moreover, I still think it's not a good idea.
-- Gianluca Sartori
October 31, 2013 at 11:35 am
Is there a policy!
November 1, 2013 at 2:30 pm
Instead of DDL_LOGIN_EVENTS, you can use DDL_SERVER_SECURITY_EVENTS, which covers a much wider range of statements. See BOL, "DDL Event Groups" for more details.
You'll need to use EVENTDATA() from within the trigger to get info on the triggering command. You can do a test with "SELECT EVENTDATA()" in the trigger to see what the XML data looks like.
Within the XML, get the affected login from the "<ObjectName>" tag/value. Sorry, I'm not an XML jock, and don't have time now to provide the specific code on how to do that, but I'm sure someone else here can help you with that part.
Then, if it's not the specific login name you want to prevent mods to, just issue a RETURN statement to exit the trigger and allow normal processing to continue, since naturally you want a DDL trigger to run as efficiently as possible.
If it is the specific name, then you can do whatever messages, rollback, etc., you want to do. Btw, you should encrypt the trigger to make it at least a little harder for other sysadmins to modify. They can of course delete the trigger, but you could capture that with extended events ... unless they modify/remove those also. Ultimately there's no way to block an sa, but you can make it enough of a pain that most sa's won't bother/know how to get around the trigger(s).
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply