Solved

the checking of adding/ update/ deletion of  SQL account

Posted on 2015-02-05
23
57 Views
Last Modified: 2015-02-26
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.
0
Comment
Question by:marrowyung
  • 13
  • 8
  • 2
23 Comments
 
LVL 1

Author Comment

by:marrowyung
ID: 40590615
must use DDL trigger? what version of SQL server can support this kind of DDL trigger?

I need as much information  return in email/report as possible.
0
 
LVL 32

Assisted Solution

by:Stefan Hoffmann
Stefan Hoffmann earned 200 total points
ID: 40590629
First of all: What the heck is an ISO request in this context?

1) You may use DDL triggers (DROP_USER event) to detect this.
2) ???
3) No. Either you can create a second account, then it's technically not a duplicate. So how should this be detected?
4) DDL triggers and extended events.
5) See 4).
0
 
LVL 1

Author Comment

by:marrowyung
ID: 40590656
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:

/* 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'

Open in new window


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 ?
0
 
LVL 32

Expert Comment

by:Stefan Hoffmann
ID: 40590922
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?
0
 
LVL 1

Author Comment

by:marrowyung
ID: 40592916
"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 ?
0
 
LVL 1

Author Comment

by:marrowyung
ID: 40602649
any update?
0
 
LVL 45

Accepted Solution

by:
Vitor Montalvão earned 300 total points
ID: 40603013
You can audit user creation or deletion with DDL trigger. Example:
CREATE TRIGGER trgUser
ON DATABASE
FOR CREATE_USER, DROP_USER
AS
BEGIN
  SET NOCOUNT ON

  DECLARE @xmlEventData XML 

  SET @xmlEventData = EVENTDATA()  --> Get event information

  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)'),
    EventTime = GETDATE()
END

Open in new window

0
 
LVL 1

Author Comment

by:marrowyung
ID: 40605176
I believe that it will works for SQL 7/2000 and 2005 ? this can installed on the respective standard edition instead of Enterprise edition ?
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 40605182
DDL triggers exists since SQL Server 2005 so won't work on SQL Server 7 and 2000.
Yes, can run in Standard Edition.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 40605203
yeah, that's the only thing workable if we don't have enterprise edition.

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()

Open in new window


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.
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 40605223
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:
@xmlEventData.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(4000)'

Open in new window


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.
0
Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

 
LVL 1

Author Comment

by:marrowyung
ID: 40611837
so I need to do this:

 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()

Open in new window


do I have to declare some variables ? the Eventxxx already variable ?
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 40611894
Yes you need. From my example:
DECLARE @xmlEventData XML 
  SET @xmlEventData = EVENTDATA()  --> Get event information

Open in new window

0
 
LVL 1

Author Comment

by:marrowyung
ID: 40611899
so EventType  can be understand without declaration ?

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()

Open in new window


then I can print those out:

EventType ... EventTime , right ?
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 40611904
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()

Open in new window

0
 
LVL 1

Author Comment

by:marrowyung
ID: 40615863
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 .
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 40616026
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:
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
#########################
*/

END

Open in new window

0
 
LVL 1

Author Comment

by:marrowyung
ID: 40627405
"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 ?
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 40627612
@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.

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)')

Open in new window


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.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 40630201
"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:

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
#########################
*/

END

Open in new window

?
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 40630327
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.
Sure, will log it. It captures de CREATE USER statement.

Yes, exactly what I meant. Now you already have the base for your code.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 40632601
"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?
0
 
LVL 1

Author Closing Comment

by:marrowyung
ID: 40632611
tks both.
0

Featured Post

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now