marrowyung
asked on
the checking of adding/ update/ deletion of SQL account
Dear all,
Right now have an ISO request that how can we detect the add/update/delete of user account and it suspose to return by any means like email.
question is :
1) any tools provide by SQL server can do this ? what is the minimum version of SQL server support that ?
2) we don't want this kind of alert report about the status of a user account to apply to store procedure. is it possible for only deal with SQL user account no matter it is SQL user or Windows user.
3) any tools to detect any adding of duplicated account ?
4) and trace to show who create the account and when he/she do it ?
5) What command he/she runs to add/update/delete the user account.
anyway you guys to DETECT and REPORT it automatically to the system administrator/DBA are welcome. tks.
Right now have an ISO request that how can we detect the add/update/delete of user account and it suspose to return by any means like email.
question is :
1) any tools provide by SQL server can do this ? what is the minimum version of SQL server support that ?
2) we don't want this kind of alert report about the status of a user account to apply to store procedure. is it possible for only deal with SQL user account no matter it is SQL user or Windows user.
3) any tools to detect any adding of duplicated account ?
4) and trace to show who create the account and when he/she do it ?
5) What command he/she runs to add/update/delete the user account.
anyway you guys to DETECT and REPORT it automatically to the system administrator/DBA are welcome. tks.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
ISO compliance.
2) means is this applicable to SQL user or Windows domain users?
"3) No. Either you can create a second account, then it's technically not a duplicate. So how should this be detected?"
so this mean the SQL will return message say the same account name already exists?
we might make sure of the following script to insert/ delete user account:
as the insert/delete of user account probably won't be process by DBA for this case.
that kind of DDL trigger for this purpose only availabe from SQL server 2008 or later? how about that one which need this turn on is SQL server 2000 and SQL server 2005 ?
2) means is this applicable to SQL user or Windows domain users?
"3) No. Either you can create a second account, then it's technically not a duplicate. So how should this be detected?"
so this mean the SQL will return message say the same account name already exists?
we might make sure of the following script to insert/ delete user account:
/* insert account */
insert into Appuser (username,password,staffid,dept,surname,givenname,phoneno,email,disable) VALUES ('XYZ','xxxxx','999999','XXXXX','XXXXX','99999999','XXXXXXXXXX','N');
insert into UserProfile (uroleid, uuserid) SELECT 99 AS uroleid, uuserid FROM Appuser WHERE (username = 'XYZ')
/* Delete account */.
-------------------------
DELETE FROM UserProfile WHERE (uuserid IN (SELECT uuserid FROM Appuser WHERE (username = 'XYZ')));
DELETE FROM useraudit WHERE (uuserid IN (SELECT uuserid FROM Appuser WHERE (username = 'XYZ')));
DELETE FROM Appuser wherer username = 'XYZ'as the insert/delete of user account probably won't be process by DBA for this case.
that kind of DDL trigger for this purpose only availabe from SQL server 2008 or later? how about that one which need this turn on is SQL server 2000 and SQL server 2005 ?
2) You can see the removal of Windows user only when they are assigned as users. But the normal approach with Windows authentication is that you assign one Windows group in SQL Server the permissions. Then you don't see when users are removed from this group in the AD.
For your example: do we talk about the SQL Server built-in users or an on-top application with it's own user management?
For your example: do we talk about the SQL Server built-in users or an on-top application with it's own user management?
ASKER
"For your example: do we talk about the SQL Server built-in users or an on-top application with it's own user management? "
you know we can add windows user but not a group of users, what if we need all
1) Windows user
2) Windows group
3) SQL user.
what can be do on this ?
you know we can add windows user but not a group of users, what if we need all
1) Windows user
2) Windows group
3) SQL user.
what can be do on this ?
ASKER
any update?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I believe that it will works for SQL 7/2000 and 2005 ? this can installed on the respective standard edition instead of Enterprise edition ?
DDL triggers exists since SQL Server 2005 so won't work on SQL Server 7 and 2000.
Yes, can run in Standard Edition.
Yes, can run in Standard Edition.
ASKER
yeah, that's the only thing workable if we don't have enterprise edition.
here:
what is the diff between UserName and LoginName? what it log down ? EventType is simple and EventTime is simple to understand too !
Which one record the failure and success of the user account create/update/delete.
How can this send out an email other than the failure and success of create/update/delete plus information like:
1) if duplicated id was added to the script or typo or missing entries?
2) if user run script to create user, can this trigger show out the whole script that ran as well as the EventTime timestamp?
Any kind of error checking is welcome.
here:
EventType = @xmlEventData.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(1000)'),
UserName = @xmlEventData.value('(/EVENT_INSTANCE/ObjectName)[1]','nvarchar(1000)'),
LoginName = @xmlEventData.value('(/EVENT_INSTANCE/LoginName)[1]', 'nvarchar(1000)'),
EventTime = GETDATE()what is the diff between UserName and LoginName? what it log down ? EventType is simple and EventTime is simple to understand too !
Which one record the failure and success of the user account create/update/delete.
How can this send out an email other than the failure and success of create/update/delete plus information like:
1) if duplicated id was added to the script or typo or missing entries?
2) if user run script to create user, can this trigger show out the whole script that ran as well as the EventTime timestamp?
Any kind of error checking is welcome.
UserName is the name that the correspondent LoginName has in the database. In most of the cases people are using the same name because it's the default behavior but it can be changed if you want.
This trigger only records what is being done. For error issues you need to use a TRY..CATCH block.
You can record the T-SQL command that was invoked:
For security reasons I don't recommend you to send emails through SQL Server but if you want to go with it then you can use the sp_send_dbmail stored procedure. More information on how to use it in MSDN.
This trigger only records what is being done. For error issues you need to use a TRY..CATCH block.
You can record the T-SQL command that was invoked:
@xmlEventData.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(4000)'For security reasons I don't recommend you to send emails through SQL Server but if you want to go with it then you can use the sp_send_dbmail stored procedure. More information on how to use it in MSDN.
ASKER
so I need to do this:
do I have to declare some variables ? the Eventxxx already variable ?
EventType = @xmlEventData.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(1000)'),
UserName = @xmlEventData.value('(/EVENT_INSTANCE/ObjectName)[1]','nvarchar(1000)'),
LoginName = @xmlEventData.value('(/EVENT_INSTANCE/LoginName)[1]', 'nvarchar(1000)'),
EventDetail = @xmlEventData.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(4000)'
EventTime = GETDATE()do I have to declare some variables ? the Eventxxx already variable ?
Yes you need. From my example:
DECLARE @xmlEventData XML
SET @xmlEventData = EVENTDATA() --> Get event informationASKER
so EventType can be understand without declaration ?
so just :
then I can print those out:
EventType ... EventTime , right ?
so just :
DECLARE @xmlEventData XML
SET @xmlEventData = EVENTDATA() --> Get event information
EventType = @xmlEventData.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(1000)'),
UserName = @xmlEventData.value('(/EVENT_INSTANCE/ObjectName)[1]','nvarchar(1000)'),
LoginName = @xmlEventData.value('(/EVENT_INSTANCE/LoginName)[1]', 'nvarchar(1000)'),
EventDetail = @xmlEventData.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(4000)'
EventTime = GETDATE()then I can print those out:
EventType ... EventTime , right ?
There was a SELECT on there. They aren't variables but columns aliases:
SELECT
EventType = @xmlEventData.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(1000)'),
UserName = @xmlEventData.value('(/EVENT_INSTANCE/ObjectName)[1]','nvarchar(1000)'),
LoginName = @xmlEventData.value('(/EVENT_INSTANCE/LoginName)[1]', 'nvarchar(1000)'),
EventDetail = @xmlEventData.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(4000)'
EventTime = GETDATE()ASKER
or one thing then, so where/table it select from ?
after that I can use that column to put into the email body for SQL server to send it ?
I don't know if it is just a column alias can I maniplate it in the email body .
after that I can use that column to put into the email body for SQL server to send it ?
I don't know if it is just a column alias can I maniplate it in the email body .
Not all SELECT needs a table. This one example and there are others like:
SELECT GETDATE()
SELECT @@ServerName
and many others.
Also this is only an example that returns to screen. If you really needs to work with variables you just need to make small changes in the code I've provided you:
SELECT GETDATE()
SELECT @@ServerName
and many others.
Also this is only an example that returns to screen. If you really needs to work with variables you just need to make small changes in the code I've provided you:
CREATE TRIGGER trgUser
ON DATABASE
FOR CREATE_USER, DROP_USER
AS
BEGIN
SET NOCOUNT ON
DECLARE @xmlEventData XML
DECLARE @EventType NVARCHAR(1000)
DECLARE @UserName NVARCHAR(1000)
DECLARE @LoginName NVARCHAR(1000)
SET @xmlEventData = EVENTDATA() --> Get event information
SET @EventType = @xmlEventData.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(1000)'),
SET @UserName = @xmlEventData.value('(/EVENT_INSTANCE/ObjectName)[1]','nvarchar(1000)'),
SET @LoginName = @xmlEventData.value('(/EVENT_INSTANCE/LoginName)[1]', 'nvarchar(1000)'),
/*
#########################
PUT IN THIS SECTION YOUR CODE
#########################
*/
ENDASKER
"Not all SELECT needs a table. This one example and there are others like:
SELECT GETDATE()
SELECT @@ServerName
and many others.
"
I agree
so back to the question from the very beginning:
you know we can add windows user but not a group of users, what if we need all
1) Windows user
2) Windows group
3) SQL user.
that script will print that out for us?
"1) if duplicated id was added to the script or typo or missing entries?
2) if user run script to create user, can this trigger show out the whole script that ran as well as the EventTime timestamp?
3) What command he/she runs to add/update/delete the user account.
"
when if 1) happen, an error will comes out ?
2) shoudl be ok by your script ?
3) should be list down by your script ?
SELECT GETDATE()
SELECT @@ServerName
and many others.
"
I agree
so back to the question from the very beginning:
you know we can add windows user but not a group of users, what if we need all
1) Windows user
2) Windows group
3) SQL user.
that script will print that out for us?
"1) if duplicated id was added to the script or typo or missing entries?
2) if user run script to create user, can this trigger show out the whole script that ran as well as the EventTime timestamp?
3) What command he/she runs to add/update/delete the user account.
"
when if 1) happen, an error will comes out ?
2) shoudl be ok by your script ?
3) should be list down by your script ?
@LoginName gives you the user login (Windows user or SQL user, depends on what are you using).
@UserName gives you the user name in the database (may be the same as LoginName because is the default)
Windows group is just a container of Windows user. You can't connect with a Windows group credential because they don't have a password. It's just an easy way to add a group of users without adding them one by one.
@UserName gives you the user name in the database (may be the same as LoginName because is the default)
Windows group is just a container of Windows user. You can't connect with a Windows group credential because they don't have a password. It's just an easy way to add a group of users without adding them one by one.
1) if duplicated id was added to the script or typo or missing entries?This will happen before the trigger be fire and SQL Server engine will raise an error.
2) if user run script to create user, can this trigger show out the whole script that ran as well as the EventTime timestamp?Yes. You need to investigate more about EVENTDATA(). I just gave you some examples of what you can capture. You can add the following statement and test it:
DECLARE @CommandText NVARCHAR(4000)
SET @CommandText = @xmlEventData.value('(/EVENT_INSTANCE/TSQLCommand/)[1]', 'nvarchar(4000)')3) What command he/she runs to add/update/delete the user account.You'll get the command on the previous step where you capture the command.
ASKER
"Windows group is just a container of Windows user. You can't connect with a Windows group credential because they don't have a password. It's just an easy way to add a group of users without adding them one by one."
yes, it make easlier but we can use domain password(Windows authen) to access SQL server without retype the password again, but we need to log this type of user account creation too.
"Yes. You need to investigate more about EVENTDATA(). I just gave you some examples of what you can capture. You can add the following statement and test it:"
so you mean this:
yes, it make easlier but we can use domain password(Windows authen) to access SQL server without retype the password again, but we need to log this type of user account creation too.
"Yes. You need to investigate more about EVENTDATA(). I just gave you some examples of what you can capture. You can add the following statement and test it:"
so you mean this:
CREATE TRIGGER trgUser
ON DATABASE
FOR CREATE_USER, DROP_USER
AS
BEGIN
SET NOCOUNT ON
DECLARE @xmlEventData XML
DECLARE @CommandText NVARCHAR(4000)
DECLARE @EventType NVARCHAR(1000)
DECLARE @UserName NVARCHAR(1000)
DECLARE @LoginName NVARCHAR(1000)
SET @xmlEventData = EVENTDATA() --> Get event information
SET @EventType = @xmlEventData.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(1000)'),
SET @UserName = @xmlEventData.value('(/EVENT_INSTANCE/ObjectName)[1]','nvarchar(1000)'),
SET @LoginName = @xmlEventData.value('(/EVENT_INSTANCE/LoginName)[1]', 'nvarchar(1000)'),
SET @CommandText = @xmlEventData.value('(/EVENT_INSTANCE/TSQLCommand/)[1]', 'nvarchar(4000)')
/*
#########################
PUT IN THIS SECTION YOUR CODE
#########################
*/
ENDyes, it make easlier but we can use domain password(Windows authen) to access SQL server without retype the password again, but we need to log this type of user account creation too.Sure, will log it. It captures de CREATE USER statement.
Yes, exactly what I meant. Now you already have the base for your code.
ASKER
"Sure, will log it. It captures de CREATE USER statement."
you mean no matter it is windows or SQL user created, this will log it down, right?
you mean no matter it is windows or SQL user created, this will log it down, right?
ASKER
tks both.
ASKER
I need as much information return in email/report as possible.