BI

How Can I know who has changed anything in SQL?

Hello all,

After such a long time , I would like to post some another blog which might be helpful to the one who works in SQL.

I need to find the one who has changed the tables within my SQL Database that I have created.

For certain reason ,we may need to find /to know who has done any changes within SQL. Such as add view, delete table, modify table, create database, add column, stopped SQL agent job, create new job, modify rules, add roles, new login, and etc.

In order to trace this information and find out my solution, I have researched somewhat and find the solution as.

we can enable and disable the default trace in SQL Server. Meanwhile we can use the default trace enabled option to enable or disable the default trace log files.

If we want to capture the information about who changed the objects in SQL Server. Please try to run the script, which works for me as below:

DECLARE @filename VARCHAR(255) 
SELECT @FileName = SUBSTRING(path, 0, LEN(path)-CHARINDEX('', REVERSE(path))+1) + 'Log.trc'  
FROM sys.traces   
WHERE is_default = 1;  

--Check who dropped and created objects, altered objects
SELECT gt.HostName, 
       gt.ApplicationName, 
       gt.NTUserName, 
       gt.NTDomainName, 
       gt.LoginName, 
       gt.SPID, 
       gt.EventClass, 
       gt.IntegerData, 
       te.Name AS EventName,
       gt.EventSubClass,      
       gt.TEXTData, 
       gt.StartTime, 
       gt.EndTime, 
       gt.ObjectName, 
       gt.DatabaseName, 
       gt.FileName, 
	gt.IsSystem
FROM [fn_trace_gettable](@filename, DEFAULT) gt 
JOIN sys.trace_events te ON gt.EventClass = te.trace_event_id 
where gt.ObjectName ='DimTable' and gt.DatabaseName ='Database'
ORDER BY StartTime;
 --DimTable -replace with the trace table and Database -replace with your DB

I am able to find the one who have changed my tables that I have created.

Also we should have to know that, SQL Server only shows you the IP address of the Terminal Server machine. It has no knowledge about how the user connected to that server. You would have to find the RDP logs and enter them into the database somehow to correlate the information. Which still can be inconclusive if there are more than one user connected through RDP at the same time.

Reference from the link:

http://www.simple-talk.com/sql/performance/the-default-trace-in-sql-server—the-power-of-performance-and-security-auditing/

http://social.msdn.microsoft.com/Forums/en-US/sqlsecurity/thread/ef5f82e3-71a6-4e65-989f-5ab7b84bbc82

Is there any alternative solution for this kind of issue .Hope someone have some idea regarding on this.

Hope this post will be useful for all of us in the field of BI mainly SQL.

Regards,

Anil Maharjan

How to find a Calculated Measure and Calculated Dimension with in particular cube.?

Hello all,

After such a long time , I would like to post some simple tips for calculating the Calculated Measure and Calculated Dimension within a particular cube in SSAS.

Though it might simple but this help me a lot.

I simply need to list down the calculated measures within a particular cube for which I have to open the BIDS project and lookup how many calculated Measure that I had made and their names which seems little odd. So ,after some research and trying out DMV queries I finally got a simple way .

DMV:

Dynamic Management Views is introduced in Analysis Services 2008 and is used to track the server resources used .It can be queried like SQL –Like syntax. We can run DMV query in SQL server Management Studio in an MDX Query.

For Calculated Measure and Calclated Dimension Member and other entire details of Cube Catalog from SSAS.

SELECT * FROM $system.MDSCHEMA_MEMBERS

WHERE [MEMBER_TYPE] = 4

Refer some other DMV queries from the link

http://dwbi1.wordpress.com/2010/01/01/ssas-dmv-dynamic-management-view/

http://www.bidn.com/blogs/Anil/ssas/2101/how-to-find-a-calculated-measure-and-calculated-dimension-within-a-particular-cube

Thanks,

Anil Maharjan

Load 1TB data in just 30 Minutes with SSIS

Today , I came to read some nice blog post by Microsoft regarding the data loading process.

If your company needs to load the maximum data then I think this post would be really helpful.
If you want to load 1TB data in just 30 Minutes with SSIS then.. Plz read this post by Microsoft.
http://msdn.microsoft.com/en-us/library/dd537533(v=SQL.100).aspx

since, the article was posted long time ago but might be helpful for the one who is just looking for this kind of approach.
Though ,I could not tried it out but sooner or later I would definitely like to test this process too.

Thanks,
Anil Maharjan

Backup all SSAS databases automatically and schedually.

With the course of time, you might need to backup all the SSAS databases within a server instance automatically and schedule to backup these database monthly wise or according to your specific time set.

May be there are a lot of alternative methods to perform this task and many solution’s.

But I have researched a lot regarding to backup all the SSAS databases within a particular server instance.

In my last blog post I have shared “How to schedule and dynamically backup all the SQL databases within a server instance”.
Here ,I am going to show how to obtain all the cube databases backup automatically along with schedule time set.

These are the following steps you should have to follow.
1. Adding a linked server in SSMS.
You can simply add a linked server within a SSMS by using a script as
–Adding a linked server
EXEC master.dbo.sp_addlinkedserver
@server = N’SSAS_Backup’
, @srvproduct=N’MSOLAP’
, @provider=N’MSOLAP’
, @datasrc=N’ANILMAHARJAN’ /* <<< My Analysis Services server name */
/* <<< My Analysis Services database name */
go

–Setup security as per your environment requirements.
EXEC master.dbo.sp_addlinkedsrvlogin
@rmtsrvname=N’SSAS_Backup’
, @useself=N’False’
, @locallogin=NULL
, @rmtuser=NULL
, @rmtpassword=NULL
go

2. Make a SSIS package.
You can simply create the SSIS Package as shown in below

2.1. Create the table in SSMS of the output of all the catalog name within a server given by frying the query into the DMV in SSAS from SSMS.
You can use the following script in order to find all the current catalogs name within a server.
IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = ‘CubeBackupInfo’) DROP TABLE [CubeBackupInfo]
SELECT * into CubeBackupInfo FROM OPENQUERY(SSAS_Backup,’select * from $system.dbschema_catalogs’)

2.2. Read the total no of database backup to be made by reading the max count of catalog name from the table just we created above.

2.3. Read the backup XMLA within a variable into SSIS from a particular location.i.e backup XMLA that generated manually and we can also generate it by using a some C# or vb.net code embedded within SSIS.
Here is an XMLA script for backup
2.4. Now, within a for loop, set the max loop to the variable as @Max_No_Backup i.e max no of backup to be made.

2.5. Here read the catalog name of database with in a server one by one by using a table that we created before from CubeBackupInfo.
2.6 Modify the XMLA script using ‘Script Task’ within where I have used C# code in order to modify the XMLA and generate the modified XMLA for each catalog name one by one.

2.7. Backup all the cube database with in a server into a default location of SSAS backup.
-stores acc to the ‘catalog name’ along with the ‘system date’ in order to know the particular backup date. as i.e. TestCube-03-11-2011.abf
-also catalog name can overwrite it if it exist already.
i.e. : C:Program FilesMicrosoft SQL ServerMSAS10.MSSQLSERVEROLAPBackup

3.Make a SQL Server Job agent.
3.1. You can easily make a job in SQL by following this link http://www.sqlservercentral.com/articles/Stairway+Series/72267/
and Running and Scheduling SSIS Packages by following the link as http://www.sqlshare.com/running-and-scheduling-ssis-packages_53.aspx
4.Make schedule to backup all the catalogs/ database within a particular server. i.e. monthly or weekly according to your specific time set.

Hope this will help for someone , also I am thinking to post another blog about ‘Dynamic partition within a cube’ using a similar approach where I have researched a lot in this topic too.

Thanks,
Anil Maharjan