June 19, 2007 at 5:20 am
I have a need to lock down sp_AddRoleMember in my databases. But apparently the DDL triggers won't work for that. I've experimented with the following groups: DDL_User_Events, DDL_Role_Events and DDL_GDR_Database_Events. After I create the triggers, I can still execute sp_AddRoleMember to add someone to the database.
Has anyone found a way or an event that locks down this particular stored procedure? Or is it that I'm a SysAdmin? I was under the impression that DDL Triggers work to prevent X event no matter what your permissions are unless you disable the trigger in question.
Thoughts? Thanks in advance for any advice you can give me on this matter.
June 19, 2007 at 8:19 am
Brandie,
What's your trigger code? Are you rolling things back?
They should work no matter what. A sysadmin doesn't override the trigger. Can you add log code to the trigger to see if it fires?
I haven't tried, but event notification could help here.
June 19, 2007 at 8:26 am
Steve,
Since I posted my problem, I've actually tested the sp_adduser event against my triggers and the triggers do indeed work against that even though I'm a sysAdmin. They just don't take into consideration the sp_addrolemember proc. Here's my code:
--TRIGGER1
Create --Alter
Trigger AuditUsers_DBRoles On Database
FOR DDL_User_Events
AS
Declare @EventRec XML
Set @EventRec = EventData()
Insert Credit_DBA.dbo.ddl_Log (PostTime, DB_User, EventType, TSQL)
Values (GetDate(), Convert(varchar(100), Current_User),
@EventRec.value('(/EVENT_INSTANCE/EventType)[1]', 'varchar(100)'),
@EventRec.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'varchar(max)') )
Print 'You cannot add users and roles to this database without disabling AuditDBRoles'
Rollback;
--TRIGGER2
Create --Alter
Trigger Audit_GDREvents On Database
FOR DDL_GDR_DATABASE_Events
AS
Declare @EventRec XML
Set @EventRec = EventData()
Insert Credit_DBA.dbo.ddl_Log (PostTime, DB_User, EventType, TSQL)
Values (GetDate(), Convert(varchar(100), Current_User),
@EventRec.value('(/EVENT_INSTANCE/EventType)[1]', 'varchar(100)'),
@EventRec.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'varchar(max)') )
Print 'You cannot add users and roles to this database without disabling AuditDBRoles'
Rollback;
--TRIGGER3
Create --Alter
Trigger Audit_RoleEvents On Database
FOR DDL_ROLE_Events
AS
Declare @EventRec XML
Set @EventRec = EventData()
Insert Credit_DBA.dbo.ddl_Log (PostTime, DB_User, EventType, TSQL)
Values (GetDate(), Convert(varchar(100), Current_User),
@EventRec.value('(/EVENT_INSTANCE/EventType)[1]', 'varchar(100)'),
@EventRec.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'varchar(max)') )
Print 'You cannot add users and roles to this database without disabling AuditDBRoles'
Rollback;
June 19, 2007 at 8:28 am
Oh, I forgot to mention that I did indeed add log code (as you can see above), but the code didn't log anything even when the trigger did work correctly.
Maybe that's because I was going to another database for the inserts. Haven't had a chance to go over the logging yet. I got the code from BOL.
The print statement worked when I tried to run sp_AddUser or add a user through the SSMS interface.
June 19, 2007 at 10:21 am
Brandie,
I've recently been researching this myself and I've decided that BOL is misleading when it lists create role, alter role, and drop role as events that can be used with DDL triggers. They clearly cannot be monitored with triggers. The alternative is to use Event Notifications, which I haven't tried yet.
Greg
Greg
June 19, 2007 at 10:25 am
Thing is, I don't want to just monitor them. I want to prevent anyone from using them and record the efforts of those who try to add users to roles. We're trying to lock things down in our database as part of our security updates, which means preventing anyone who isn't SysAdmin but needs DB_Owner access in a database from assigning users to the DB_Owner role. Hence the need to find something to lock down sp_addRoleMember.
@sigh. I don't think it's going to work, though. Maybe I should submit to Microsoft as a suggestion.
June 19, 2007 at 12:46 pm
Yeah those triggers definatly shut you down when they work as I found out with my login trigger! Even locks out the SA login!
June 19, 2007 at 12:52 pm
It couldn't hurt to submit it to Microsoft. They're always saying they want feedback.
In the meantime, maybe you could lock users down with more granular permissions than making them members of db_owner. What do they need to do?
Greg
Greg
June 20, 2007 at 6:03 am
The situation is that some of the Developers have access to a SQL Server login with DB_Owner permissions (and password) so they can give our application access to a specific database. And since the application needs to do just about anything in that database, we can't make the login have any lesser permissions.
Unfortunately, since these people *do* have access to this login, they could potentially use it to log into SQL Server and grant database permissions to their own logins or to other people's logins. Now, these people have been with the company a long time and they have never ever actually abused this. But as our department grows and security has become a major issue world-wide, we have been re-addressing our security setup.
We can't just change the password, because the application would still need it and the developers in question would need the new password to make the app work. As indicated above, we can't just change the login permissions as that would cause all kind of code breaks that we don't have time to fix. So the last resort is making sure the login simply can't grant roles or permissions to other users, which means DDL triggers.
Unless someone has another idea.
June 20, 2007 at 6:16 am
I've added feedback to Microsoft's site. If anyone would like to vote on the issue it's located at: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=283525
Issue ID 283525 and titled "DDL Triggers do not restrict sp_AddRoleMember".
Thanks everyone for your thoughts on this matter. I appreciate everything.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply