SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


A trigger prevent user from update/delete a user login


A trigger prevent user from update/delete a user login

Author
Message
robert rogers-466427
robert rogers-466427
SSC Rookie
SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)

Group: General Forum Members
Points: 45 Visits: 674
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.
Gianluca Sartori
Gianluca Sartori
SSC-Insane
SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)

Group: General Forum Members
Points: 23811 Visits: 13362
Why don't you simply revoke permissions?

--Gianluca Sartori

How to post T-SQL questions
spaghettidba.com
@spaghettidba
robert rogers-466427
robert rogers-466427
SSC Rookie
SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)

Group: General Forum Members
Points: 45 Visits: 674
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.
Gianluca Sartori
Gianluca Sartori
SSC-Insane
SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)

Group: General Forum Members
Points: 23811 Visits: 13362
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

How to post T-SQL questions
spaghettidba.com
@spaghettidba
robert rogers-466427
robert rogers-466427
SSC Rookie
SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)

Group: General Forum Members
Points: 45 Visits: 674
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.
Gianluca Sartori
Gianluca Sartori
SSC-Insane
SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)

Group: General Forum Members
Points: 23811 Visits: 13362
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

How to post T-SQL questions
spaghettidba.com
@spaghettidba
robert rogers-466427
robert rogers-466427
SSC Rookie
SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)

Group: General Forum Members
Points: 45 Visits: 674
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;
Gianluca Sartori
Gianluca Sartori
SSC-Insane
SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)

Group: General Forum Members
Points: 23811 Visits: 13362
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

How to post T-SQL questions
spaghettidba.com
@spaghettidba
Ed Wagner
Ed Wagner
SSC-Forever
SSC-Forever (48K reputation)SSC-Forever (48K reputation)SSC-Forever (48K reputation)SSC-Forever (48K reputation)SSC-Forever (48K reputation)SSC-Forever (48K reputation)SSC-Forever (48K reputation)SSC-Forever (48K reputation)

Group: General Forum Members
Points: 48095 Visits: 10844
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
robert rogers-466427
robert rogers-466427
SSC Rookie
SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)

Group: General Forum Members
Points: 45 Visits: 674
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search