Attach remote DB file: February 2012

Saturday, February 25, 2012

Audit in SQL Server

Hi,
I need to setup a job in SQL Server for auditing all task made by the
sysadmin Group all time
I have tried to use the system stored procedures: sp_trace_create,
sp_trace_setevent, sp_trace_setstatus but I do not see the job's result in
any file or table.
I used this stored procedures in this way:
declare @.trace_id int
declare @.intOn bit
set @.intOn=1
exec sp_trace_create @.traceid = @.trace_id OUTPUT, @.options=3,
@.tracefile=N'c:\Audit', @.maxfilesize = Null, @.stoptime = Null
select @.trace_id
exec sp_trace_setevent @.traceid = @.trace_id, @.eventid = 117, @.columnid=1,
@.on=@.intOn
exec sp_trace_setstatus @.traceid = @.trace_id, @.status = 1
How can I make this task
Help me please
I know the SQL Profiler but I don't know the way to programming an automatic
task to run all time.
Thank so muchYou may want to look at a third party product such as Lumigent.
"Auditing SQL Server" wrote:
> Hi,
> I need to setup a job in SQL Server for auditing all task made by the
> sysadmin Group all time
> I have tried to use the system stored procedures: sp_trace_create,
> sp_trace_setevent, sp_trace_setstatus but I do not see the job's result in
> any file or table.
> I used this stored procedures in this way:
> declare @.trace_id int
> declare @.intOn bit
> set @.intOn=1
> exec sp_trace_create @.traceid = @.trace_id OUTPUT, @.options=3,
> @.tracefile=N'c:\Audit', @.maxfilesize = Null, @.stoptime = Null
> select @.trace_id
> exec sp_trace_setevent @.traceid = @.trace_id, @.eventid = 117, @.columnid=1,
> @.on=@.intOn
> exec sp_trace_setstatus @.traceid = @.trace_id, @.status = 1
> How can I make this task
> Help me please
> I know the SQL Profiler but I don't know the way to programming an automatic
> task to run all time.
> Thank so much|||Auditing SQL Server wrote:
> Hi,
> I need to setup a job in SQL Server for auditing all task made by the
> sysadmin Group all time
> I have tried to use the system stored procedures: sp_trace_create,
> sp_trace_setevent, sp_trace_setstatus but I do not see the job's
> result in any file or table.
>
The best way to see the code used to create a trace is to create the
exact trace you want in Profiler (including the server manages trace
option). Make sure yo include only the events you want and the columns
you require. Set any necessary filter conditions.
Then use the File - Script Trace menu option to generate the T-SQL for
the trace.
You'll need to manually turn it into a job and also manage the stopping
of the trace (which is not scripted). Also, consider placing the trace
file somewhere else besides to root folder of the boot drive. Place it
on a disk with sufficient available space (like the log file/temdb
drive). You cannot view the collected trace data in the file until you
stop the trace, so you might want to have the job start a new trace
using an incrementing file ID (or use a the new_id() function for the
name) and then stop the old trace. Then you can view the trace data
using fn_trace_gettable or using Profiler (SP4 Profiler will require a
soon-to-be released request-only hotfix to view the data in the file).
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Your problem seems to be @.options = 3. That is not documented. I tried with 2 and I got a proper
trace id back. Why don't you define the trace in Profiler and script that definition?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Auditing SQL Server" <Auditing SQL Server@.discussions.microsoft.com> wrote in message
news:84B4D6A4-045D-4442-ACB0-0332B84AEB63@.microsoft.com...
> Hi,
> I need to setup a job in SQL Server for auditing all task made by the
> sysadmin Group all time
> I have tried to use the system stored procedures: sp_trace_create,
> sp_trace_setevent, sp_trace_setstatus but I do not see the job's result in
> any file or table.
> I used this stored procedures in this way:
> declare @.trace_id int
> declare @.intOn bit
> set @.intOn=1
> exec sp_trace_create @.traceid = @.trace_id OUTPUT, @.options=3,
> @.tracefile=N'c:\Audit', @.maxfilesize = Null, @.stoptime = Null
> select @.trace_id
> exec sp_trace_setevent @.traceid = @.trace_id, @.eventid = 117, @.columnid=1,
> @.on=@.intOn
> exec sp_trace_setstatus @.traceid = @.trace_id, @.status = 1
> How can I make this task
> Help me please
> I know the SQL Profiler but I don't know the way to programming an automatic
> task to run all time.
> Thank so much

audit in SQL 2005

Expensive friends,

I am new in the T-SQL development and would like to count on aid of all. Good my problem in question is that I am needing to develop a system of audit in SQL 2005, however does not know if I am in the certain way. Already I created some codes, however they are Triggers of each table of data, then I found that it is not very practical, therefore to need to control 200 tables I will have that to make a Trigger for each one. Some to develop that it reads me could give a light to me. Already I verified that it is possible to use DDL for events of the Database (ex.DDL_DATABASE_LEVEL_EVENTS), however did not find the same for tables.

I am thankful for the aid of all!

You have several methods of creating auditing.

1. Triggers on EACH table, write data to audit table
2. Third party product that will create an audit log from the Transaction Log activity
3. Require all data activity to use stored procedures, and add auditing code to stored procedures

You are correct, there is not a database wide event that reacts to data changes.

(And we are not 'too' expensive!)

|||

You can automate the creation of DML audit triggers using this product:

http://www.apexsql.com/sql_tools_audit.asp

If you are looking to audit table DDL changes, then you should use the DDL_TABLE_EVENTS event group, more info is available at this link:

http://msdn2.microsoft.com/en-us/library/ms191441.aspx

Note that you don't have to write a DDL trigger for every table - you can write one trigger for all tables and inside the trigger you can parse the XML returned by the EVENTDATA function to extract the audit data you need.

Chris

|||

Arnie Rowland,

it wanted to be thankful for its aid. I find that in this in case that this problem must very be common between the DBA, who knows in the next version to SQL we let us can count with whom we it helps in this difficult task, that is to take care of of data base.

Not yet I will discourage and count on the contribution of the other friends.

Thanks a lot.

|||

Chris,

I Am thankful for me to pass these links, I wait that he helps me in uncurling of this task. As I commented I am not one expert in T-SQL, therefore all aid always is welcome.

I am thankful very for the aid.

Audit history for scheduled jobs (SQL Server 2000)

Hello,

I would like to be able to set up an audit of changes that are made to scheduled jobs, so that a history can be built up of of these changes. Anyone any ideas on how to do this?

Thanks

You can create a query to do a SELECt on the sysjobs table in the MSDB database

Audit Failures whenever I start the SQL Enterprise Manager

Hi,
When ever I start the Enterprise manger client from my laptop(Client), I see
the Failure Audit in the Security Eventlog. I registered all the servers
using sql authentication. I am running SQL Enterprisemanger on Windows Xp.
Here is one of the error:
Unprotection of auditable protected data.
Data Description: SQL Server Registration
Key Identifier: 1f3ba71a-6f52-4e58-8007-43ced74242b4
Protected Data Flags: 0x0
Protection Algorithms: 3DES-168 , SHA1-160
Failure Reason: 0x8009000B
For more information, see Help and Support Center at
http://go.microsoft.com/fwlink/events.asp.
Can anybody having the fix for this, because my securtiy Auditing team
questioning these failures.
Thanks
rb
1. Do you have encryption enabled on the client or server?
2. When this happens is the laptop connected to the network?
3. Do you have any IPSec rules setup for the SQL Server?
"rb" <srbssr@.yahoo.com> wrote in message
news:efxp4cN9EHA.2600@.TK2MSFTNGP09.phx.gbl...
> Hi,
> When ever I start the Enterprise manger client from my laptop(Client), I
see
> the Failure Audit in the Security Eventlog. I registered all the servers
> using sql authentication. I am running SQL Enterprisemanger on Windows Xp.
> Here is one of the error:
> Unprotection of auditable protected data.
> Data Description: SQL Server Registration
> Key Identifier: 1f3ba71a-6f52-4e58-8007-43ced74242b4
> Protected Data Flags: 0x0
> Protection Algorithms: 3DES-168 , SHA1-160
> Failure Reason: 0x8009000B
>
> For more information, see Help and Support Center at
> http://go.microsoft.com/fwlink/events.asp.
> Can anybody having the fix for this, because my securtiy Auditing team
> questioning these failures.
> Thanks
> rb
>

Audit failed logins

Hello:
I am trying to enable failed login attempt auditing and have succeeded with
by using a trace and event log alerts. The problem is there is no IP address
in the trace or alters. I have selected the "Host Name" column in profiler
but it only lists the hosts for successful logins, kind of pointless.
Any ideas on how I can get the IP address of failed login attempts?
Thanks.Hi,
SQL Server will not get IP Address of the Hosts machines. Probably you could
use the utilities like NETMON to track the IPAddress.
Thanks
Hari
SQL Server MVP
"emde" <emde@.na.com> wrote in message
news:%23XFJ8kjNFHA.3380@.TK2MSFTNGP15.phx.gbl...
> Hello:
> I am trying to enable failed login attempt auditing and have succeeded
> with
> by using a trace and event log alerts. The problem is there is no IP
> address
> in the trace or alters. I have selected the "Host Name" column in profiler
> but it only lists the hosts for successful logins, kind of pointless.
> Any ideas on how I can get the IP address of failed login attempts?
> Thanks.
>
>|||As Hari has pointed out, you can't do this in SQL2000 however in SQL2005,
the IP address is logged in the SQL Errorlog for failed logins.
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"emde" <emde@.na.com> wrote in message
news:%23XFJ8kjNFHA.3380@.TK2MSFTNGP15.phx.gbl...
> Hello:
> I am trying to enable failed login attempt auditing and have succeeded
> with
> by using a trace and event log alerts. The problem is there is no IP
> address
> in the trace or alters. I have selected the "Host Name" column in profiler
> but it only lists the hosts for successful logins, kind of pointless.
> Any ideas on how I can get the IP address of failed login attempts?
> Thanks.
>
>|||Good news. Hopefully this feature will be backported to SQL2000 in a future
service pack!
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
news:%23rKrNUoNFHA.1396@.TK2MSFTNGP10.phx.gbl...
> As Hari has pointed out, you can't do this in SQL2000 however in SQL2005,
> the IP address is logged in the SQL Errorlog for failed logins.
> --
> HTH
> Jasper Smith (SQL Server MVP)
> http://www.sqldbatips.com
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org

Audit Drop table

We would like to create a audit table for storing information if any
objects like tables / stored procedures are dropped.
Table would also contain information about time and the user name who
has dropped the object.
Any help would be great.

Thanks in advance.
Kamal"Kamal" <karora@.melstar.com> wrote in message
news:a4d6a1fb.0402060028.654b0f88@.posting.google.c om...
> We would like to create a audit table for storing information if any
> objects like tables / stored procedures are dropped.
> Table would also contain information about time and the user name who
> has dropped the object.
> Any help would be great.
> Thanks in advance.
> Kamal

One solution would be to create a trace for any SQL commands containing the
word DROP - see sp_trace_create and Profiler in Books Online. Or you could a
third-party audit tool, such as this one:

http://www.lumigent.com/products/entegra/entegra.htm

Simon

Audit Design Question

I need to audit Insert, Update and Delete on tables in the database.
But the sy

min of the app can selectively enable and disable auditing
on tables. So I need to be able to switch the auditing on and off.
Is there any built-in function in SqlServer 2005 that I can use to
track changes?
In the absence of built-in auditing I have come up with two possible
solutions:
1) Pass a patameter called @.bAudit to the stored procedures performing
CRUD tasks and if the parameter is true then insert a row into the
audit table (audit table has the same schema as the main table but with
a few more columns for tracking).
2) Use triggers on Insert, Update and Delete but I can't find out how
to disable triggers at run time.
Can you please point me in the right direction? Thanks.S Chapman,

> 2) Use triggers on Insert, Update and Delete but I can't find out how
> to disable triggers at run time.
Use the statement "alter table".
alter table dbo.t1
disable trigger trigger_name
See BOL for more info.

> 1) Pass a patameter called @.bAudit to the stored procedures performing
> CRUD tasks and if the parameter is true then insert a row into the
> audit table (audit table has the same schema as the main table but with
> a few more columns for tracking).
A possible solution could be having an options table and check the option
inside the trigger.
update conf_options
set audit = 1
where table_name = 'my_table'
go
create trigger tr_mytable on my_table
for insert, update, delete
as
if exists (select * from dbo.conf_options where table_name = 'my_table' and
audit = 1)
begin
-- put here the audit code
end
go
AMB
"S Chapman" wrote:

>
> I need to audit Insert, Update and Delete on tables in the database.
> But the sy

min of the app can selectively enable and disable auditing
> on tables. So I need to be able to switch the auditing on and off.
> Is there any built-in function in SqlServer 2005 that I can use to
> track changes?
> In the absence of built-in auditing I have come up with two possible
> solutions:
> 1) Pass a patameter called @.bAudit to the stored procedures performing
> CRUD tasks and if the parameter is true then insert a row into the
> audit table (audit table has the same schema as the main table but with
> a few more columns for tracking).
> 2) Use triggers on Insert, Update and Delete but I can't find out how
> to disable triggers at run time.
> Can you please point me in the right direction? Thanks.
>

Audit Delete Statements

Hi

I was curious whether it's possible to audit DELETE statements in the MS SQL database. I created a procedure (below), but I didn't find any event associated with DELETE statements.

Any help will be greatly appreciated!

Thanks,
Alla

CREATE proc sp_Turn_Audit_On
as
/************************************************** **/
/* Created by: SQL Profiler */
/* Date: 11/15/2006 05:16:40 PM */
/************************************************** **/

-- Create a Queue
declare @.rc int
declare @.TraceID int
declare @.maxfilesize bigint
declare @.StatusMsg varchar
declare @.ServerTraceFile varchar
set @.ServerTraceFile = 'E:\Program Files\Microsoft SQL Server\MSSQL\Trace\Audit_Info'
set @.maxfilesize = 1024

-- Client side File and Table cannot be scripted

-- Set the events
declare @.on bit
set @.on = 1

exec @.rc = sp_trace_create @.TraceID OUTPUT, 0, N'\\hostname\dbauditlog\my_dir', @.maxfilesize, NULL
print @.TraceID

if (@.rc != 0) goto error
exec sp_trace_setevent @.TraceID, 14, 1, @.on
exec sp_trace_setevent @.TraceID, 14, 6, @.on
exec sp_trace_setevent @.TraceID, 14, 9, @.on
exec sp_trace_setevent @.TraceID, 14, 10, @.on
exec sp_trace_setevent @.TraceID, 14, 11, @.on
exec sp_trace_setevent @.TraceID, 14, 12, @.on
exec sp_trace_setevent @.TraceID, 14, 13, @.on
exec sp_trace_setevent @.TraceID, 14, 14, @.on
exec sp_trace_setevent @.TraceID, 14, 16, @.on
exec sp_trace_setevent @.TraceID, 14, 17, @.on
exec sp_trace_setevent @.TraceID, 14, 18, @.on
-- Set the Filters
declare @.intfilter int
declare @.bigintfilter bigint

exec sp_trace_setfilter @.TraceID, 10, 0, 7, N'SQL Profiler'

-- Set the trace status to start
exec sp_trace_setstatus @.TraceID, 1
--SELECT @.StatusMsg = 'sp_trace_setstatus' + ' Error - ' + @.TraceID
-- display trace id for future references
select TraceID=@.TraceID

goto noCursor

error:
select ErrorCode=@.rc

noCursor:
return

GO
exec sp_procoption N'sp_Turn_Audit_On', N'startup', N'true'
GOTake a look at tirggers (http://doc.ddart.net/mssql/sql70/create_8.htm) :)|||I see...

I just noticed that there is an event to audit TSQL via SQL Profiler. I spooled the script to a SQL file. however, is there any way of filtering it that it would capture DELETE statements only?

Thanks,
Alla|||Spool SQL Profiler output to a table so you can query it.

audit database user creation

Hi,
Is it possible to find out ‘Who’ created a specific database user in database?
Thanks
bb
You can use Profiler (possibly also Event Notification if on 2005) for this. Check the "Security"
event categories.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"vinu thomas" <vinuthomas@.discussions.microsoft.com> wrote in message
news:AC83ED35-1AA8-4AA8-A3F5-0E6CCD7BDF52@.microsoft.com...
> Hi,
> Is it possible to find out ‘Who’ created a specific database user in database?
> Thanks
> bb
|||Your logic will work only for new login, i need to find for an existing login
"Tibor Karaszi" wrote:

> You can use Profiler (possibly also Event Notification if on 2005) for this. Check the "Security"
> event categories.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "vinu thomas" <vinuthomas@.discussions.microsoft.com> wrote in message
> news:AC83ED35-1AA8-4AA8-A3F5-0E6CCD7BDF52@.microsoft.com...
>
|||I don't follow you. You can catch creation of a user as a Profiler event. You asked:
[vbcol=seagreen]
I don't know why you now refer to a login... Can you clarify?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"vinu thomas" <vinuthomas@.discussions.microsoft.com> wrote in message
news:FCBC4E70-7417-4F85-860F-6AB3774C494A@.microsoft.com...[vbcol=seagreen]
> Your logic will work only for new login, i need to find for an existing login
> "Tibor Karaszi" wrote:
|||Our db contain a sql server login,say xxx, which was created last week by
somebody. i want to find out who created it...
"vinu thomas" wrote:

> Hi,
> Is it possible to find out ‘Who’ created a specific database user in database?
> Thanks
> bb
|||So you want to see *who created* a login or user (I'm still not sure whether you mean login or
user). SQL Server doesn't keep track of that information. You'd have to had a Profiler trace, DDL
trigger or Event Notification defined when this happened in order to determine it.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"vinu thomas" <vinuthomas@.discussions.microsoft.com> wrote in message
news:D2EB7A31-D207-4EC0-B69F-35F47AFB1279@.microsoft.com...[vbcol=seagreen]
> Our db contain a sql server login,say xxx, which was created last week by
> somebody. i want to find out who created it...
>
> "vinu thomas" wrote:

audit database user creation

Hi,
Is it possible to find out â'Whoâ' created a specific database user in database?
Thanks
bbYou can use Profiler (possibly also Event Notification if on 2005) for this. Check the "Security"
event categories.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"vinu thomas" <vinuthomas@.discussions.microsoft.com> wrote in message
news:AC83ED35-1AA8-4AA8-A3F5-0E6CCD7BDF52@.microsoft.com...
> Hi,
> Is it possible to find out â'Whoâ' created a specific database user in database?
> Thanks
> bb|||Your logic will work only for new login, i need to find for an existing login
"Tibor Karaszi" wrote:
> You can use Profiler (possibly also Event Notification if on 2005) for this. Check the "Security"
> event categories.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "vinu thomas" <vinuthomas@.discussions.microsoft.com> wrote in message
> news:AC83ED35-1AA8-4AA8-A3F5-0E6CCD7BDF52@.microsoft.com...
> > Hi,
> >
> > Is it possible to find out â'Whoâ' created a specific database user in database?
> >
> > Thanks
> > bb
>|||I don't follow you. You can catch creation of a user as a Profiler event. You asked:
>> > Is it possible to find out â'Whoâ' created a specific database user in database?
I don't know why you now refer to a login... Can you clarify?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"vinu thomas" <vinuthomas@.discussions.microsoft.com> wrote in message
news:FCBC4E70-7417-4F85-860F-6AB3774C494A@.microsoft.com...
> Your logic will work only for new login, i need to find for an existing login
> "Tibor Karaszi" wrote:
>> You can use Profiler (possibly also Event Notification if on 2005) for this. Check the "Security"
>> event categories.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "vinu thomas" <vinuthomas@.discussions.microsoft.com> wrote in message
>> news:AC83ED35-1AA8-4AA8-A3F5-0E6CCD7BDF52@.microsoft.com...
>> > Hi,
>> >
>> > Is it possible to find out â'Whoâ' created a specific database user in database?
>> >
>> > Thanks
>> > bb
>>|||Our db contain a sql server login,say xxx, which was created last week by
somebody. i want to find out who created it...
"vinu thomas" wrote:
> Hi,
> Is it possible to find out â'Whoâ' created a specific database user in database?
> Thanks
> bb|||So you want to see *who created* a login or user (I'm still not sure whether you mean login or
user). SQL Server doesn't keep track of that information. You'd have to had a Profiler trace, DDL
trigger or Event Notification defined when this happened in order to determine it.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"vinu thomas" <vinuthomas@.discussions.microsoft.com> wrote in message
news:D2EB7A31-D207-4EC0-B69F-35F47AFB1279@.microsoft.com...
> Our db contain a sql server login,say xxx, which was created last week by
> somebody. i want to find out who created it...
>
> "vinu thomas" wrote:
>> Hi,
>> Is it possible to find out â'Whoâ' created a specific database user in database?
>> Thanks
>> bb

audit database user creation

Hi,
Is it possible to find out ‘Who’ created a specific database user in dat
abase?
Thanks
bbYou can use Profiler (possibly also Event Notification if on 2005) for this.
Check the "Security"
event categories.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"vinu thomas" <vinuthomas@.discussions.microsoft.com> wrote in message
news:AC83ED35-1AA8-4AA8-A3F5-0E6CCD7BDF52@.microsoft.com...
> Hi,
> Is it possible to find out ‘Who’ created a specific database user in d
atabase?
> Thanks
> bb|||Your logic will work only for new login, i need to find for an existing logi
n
"Tibor Karaszi" wrote:

> You can use Profiler (possibly also Event Notification if on 2005) for thi
s. Check the "Security"
> event categories.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "vinu thomas" <vinuthomas@.discussions.microsoft.com> wrote in message
> news:AC83ED35-1AA8-4AA8-A3F5-0E6CCD7BDF52@.microsoft.com...
>|||I don't follow you. You can catch creation of a user as a Profiler event. Yo
u asked:
[vbcol=seagreen]
I don't know why you now refer to a login... Can you clarify?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"vinu thomas" <vinuthomas@.discussions.microsoft.com> wrote in message
news:FCBC4E70-7417-4F85-860F-6AB3774C494A@.microsoft.com...[vbcol=seagreen]
> Your logic will work only for new login, i need to find for an existing lo
gin
> "Tibor Karaszi" wrote:
>|||Our db contain a sql server login,say xxx, which was created last week by
somebody. i want to find out who created it...
"vinu thomas" wrote:

> Hi,
> Is it possible to find out ‘Who’ created a specific database user in d
atabase?
> Thanks
> bb|||So you want to see *who created* a login or user (I'm still not sure whether
you mean login or
user). SQL Server doesn't keep track of that information. You'd have to had
a Profiler trace, DDL
trigger or Event Notification defined when this happened in order to determi
ne it.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"vinu thomas" <vinuthomas@.discussions.microsoft.com> wrote in message
news:D2EB7A31-D207-4EC0-B69F-35F47AFB1279@.microsoft.com...[vbcol=seagreen]
> Our db contain a sql server login,say xxx, which was created last week by
> somebody. i want to find out who created it...
>
> "vinu thomas" wrote:
>

Audit Data

Hi all,
I would just like to ask whats the best way to make some audit on some of the tables in a MS SQL server, what i'm planning to have is to have a table which can contain all changes/inserts/deletion of some given tables, my first idea was to have this:

AuditTable that have the following fields:
AuditID, TableName, FieldName, OldValue, NewValue, UpdateBy, UpdateDate

then in all the given tables, i'll have insert, update and delete trigger, the issue comes down to the trigger, what will be the best way to have that trigger written in a way that it can be use for other tables as well? say if a table have more then 20 fields, I don't want to declare 20 var and compare them 1 by1, and if there is a diff, then i insert to the audittable, I want something that it can loop and (if possible) be able to use by other table as well, so the field name etc can get from sysobjects, but then how can you code it in a way that it can do that?

Or is there any better way to get the same result? currently i have an audit table for each table i want to audit on and its just wasting space, any help will be great.
Thanks,What do you need this for?|||One method is to use the Binary_Checksum() function across both the original record and the updated record. If the values returned are different, then the record has changed and you should copy the original into your archive table.|||Does that mean i need to check every single field? that will take a while isn't it? is there any better way? how do you guys do your audit?

The reason I need this for is to keep a log on who did what on when on selected tables in a given DB, where sometimes ppl will ask things like who delete this record, or who update that record etc, and i can't just keep the last update as they need to chase back who did updates, for example PersonA update 2 times and PersonB comes in and update 3 times then PersonD delete that record...i need to keep track of all these info.|||There are many off-the-shelf packages that will give you this functionality. Download the eval and see how it is done. Most likely it all bottles down to C2-based auditing trace.|||I ran across this the other day. The VB package builds a shadow audit table (per auditied table) and builds the triggers for insert, update and delete. Also has paramaters which build SP's to trim the audit records or restore from the audit table.

http://www.gotdotnet.com/Community/UserSamples/Details.aspx?SampleGuid=e94eb930-1f8d-4e9a-be65-38313ce31166|||I think you are going to have problems with audit table design:

"AuditTable that have the following fields:
AuditID, TableName, FieldName, OldValue, NewValue, UpdateBy, UpdateDate"

I suspect you will find it very difficult to manage or retrieve usefull information from a single audit table that covers all the tables in your database, though at first glance that may appear to be the simplest solution. You will find that writing queries against this to track changes or restore lost data will be quite cumbersome.

A more common approach is to create a copy of each of the tables you want to archive, perhaps with "_HIST" appended to the name and with your Update time columns included as well. Then just have an update trigger on your production tables that copies the entire record to its history table.

Even though you are increasing the number of tables, you will find this method much easier to use.

Audit and Error Log Tables

I've seen it done before that tables that contain Audit logs and Error data
are sometimes placed in a different database.
As some apps log user actions in quite a bit of detail these tables can get
quite big and make the database grow quite large.
I just wondered what peoples thoughts on this approach are. Other than the
size of the database does have a couple of massive tables have any impact on
the performance of the database.
TIA
MattC> I just wondered what peoples thoughts on this approach are. Other than
> the size of the database does have a couple of massive tables have any
> impact on the performance of the database.
Large audit tables won't negatively affect performance. You'll incur the
write overhead regardless of the database in which the tables reside.
The main issue with large tables is manageability. Audit tables are
particularly good candidates for SQL 2005 partitioning because they are
often large and can often be partitioned by date.
Hope this helps.
Dan Guzman
SQL Server MVP
"MattC" <m@.m.com> wrote in message
news:OVTeThWkHHA.4800@.TK2MSFTNGP02.phx.gbl...
> I've seen it done before that tables that contain Audit logs and Error
> data are sometimes placed in a different database.
> As some apps log user actions in quite a bit of detail these tables can
> get quite big and make the database grow quite large.
> I just wondered what peoples thoughts on this approach are. Other than
> the size of the database does have a couple of massive tables have any
> impact on the performance of the database.
> TIA
> MattC
>|||Hi,
We have a database that archives modified records to a separate database.
One drawback of that design is security management. Every time a new user
or domain group is granted write access to the main database, I have to
remember to add the login to the archive database as well.
Daniel Jameson
.............................
.
You don't have to agree with an artist's opinion
to appreciate the art with which he expresses it.
"MattC" <m@.m.com> wrote in message
news:OVTeThWkHHA.4800@.TK2MSFTNGP02.phx.gbl...
> I've seen it done before that tables that contain Audit logs and Error
> data are sometimes placed in a different database.
> As some apps log user actions in quite a bit of detail these tables can
> get quite big and make the database grow quite large.
> I just wondered what peoples thoughts on this approach are. Other than
> the size of the database does have a couple of massive tables have any
> impact on the performance of the database.
> TIA
> MattC
>

Audit and Error Log Tables

I've seen it done before that tables that contain Audit logs and Error data
are sometimes placed in a different database.
As some apps log user actions in quite a bit of detail these tables can get
quite big and make the database grow quite large.
I just wondered what peoples thoughts on this approach are. Other than the
size of the database does have a couple of massive tables have any impact on
the performance of the database.
TIA
MattC> I just wondered what peoples thoughts on this approach are. Other than
> the size of the database does have a couple of massive tables have any
> impact on the performance of the database.
Large audit tables won't negatively affect performance. You'll incur the
write overhead regardless of the database in which the tables reside.
The main issue with large tables is manageability. Audit tables are
particularly good candidates for SQL 2005 partitioning because they are
often large and can often be partitioned by date.
Hope this helps.
Dan Guzman
SQL Server MVP
"MattC" <m@.m.com> wrote in message
news:OVTeThWkHHA.4800@.TK2MSFTNGP02.phx.gbl...
> I've seen it done before that tables that contain Audit logs and Error
> data are sometimes placed in a different database.
> As some apps log user actions in quite a bit of detail these tables can
> get quite big and make the database grow quite large.
> I just wondered what peoples thoughts on this approach are. Other than
> the size of the database does have a couple of massive tables have any
> impact on the performance of the database.
> TIA
> MattC
>|||Hi,
We have a database that archives modified records to a separate database.
One drawback of that design is security management. Every time a new user
or domain group is granted write access to the main database, I have to
remember to add the login to the archive database as well.
--
Daniel Jameson
·.·.·.·.·.·.·.·.·.·.·.·.·.·.·.·.·.·.·.·.·.·.·.·.·.·.·.·.·.·.·.·.·.·.·.·.·.·.·.·
You don't have to agree with an artist's opinion
to appreciate the art with which he expresses it.
"MattC" <m@.m.com> wrote in message
news:OVTeThWkHHA.4800@.TK2MSFTNGP02.phx.gbl...
> I've seen it done before that tables that contain Audit logs and Error
> data are sometimes placed in a different database.
> As some apps log user actions in quite a bit of detail these tables can
> get quite big and make the database grow quite large.
> I just wondered what peoples thoughts on this approach are. Other than
> the size of the database does have a couple of massive tables have any
> impact on the performance of the database.
> TIA
> MattC
>

Audit All SQL Queries against all or specific databases

Hello,

I have a problem. I suspect a certain user in my company is using the SA account to log into Databases and run queries(query analyzer) to gather information this user is not suppose to view. The problem I have is this is an IT person so they know the SA password. How can I log specific or all queries run against all databases/tables/fields to find out exactly what this person is doing/seeing? Or if there is an alternative to find out this information?

Please advise?

Hi,

well that depends...on your way for data retrieval. If your only way for data retrieval are stored procedures, you could easily plugin another logging procedure in that code. If they can work with Select statement you will have to let the profiler run on some machine, capturing the SQL and Statement events with specifying additional attributes like the user_name and the host_name from where the user is connecting. That might get you a good information about the called statements. With putting a filter on the profiler you can narrow the event down to a database / HostName / Application etc.

HTH, Jens K. Suessmeyer.

http://www.sqlserver20095.de

|||

Hi,

Well they are only running these in Select statements. These are not stored procedures. So I am assuming the SQL profiler would work with capturing the data on the database / hostname /application, etc. If you have a good example, that would be great.

Please advise?

-K

|||Simply run profiler and select the RPC / STMT / SQL Events which the appropiate data you want to capture. Thats all.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

AUDIT all commands performed by Privileged Users on SQL 2000/2005

Is there any way to audit all commands executed by people with Sysadmin
access on SQL 2000 and SQL 2005 ? Any answer or suggestion would be greatly
appreciated.Hi
On SS2005 you are availalble to create a trigger on server/database level.
For moree details please refer to the BOL
"DDLooks" <DDLooks@.discussions.microsoft.com> wrote in message
news:893A8BF5-2298-4446-AC66-93840B922325@.microsoft.com...
> Is there any way to audit all commands executed by people with Sysadmin
> access on SQL 2000 and SQL 2005 ? Any answer or suggestion would be
> greatly
> appreciated.

Audit Add Windows Login?

Hi,
I want to audit add/drop of windows login to my sql server.
Through SQL Profiler Add/Drop login I can only Audit Add/Drop login of SQL
Security Login and not the windows login.
Is there any way to audit Adding of windows login?
Thanks
PushkarWhich version of SQL Server are you using?
"Pushkar" <pushkartiwari@.gmail.com> wrote in message
news:%23hFBWR3NGHA.1288@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I want to audit add/drop of windows login to my sql server.
> Through SQL Profiler Add/Drop login I can only Audit Add/Drop login of SQL
> Security Login and not the windows login.
> Is there any way to audit Adding of windows login?
> Thanks
> Pushkar
>|||I am using SQL Server SP4.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:ORZFdc3NGHA.720@.TK2MSFTNGP14.phx.gbl...
> Which version of SQL Server are you using?
>
> "Pushkar" <pushkartiwari@.gmail.com> wrote in message
> news:%23hFBWR3NGHA.1288@.TK2MSFTNGP09.phx.gbl...
>|||Run SQL Server Profiler to capture this event.
"Pushkar" <pushkartiwari@.gmail.com> wrote in message
news:OBHamF4NGHA.2624@.TK2MSFTNGP12.phx.gbl...
>I am using SQL Server SP4.
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:ORZFdc3NGHA.720@.TK2MSFTNGP14.phx.gbl...
>|||SQL Server profiler does not capture add/drop login for windows login.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eFOPVo6NGHA.2992@.tk2msftngp13.phx.gbl...
> Run SQL Server Profiler to capture this event.
>
>
> "Pushkar" <pushkartiwari@.gmail.com> wrote in message
> news:OBHamF4NGHA.2624@.TK2MSFTNGP12.phx.gbl...
>|||Take a look at syslogins system. Perform SELECT statement to compare rows
with a history ( of syslogins) for example table
I was thinking that you wanted to track a LOGIN connections , sorry
"Pushkar" <pushkartiwari@.gmail.com> wrote in message
news:%23Qr2vD7NGHA.2036@.TK2MSFTNGP14.phx.gbl...
> SQL Server profiler does not capture add/drop login for windows login.
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:eFOPVo6NGHA.2992@.tk2msftngp13.phx.gbl...
>|||Yes, it does.
You need to trace the "Audit AddLogin" event.
The "EventSubClass" data column holds the type of event (Add or Drop).
The "TargetLoginName" data column holds the name of the login.
Of course you need to set up a custom trace.
ML
http://milambda.blogspot.com/|||Sorry!
Correction:
Event is: "Audit Login GDR Event" (EventSubClass data column holds either
Grant or Deny).
ML
p.s. I get stupid as my coffee gets cold.
http://milambda.blogspot.com/|||Thanks!!! It works.
Pushkar
"ML" <ML@.discussions.microsoft.com> wrote in message
news:47D3D9B6-DBBB-47E0-9737-59265B96F201@.microsoft.com...
> Sorry!
> Correction:
> Event is: "Audit Login GDR Event" (EventSubClass data column holds either
> Grant or Deny).
>
> ML
> p.s. I get stupid as my coffee gets cold.
> --
> http://milambda.blogspot.com/

Audit a Table

Hello,
How do I audit who is changing data in a table ? SQL 2000
We have a table and records keep getting deleted ...........
in a User Database.
Thanks
MaTTMatt,
you could create triggers which write these details to an audit table if you
want to do this continuously, or as a temporary measure you could run a tool
called Profiler and monitor the TSQL and Stored Procedure events.
Regards,
Paul Ibison|||Also there is a tool from Lumigent which will do what you wish...
www.lumigent.com
I think there may be a second vendor which can do this as well, but I don't
remember the name...
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"matt" <matt.smith@.jacobsonco.com> wrote in message
news:eNkbMxsLEHA.3292@.TK2MSFTNGP11.phx.gbl...
> Hello,
> How do I audit who is changing data in a table ? SQL 2000
> We have a table and records keep getting deleted ...........
> in a User Database.
> Thanks
> MaTT
>

Audit a SQl connection

hi ,

Is it possible to audit a sql Connection, and retrieve all the commands fired from that connection ,

how to find all commands fired to a database , as it is shown in " Profiler ".

and its command text .

Use Profiler, run a trace remotely.

Use Third party Auditing products

Audit Tools
ApexSQL Audit http://www.apexsql.com/sql_tools_audit.asp
AuditDatabase (Free Web based trigger generation) http://www.auditdatabase.com/
Lumigent Adit DB http://www.lumigent.com/products/auditdb.html
OmniAudit http://www.krell-software.com/omniaudit/index.asp
SQLLog http://www.rlpsoftware.com/mainframe.asp?contents=SQLLog.asp&mainmenu=SQLLog&submenu=Info
Upscene SQL Log Manager http://www.upscene.com/index.htm?./products/audit/mssqllm_main.htm
DB Audit Expert http://www.softtreetech.com/dbaudit/

Audit 3 fields in one table

Hello,
I am unfamiliar with SQL Server 2005 but want to learn more.
I have an application with a MS Access 2003 Front end and a 2005 SQL
Server backend.
I have a table called HandoverClinicalChanges, there are 3 main fields
which are updated by Doctors - usually by adding to the end of the
previous entry, sometimes by deleting the previous entry and adding
their own.
I want to be able to capture the changes and additionsthey record to
these fields in a table (? audit table).
Is there an easy way do this in SQL SERVER 2005 or should I do it in
ACCESS FE?
Thank you for any help and advice.
Regards
KevHello,
Take a look into the INSERT/DELETE Triggers in books online. So when ever
there is a delete or Insert happendson table HandoverClinicalChanges a
record
will be inserted to the audiit table. See the below URL for examples and
usages of triggers.
http://www.sql-server-performance.com/nn_triggers.asp
Thanks
Hari
"Kev" <kevin.vaughan@.nhw.hume.org.au> wrote in message
news:1172208080.462256.162750@.p10g2000cwp.googlegroups.com...
> Hello,
> I am unfamiliar with SQL Server 2005 but want to learn more.
> I have an application with a MS Access 2003 Front end and a 2005 SQL
> Server backend.
> I have a table called HandoverClinicalChanges, there are 3 main fields
> which are updated by Doctors - usually by adding to the end of the
> previous entry, sometimes by deleting the previous entry and adding
> their own.
> I want to be able to capture the changes and additionsthey record to
> these fields in a table (? audit table).
> Is there an easy way do this in SQL SERVER 2005 or should I do it in
> ACCESS FE?
> Thank you for any help and advice.
> Regards
> Kev
>|||Hello Hari and everyone else,
Thanks for your response.
So far I've got the below code working to an extent:
The problem with it is that it inserts the entire cell contents (old
and new data) of the datasheet cell, not just the new data which was
typed by the user. Is this possible to do?
Regards
Kevin
CREATE TRIGGER tgClinicalInstAudit_Trigger
ON kvaugha.dbo_ClinicalInstances
AFTER UPDATE
AS
IF UPDATE ([MedicalProblems]) OR UPDATE ([MedicalPlan]) OR UPDATE
([ActionsTaken])
BEGIN
INSERT INTO ClinicalHandoverAudit
([MedProbs]
,[MedPlan]
,[ActsTaken]
,[AudDateTime])
SELECT
inserted.[MedicalProblems]
,inserted.[MedicalPlan]
,inserted.[ActionsTaken]
,GETDATE()
FROM inserted;
end|||Kev (kevin.vaughan@.nhw.hume.org.au) writes:
> Hello Hari and everyone else,
> Thanks for your response.
> So far I've got the below code working to an extent:
> The problem with it is that it inserts the entire cell contents (old
> and new data) of the datasheet cell, not just the new data which was
> typed by the user. Is this possible to do?
So if for instance MedProbs initially read: "This patient is sick", and the
doctor then adds. "And he will never get well", you only want "And he will
never get well" to be added to the audit table?
If think this would be quite difficult, not the least since you say
that dcctors also can modify already exinst text. What if the doctor
changes the text to "This patient is very sick". Should the trigger
just log "very"?
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

audit

Hi,
I want to audit the log-on attempts and record with
another information (login, user, NTusername, date,
hostname, etc.)
We reviewed Audit level but errorlog only sent the login
used, C2 gave me a lot of information buy I only want a
subset of it.
Do you know how can I get this kind of information?
Do exist another tool to help me to get this information?
ThanksIf you want a subset of what you get with C2 auditing, you
can create a SQL Profiler trace that will capture what you
want. C2 auditing is comprised of classes and events
available to you when using Profiler. Look at the Security
Audit event classes when defining a trace in Profiler.
-Sue
On Mon, 29 Mar 2004 09:14:19 -0800, "MB"
<anonymous@.discussions.microsoft.com> wrote:

>Hi,
> I want to audit the log-on attempts and record with
>another information (login, user, NTusername, date,
>hostname, etc.)
> We reviewed Audit level but errorlog only sent the login
>used, C2 gave me a lot of information buy I only want a
>subset of it.
> Do you know how can I get this kind of information?
> Do exist another tool to help me to get this information?
>Thanks

Audit

I want to register into a table each time a user creates, modifies or deletes any object in a database. It's not possible to add a trigger to the sysobjects table. What can I do?SQL 2005 will have that feature...the best I can tell you is to not allow them DDL Priviliges, and have them run all the DDL request through you...

Audit

Hi All,
I have one audit table in my db and i would like to do this:
1: always that user login in SQL I would like add one entry in this table.
(like this, USER 'xxx' SID 'xxx' log on Server 13:30:22...) I use Mixed
Mode (authentication)
2: always that user INSER, DELET or UPDATE tables, I would like add one
entry in audit table. (like this, USER 'xxx' SID 'xxx' DELET of table'xx'
13:30:22...)
Have any way to do this, or have other better way to do this?
ThanksThird party tools are going to be much better than re-inventing the wheel.
Lumigent's Entegra comes to mind, but there are several others available.
"ReTF" <re.tf@.newsgroup.nospam> wrote in message
news:%23BmV0bZqFHA.3352@.TK2MSFTNGP14.phx.gbl...
> Hi All,
> I have one audit table in my db and i would like to do this:
> 1: always that user login in SQL I would like add one entry in this
> table. (like this, USER 'xxx' SID 'xxx' log on Server 13:30:22...) I use
> Mixed Mode (authentication)
> 2: always that user INSER, DELET or UPDATE tables, I would like add one
> entry in audit table. (like this, USER 'xxx' SID 'xxx' DELET of table'xx'
> 13:30:22...)
> Have any way to do this, or have other better way to do this?
> Thanks
>|||I'm total agree with Aaron Bertrand, but if you can not afford such tools
then the only way in SQLServer 2000 is
- Profiler (not from GUI)
- c2 audit mode option (see BOL)
Fortunately SQLServer 2005 helps a lot with DDL triggers
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:uMhz0eZqFHA.3920@.TK2MSFTNGP09.phx.gbl...
> Third party tools are going to be much better than re-inventing the wheel.
> Lumigent's Entegra comes to mind, but there are several others available.
>
> "ReTF" <re.tf@.newsgroup.nospam> wrote in message
> news:%23BmV0bZqFHA.3352@.TK2MSFTNGP14.phx.gbl...
>

Auction Best Practice

Hi to everyone!
I've to create a little auction system that runs on web. Before starting developing, I'll would like to be sure to use the best practice...
The main aspect is to avoid conflicts on database updating with bids, i.e. if a user places his bid I've to be absolutely sure that his bid is the highest at the moment of updating database. If not, I have to refuse it...
So I ask you: using transaction is the best way for assuring the non-conflicts? And may I have to be careful of some other aspect in ASP.NET pages? Or there's no problem of conflicts at page level?
Thank you very much in advance for any suggestion, and If anyone has some other thing to say about possible problem on auctions I'll be glad to hear him!!!! ;-)

I think one statement query is more safe, like

Update auctiontable setprice=@.newprice,auctionuser=@.theuser whereid=@.actionproductid and price<@.newprice

if @.@.rowcount>0 --successfully updated

...

else -- higher price is in

...

|||Firts of all thank you for the suggestion!
But what does happen if there are 2 concurrent updates? without transaction is sure that there's no conflict?
thanks|||There is lock involved in that one line statement, it is a implicit lock, SQL can handle it. I don't think you need complex logic here, you just need make sure the auctor in the record is the one gives that price.|||But what do you mean with "you just need make sure the auctor in the record is the one gives that price"? May I insert some special check?
Thanks again!|||that statement can only make sure in the record, the price is from the user.

After the statement, what you want to do? send an email to the user, show the current price on somewhere? I think no matter what you do, just read the table to find out what is the current user in there.|||

Sotty, but I'm not sure I've correctly understood what you said...

If a user makes his bid, I update the bids-table with his price (using the update-query you wrote); if the @.@.rowcont>0 it means that no one else has made an higher bid, otherwise I have to notify the auctor (wiht a non-confirmation page) that someone else has placed an higher bid and his bid is not acceptable.

At the end of the auction (the auction will be available for 30-50 minutes) I would like to send an email to the winner (which I find out reading from the table as you suggest) notifying the winning.
Is this correct or you suggest me something else?
Thanks for you patience!

ATX Caribou, ME

I try to create assembly with UNSAFE permissions.
I granted "unsafe assembly" to my login, set TRUSTWORTHY property ON.
Now I have this error:

Could not obtain information about Windows NT group/user <MyDomain>/<MyName>, error code 0x5. (Microsoft SQL Server, Error: 15404).

How to resolve this?

Looks like your server isn't able to reach the D.C. to get information on your particular user/group...have you verified connectivity to the domain controller?

|||On a side note, it isn't recommended setting the TRUSTWORTHY property to ON if you only need to create an unsafe assembly. You can also create an asymmetric key, map a login to that key, and grant UNSAFE permission to the login to create an unsafe assembly. See http://msdn2.microsoft.com/ms345106(en-US,SQL.90).aspx for more info.|||Chad, how to verify connectivity to D.C.?|||I've gotten that error before. I think the owner of your database doesn't have permission to create assemblies. Try running sp_ChangeDBOwner 'sa' to change the owner of your database to sa.|||

Good Morning,
Is this the same Chad Boyd from Mars Hill who used to work for ATX in Caribou, ME?

ChrisRogeski@.gmail.com

Attributes, Properties; Dimensions, Levels, and Hierarchies, oh my!

I have a most basic question: how to correctly access properties (attributes) of dimension members that are at a different level. Here's a simple query from AdventureWorks to illustrate:

Code Snippet

select

[Customer].[Customer].members

dimension properties

[customer].[customer].[postal code],

[customer].[postal code].[city]

on 1,

[Measures].[Internet Sales Amount]

on 0

from [Adventure Works]

This query works, but the city property is left out of the results (postal code is provided). How can I get City, State-Province, Country, etc, as properties in a query like this? (As the relationships are all correctly defined, and the levels for these attributes/properties are less restrictive than the query level, I'm totally missing why this doesn't work in either the form given -- but do I have the name perhaps wrong?)

Seperately, I have read (somewhere) that property access is to be discouraged in SSAS05, instead prefering direct attribute access via UDM; is there a better way to write these sorts of queries, say including City, etc in the output grid area?

Thanks as always!

To somewhat answer my own query, I have found that:

Code Snippet

with member [measures].[cc] as [customer].[city].MemberValue + ', ' + [customer].[state-province].MemberValue

select

[Customer].[Customer].members

dimension properties

[customer].[customer].[postal code],

[customer].[postal code].[city]

on 1,

{[measures].[cc], [Measures].[Internet Sales Amount]}

on 0

from [Adventure Works]

properly returns the customer "city, state" in the output grid area. But I am still interested in how to obtain the city and state-province instead as properties of the customer members on axis(1).....

Thanks again!

Attributes of Database

Hi,
Does anybody know how to get the following information for a particular
database in SQL 2000
1. Whether it is a System Object or not.
2. Create for Attach
3. Replication Status
We can get this values through SQL-DMO, but can I get these values from some
system tables or in-built functions
TIA
PrasadTake a look at OBJECTPROPERTY ( id , property ) command in the BOL
"Prasad" <ekke_nikhil@.yahoo.co.uk> wrote in message
news:%23RrjJaVMGHA.648@.TK2MSFTNGP14.phx.gbl...
> Hi,
> Does anybody know how to get the following information for a particular
> database in SQL 2000
> 1. Whether it is a System Object or not.
> 2. Create for Attach
> 3. Replication Status
> We can get this values through SQL-DMO, but can I get these values from
> some system tables or in-built functions
> TIA
> Prasad
>|||Hi Prasad
You can use the DATABASEPROPERTYEX function to check the database
replication status :
SELECT DATABASEPROPERTYEX ('database_name, 'IsPublished')
SELECT DATABASEPROPERTYEX ('database_name, 'IsSubscribed')
SELECT DATABASEPROPERTYEX ('database_name, 'IsMergePublished')
To test if the db is a system database, just check the name. There is only a
short list of 'system databases' (master, model, tempdb, msdb, distribution)
and if it's not one of the known ones, it's not a system database.
To check if the database was created for attach is not possible. Once the
database is created, it doesn't retain history as to how it was created. It
is equal to all other databases. Why do you want to know this?
--
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Prasad" <ekke_nikhil@.yahoo.co.uk> wrote in message
news:%23RrjJaVMGHA.648@.TK2MSFTNGP14.phx.gbl...
> Hi,
> Does anybody know how to get the following information for a particular
> database in SQL 2000
> 1. Whether it is a System Object or not.
> 2. Create for Attach
> 3. Replication Status
> We can get this values through SQL-DMO, but can I get these values from
> some system tables or in-built functions
> TIA
> Prasad
>|||Thanks Kalen
Isn't there a more cleaner way of finding the system databases, comparing
the names would mean hard-coding the stuff.
and regarding the "created for attach" field bcoz SQL-DMO returns this value
I also wanted to show the same.
Thanks
Prasad
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:%23jMoCSYMGHA.2124@.TK2MSFTNGP14.phx.gbl...
> Hi Prasad
> You can use the DATABASEPROPERTYEX function to check the database
> replication status :
> SELECT DATABASEPROPERTYEX ('database_name, 'IsPublished')
> SELECT DATABASEPROPERTYEX ('database_name, 'IsSubscribed')
> SELECT DATABASEPROPERTYEX ('database_name, 'IsMergePublished')
> To test if the db is a system database, just check the name. There is only
> a short list of 'system databases' (master, model, tempdb, msdb,
> distribution) and if it's not one of the known ones, it's not a system
> database.
> To check if the database was created for attach is not possible. Once the
> database is created, it doesn't retain history as to how it was created.
> It is equal to all other databases. Why do you want to know this?
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.solidqualitylearning.com
>
> "Prasad" <ekke_nikhil@.yahoo.co.uk> wrote in message
> news:%23RrjJaVMGHA.648@.TK2MSFTNGP14.phx.gbl...
>
>|||For an alternative look at the sysdatabases table. The sid column stores the
System ID of the database creator - its the same "fake" value for each syste
m
database created at install.
I'd hard-code the names, though.
ML
http://milambda.blogspot.com/|||Thanks
But its not fake sid its the sid for "sa" which means suppose if a new
database is created by "sa" it would also have the same sid.
Thanks
Prasad
"ML" <ML@.discussions.microsoft.com> wrote in message
news:01649392-978B-4C06-889F-8EE342A5C7E1@.microsoft.com...
> For an alternative look at the sysdatabases table. The sid column stores
> the
> System ID of the database creator - its the same "fake" value for each
> system
> database created at install.
> I'd hard-code the names, though.
>
> ML
> --
> http://milambda.blogspot.com/|||You're right. Sorry. What was I thinking...?
ML
http://milambda.blogspot.com/|||Hi, Prasad

> Isn't there a more cleaner way of finding the system databases,
> comparing the names would mean hard-coding the stuff.
I don't know any other way; AFAIK, Enterprise Manager and Management
Studio are doing the same thing.
[vbcol=seagreen]
> and regarding the "created for attach" field bcoz SQL-DMO returns this value[/vbco
l]
The CreateForAttach property in SQL-DMO is used to specify how the
database will be created (before appending the Database object to the
Databases collection).
Razvan|||Thanks Razvan
"Razvan Socol" <rsocol@.gmail.com> wrote in message
news:1140015795.987952.21330@.f14g2000cwb.googlegroups.com...
> Hi, Prasad
>
> I don't know any other way; AFAIK, Enterprise Manager and Management
> Studio are doing the same thing.
>
> The CreateForAttach property in SQL-DMO is used to specify how the
> database will be created (before appending the Database object to the
> Databases collection).
> Razvan
>

Attributes of Database

Hi,
Does anybody know how to get the following information for a particular
database in SQL 2000
1. Whether it is a System Object or not.
2. Create for Attach
3. Replication Status
We can get this values through SQL-DMO, but can I get these values from some
system tables or in-built functions
TIA
Prasad
Take a look at OBJECTPROPERTY ( id , property ) command in the BOL
"Prasad" <ekke_nikhil@.yahoo.co.uk> wrote in message
news:%23RrjJaVMGHA.648@.TK2MSFTNGP14.phx.gbl...
> Hi,
> Does anybody know how to get the following information for a particular
> database in SQL 2000
> 1. Whether it is a System Object or not.
> 2. Create for Attach
> 3. Replication Status
> We can get this values through SQL-DMO, but can I get these values from
> some system tables or in-built functions
> TIA
> Prasad
>
|||Hi Prasad
You can use the DATABASEPROPERTYEX function to check the database
replication status :
SELECT DATABASEPROPERTYEX ('database_name, 'IsPublished')
SELECT DATABASEPROPERTYEX ('database_name, 'IsSubscribed')
SELECT DATABASEPROPERTYEX ('database_name, 'IsMergePublished')
To test if the db is a system database, just check the name. There is only a
short list of 'system databases' (master, model, tempdb, msdb, distribution)
and if it's not one of the known ones, it's not a system database.
To check if the database was created for attach is not possible. Once the
database is created, it doesn't retain history as to how it was created. It
is equal to all other databases. Why do you want to know this?
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Prasad" <ekke_nikhil@.yahoo.co.uk> wrote in message
news:%23RrjJaVMGHA.648@.TK2MSFTNGP14.phx.gbl...
> Hi,
> Does anybody know how to get the following information for a particular
> database in SQL 2000
> 1. Whether it is a System Object or not.
> 2. Create for Attach
> 3. Replication Status
> We can get this values through SQL-DMO, but can I get these values from
> some system tables or in-built functions
> TIA
> Prasad
>
|||Thanks Kalen
Isn't there a more cleaner way of finding the system databases, comparing
the names would mean hard-coding the stuff.
and regarding the "created for attach" field bcoz SQL-DMO returns this value
I also wanted to show the same.
Thanks
Prasad
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:%23jMoCSYMGHA.2124@.TK2MSFTNGP14.phx.gbl...
> Hi Prasad
> You can use the DATABASEPROPERTYEX function to check the database
> replication status :
> SELECT DATABASEPROPERTYEX ('database_name, 'IsPublished')
> SELECT DATABASEPROPERTYEX ('database_name, 'IsSubscribed')
> SELECT DATABASEPROPERTYEX ('database_name, 'IsMergePublished')
> To test if the db is a system database, just check the name. There is only
> a short list of 'system databases' (master, model, tempdb, msdb,
> distribution) and if it's not one of the known ones, it's not a system
> database.
> To check if the database was created for attach is not possible. Once the
> database is created, it doesn't retain history as to how it was created.
> It is equal to all other databases. Why do you want to know this?
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.solidqualitylearning.com
>
> "Prasad" <ekke_nikhil@.yahoo.co.uk> wrote in message
> news:%23RrjJaVMGHA.648@.TK2MSFTNGP14.phx.gbl...
>
>
|||For an alternative look at the sysdatabases table. The sid column stores the
System ID of the database creator - its the same "fake" value for each system
database created at install.
I'd hard-code the names, though.
ML
http://milambda.blogspot.com/
|||Thanks
But its not fake sid its the sid for "sa" which means suppose if a new
database is created by "sa" it would also have the same sid.
Thanks
Prasad
"ML" <ML@.discussions.microsoft.com> wrote in message
news:01649392-978B-4C06-889F-8EE342A5C7E1@.microsoft.com...
> For an alternative look at the sysdatabases table. The sid column stores
> the
> System ID of the database creator - its the same "fake" value for each
> system
> database created at install.
> I'd hard-code the names, though.
>
> ML
> --
> http://milambda.blogspot.com/
|||You're right. Sorry. What was I thinking...?
ML
http://milambda.blogspot.com/
|||Hi, Prasad

> Isn't there a more cleaner way of finding the system databases,
> comparing the names would mean hard-coding the stuff.
I don't know any other way; AFAIK, Enterprise Manager and Management
Studio are doing the same thing.

> and regarding the "created for attach" field bcoz SQL-DMO returns this value
The CreateForAttach property in SQL-DMO is used to specify how the
database will be created (before appending the Database object to the
Databases collection).
Razvan
|||Thanks Razvan
"Razvan Socol" <rsocol@.gmail.com> wrote in message
news:1140015795.987952.21330@.f14g2000cwb.googlegro ups.com...
> Hi, Prasad
>
> I don't know any other way; AFAIK, Enterprise Manager and Management
> Studio are doing the same thing.
>
> The CreateForAttach property in SQL-DMO is used to specify how the
> database will be created (before appending the Database object to the
> Databases collection).
> Razvan
>

Attributes of Database

Take a look at OBJECTPROPERTY ( id , property ) command in the BOL
"Pra

" <ekke_nikhil@.yahoo.co.uk> wrote in message
news:%23RrjJaVMGHA.648@.TK2MSFTNGP14.phx.gbl...
> Hi,
> Does anybody know how to get the following information for a particular
> database in SQL 2000
> 1. Whether it is a System Object or not.
> 2. Create for Attach
> 3. Replication Status
> We can get this values through SQL-DMO, but can I get these values from
> some system tables or in-built functions
> TIA
> Pra

>|||Hi Pra

You can use the DATABASEPROPERTYEX function to check the database
replication status :
SELECT DATABASEPROPERTYEX ('database_name, 'IsPublished')
SELECT DATABASEPROPERTYEX ('database_name, 'IsSubscribed')
SELECT DATABASEPROPERTYEX ('database_name, 'IsMergePublished')
To test if the db is a system database, just check the name. There is only a
short list of 'system databases' (master, model, tempdb, msdb, distribution)
and if it's not one of the known ones, it's not a system database.
To check if the database was created for attach is not possible. Once the
database is created, it doesn't retain history as to how it was created. It
is equal to all other databases. Why do you want to know this?
--
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Pra

>|||Thanks Kalen
Isn't there a more cleaner way of finding the system databases, comparing
the names would mean hard-coding the stuff.
and regarding the "created for attach" field bcoz SQL-DMO returns this value
I also wanted to show the same.
Thanks
Pra

"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:%23jMoCSYMGHA.2124@.TK2MSFTNGP14.phx.gbl...
> Hi Pra

> You can use the DATABASEPROPERTYEX function to check the database
> replication status :
> SELECT DATABASEPROPERTYEX ('database_name, 'IsPublished')
> SELECT DATABASEPROPERTYEX ('database_name, 'IsSubscribed')
> SELECT DATABASEPROPERTYEX ('database_name, 'IsMergePublished')
> To test if the db is a system database, just check the name. There is only
> a short list of 'system databases' (master, model, tempdb, msdb,
> distribution) and if it's not one of the known ones, it's not a system
> database.
> To check if the database was created for attach is not possible. Once the
> database is created, it doesn't retain history as to how it was created.
> It is equal to all other databases. Why do you want to know this?
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.solidqualitylearning.com
>
> "Pra

" <ekke_nikhil@.yahoo.co.uk> wrote in message
> news:%23RrjJaVMGHA.648@.TK2MSFTNGP14.phx.gbl...
>
>|||For an alternative look at the sysdatabases table. The sid column stores the
System ID of the database creator - its the same "fake" value for each syste
m
database created at install.
I'd hard-code the names, though.
ML
http://milambda.blogspot.com/|||Thanks
But its not fake sid its the sid for "sa" which means suppose if a new
database is created by "sa" it would also have the same sid.
Thanks
Pra

"ML" <ML@.discussions.microsoft.com> wrote in message
news:01649392-978B-4C06-889F-8EE342A5C7E1@.microsoft.com...
> For an alternative look at the sysdatabases table. The sid column stores
> the
> System ID of the database creator - its the same "fake" value for each
> system
> database created at install.
> I'd hard-code the names, though.
>
> ML
> --
> http://milambda.blogspot.com/|||You're right. Sorry. What was I thinking...?
ML
http://milambda.blogspot.com/|||Hi, Pra

> Isn't there a more cleaner way of finding the system databases,
> comparing the names would mean hard-coding the stuff.
I don't know any other way; AFAIK, Enterprise Manager and Management
Studio are doing the same thing.
> and regarding the "created for attach" field bcoz SQL-DMO returns this value[/colo
r]
The CreateForAttach property in SQL-DMO is used to specify how the
database will be created (before appending the Database object to the
Databases collection).
Razvan|||Thanks Razvan
"Razvan Socol" <rsocol@.gmail.com> wrote in message
news:1140015795.987952.21330@.f14g2000cwb.googlegroups.com...
> Hi, Pra

>
> I don't know any other way; AFAIK, Enterprise Manager and Management
> Studio are doing the same thing.
>
> The CreateForAttach property in SQL-DMO is used to specify how the
> database will be created (before appending the Database object to the
> Databases collection).
> Razvan
>

Attributes of Database

Hi,
Does anybody know how to get the following information for a particular
database in SQL 2000
1. Whether it is a System Object or not.
2. Create for Attach
3. Replication Status
We can get this values through SQL-DMO, but can I get these values from some
system tables or in-built functions
TIA
PrasadTake a look at OBJECTPROPERTY ( id , property ) command in the BOL
"Prasad" <ekke_nikhil@.yahoo.co.uk> wrote in message
news:%23RrjJaVMGHA.648@.TK2MSFTNGP14.phx.gbl...
> Hi,
> Does anybody know how to get the following information for a particular
> database in SQL 2000
> 1. Whether it is a System Object or not.
> 2. Create for Attach
> 3. Replication Status
> We can get this values through SQL-DMO, but can I get these values from
> some system tables or in-built functions
> TIA
> Prasad
>|||Hi Prasad
You can use the DATABASEPROPERTYEX function to check the database
replication status :
SELECT DATABASEPROPERTYEX ('database_name, 'IsPublished')
SELECT DATABASEPROPERTYEX ('database_name, 'IsSubscribed')
SELECT DATABASEPROPERTYEX ('database_name, 'IsMergePublished')
To test if the db is a system database, just check the name. There is only a
short list of 'system databases' (master, model, tempdb, msdb, distribution)
and if it's not one of the known ones, it's not a system database.
To check if the database was created for attach is not possible. Once the
database is created, it doesn't retain history as to how it was created. It
is equal to all other databases. Why do you want to know this?
--
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Prasad" <ekke_nikhil@.yahoo.co.uk> wrote in message
news:%23RrjJaVMGHA.648@.TK2MSFTNGP14.phx.gbl...
> Hi,
> Does anybody know how to get the following information for a particular
> database in SQL 2000
> 1. Whether it is a System Object or not.
> 2. Create for Attach
> 3. Replication Status
> We can get this values through SQL-DMO, but can I get these values from
> some system tables or in-built functions
> TIA
> Prasad
>|||Thanks Kalen
Isn't there a more cleaner way of finding the system databases, comparing
the names would mean hard-coding the stuff.
and regarding the "created for attach" field bcoz SQL-DMO returns this value
I also wanted to show the same.
Thanks
Prasad
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:%23jMoCSYMGHA.2124@.TK2MSFTNGP14.phx.gbl...
> Hi Prasad
> You can use the DATABASEPROPERTYEX function to check the database
> replication status :
> SELECT DATABASEPROPERTYEX ('database_name, 'IsPublished')
> SELECT DATABASEPROPERTYEX ('database_name, 'IsSubscribed')
> SELECT DATABASEPROPERTYEX ('database_name, 'IsMergePublished')
> To test if the db is a system database, just check the name. There is only
> a short list of 'system databases' (master, model, tempdb, msdb,
> distribution) and if it's not one of the known ones, it's not a system
> database.
> To check if the database was created for attach is not possible. Once the
> database is created, it doesn't retain history as to how it was created.
> It is equal to all other databases. Why do you want to know this?
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.solidqualitylearning.com
>
> "Prasad" <ekke_nikhil@.yahoo.co.uk> wrote in message
> news:%23RrjJaVMGHA.648@.TK2MSFTNGP14.phx.gbl...
>> Hi,
>> Does anybody know how to get the following information for a particular
>> database in SQL 2000
>> 1. Whether it is a System Object or not.
>> 2. Create for Attach
>> 3. Replication Status
>> We can get this values through SQL-DMO, but can I get these values from
>> some system tables or in-built functions
>> TIA
>> Prasad
>>
>
>|||For an alternative look at the sysdatabases table. The sid column stores the
System ID of the database creator - its the same "fake" value for each system
database created at install.
I'd hard-code the names, though.
ML
--
http://milambda.blogspot.com/|||Thanks
But its not fake sid its the sid for "sa" which means suppose if a new
database is created by "sa" it would also have the same sid.
Thanks
Prasad
"ML" <ML@.discussions.microsoft.com> wrote in message
news:01649392-978B-4C06-889F-8EE342A5C7E1@.microsoft.com...
> For an alternative look at the sysdatabases table. The sid column stores
> the
> System ID of the database creator - its the same "fake" value for each
> system
> database created at install.
> I'd hard-code the names, though.
>
> ML
> --
> http://milambda.blogspot.com/|||You're right. Sorry. What was I thinking...?
ML
--
http://milambda.blogspot.com/|||Hi, Prasad
> Isn't there a more cleaner way of finding the system databases,
> comparing the names would mean hard-coding the stuff.
I don't know any other way; AFAIK, Enterprise Manager and Management
Studio are doing the same thing.
> and regarding the "created for attach" field bcoz SQL-DMO returns this value
The CreateForAttach property in SQL-DMO is used to specify how the
database will be created (before appending the Database object to the
Databases collection).
Razvan|||Thanks Razvan
"Razvan Socol" <rsocol@.gmail.com> wrote in message
news:1140015795.987952.21330@.f14g2000cwb.googlegroups.com...
> Hi, Prasad
>> Isn't there a more cleaner way of finding the system databases,
>> comparing the names would mean hard-coding the stuff.
> I don't know any other way; AFAIK, Enterprise Manager and Management
> Studio are doing the same thing.
>> and regarding the "created for attach" field bcoz SQL-DMO returns this
>> value
> The CreateForAttach property in SQL-DMO is used to specify how the
> database will be created (before appending the Database object to the
> Databases collection).
> Razvan
>

Attributes not visible in Cube when adding with AMO and Hierarchyenabled = false

I need help with a big problem.

I'm using AMO to add new Attributes to a Dimension and I have to use AttributeHierarchyEnabled property = false as Default.

But then I'm never able again to see the Attribute in the Cube though I change with Visiual Studio the property to true.

If use AttributeHierarchyEnabled = true all works fine.

Dimension dim = mDataBase.Dimensions.GetByName(Attr.DimensionName);

DimensionAttribute Attribute = dim.Attributes.FindByName(Attr.AttributeName);

Attribute = new DimensionAttribute();

Attribute.Name = Attr.AttributeName;

Attribute.ID = Attr.AttributeName;

...

Attribute.AttributeHierarchyEnabled = true;

dim.Attributes.Add(Attribute);

Thanks for any help.

You need set the AttributeHierarchyEnabled to true for both DimensionAttribute and CubeAttribute in AMO.

In visual studio, you need change the property both in dimension attribute property dialog and cube attribute dialog.

Attributes in referenced dimension

I am wondering about the correct/best/most efficient way to access attributes contained in a referenced dimension. ie in AdventureWorks: say I had population data for states (or some other level other than the base key/postal code level), and I added this as a column in the Geography dimension (Population attribute/property related to state attribute); I would then like to use this new data in a query against Reseller Sales by the Reseller dimension. Is this the "right"/optimal approach:

with member [Measures].[popu] as linkmember([Reseller].[Geography Key].currentmember, [Geography].[Geography]).parent.parent.properties('Population', TYPED)

select [Reseller].[Reseller].members on 1,

[Measures].[popu] on 0

from [Adventure Works]

While this works, I do wonder about the efficiency, and Mosha's comments regarding the general avoidance of both linkmember and properties has me suspicious that "there must be a better way"....

Thanks!

-f

Efficiency can be a question of how your users would like to see data in dimensions or how fast a query will be.

If you create a view on the Resellers and the Geography dimension you will have autoexist between attributes in these dimensions. That can be an efficient and a better solution than using referenced dimensions. This is from the point of view of how SSAS2005 works.

HTH

Thomas Ivarsson

|||

Thanks, Thomas -- in my particular situation, I'm pretty stuck with the referenced dimension (my real situation is a lot more complex). I do pull many attributes into the logical equivallent of the Reseller dimension in my situation, but can't pull all -- so I was really focused on the question of if this is indeed the right way to cross the referenced boundary and effect the "indirect overwrite" (to coin a phrase) to grab the attribute of interest (on a different level/hierarchy in the referenced dimension just to add to the complexity). I'm a little surprised if there isn't an easier/more efficient way to do this....

On an indirectly related note, this difficulty with referenced dimensions does really bug me, as while they do provide some measure of reuse, it seems that we still need to do a lot of data duplication between dimensions to really make use of the data values for calculations and even browsing, as in my example. While this is not a huge impact in terms of storage (dimensions are small and all that), it seems wrong from a development POV as we have data access/migration spread between ETL and/or AS DSV and/or AS calcs to overcome an implementation issue. I understand that there is a performance impact to keeping things clean, but as attributes in related dimensions are logically really just attributes in other hierarchies in the relating dimension, it would seem to me important to unify the handling of related dimensions with hierarchies within a dimension. (Logically, I would have expected this sort of functionality to be implemented by having related dimensions implemented directly attached to a dimension, in the dimension design area -- to perhaps be materialized as part of the processing of the relating dimension, rather than via the dimension usage area, but perhaps I am missing something fundamental about the implementation of AS.)

Thanks again,

-f

|||

Hello again Sir SliceDice. I am not sure about your business problem but most design approaches in SSAS2005 simply follows the same rules that you have with joins between tables in the relational world.

You have one-to-many, one-to-one and many-to-many.

Referenced dimensions can be subsituted with views, like I suggested before, or by having both a resellers key and and geography key in the fact table.

I suggest you to have a look here ( www.kimballgroup.com ) for guidelines of how to solve your business problem.

To solve business problems when the structure is fixed in a starschema, with MDX or dimension relations, is always harder than changing or rewrite the structure of the data warehouse.

I know that design changes in a data warehouse will effect the report side negatively, but I have no clear idea of why Microsoft have added referenced dimensions to SSAS2005? I have a hard time finding out why I should use them except when something is wrong with the design of my data mart /data warehouse.

Kind Regards

Thomas Ivarsson

|||

Thomas,

Thanks again for your reply. I agree that we can work around the limitations of the current implementation of the UDM via a view, or via recording the additional geography attributes in the fact table or the (Resellers in the case of AdventureWorks) dimension table.

BUT, let me ask: is geography a useful abstraction in its own right? Of course it is! It makes a lot of sense to seperate and isolate the geography information/attributes to their own table; it makes a lot of sense for lots of reasons to use a "snowflake" approach for geography and reseller. But now having the snowflake, we need to incorporate geography hierarchies/levels into the Reseller dimension OR use a referenced dimension. In the case of the Customer dimension, the AdventureWorks demo takes the former approach (Geo in the dimension); in the case of Reseller, it takes the latter approach (referenced dimension).

With the embedded snowflake approach (eg: Customer dimension), it is painful and wasteful to have to repeatedly design all the same hierarchies and attribute relationships, particularly if there is a lot of data related to the flaked dimension (Geography; e.g.: say you had geographical area data, population/census data, government data, etc). Do the users need to browse and/or calculate with all these items in all the related dimensions? Maybe, maybe not (but we can be sure the need will grow in the future) -- and while we can certainly work around the situation (views and the like), it still is a waste of time & effort to repeat the work.

And, in the realm of software development beyond the realm of data warehousing/business intelligence, this sort of repeating of effort and data is a non-starter. Sure, some exceptions from 3NF are reasonable and well justifiable, even if only to improve performance (especially with the strict gatekeeping administration of a strong ETL system), but wholesale duplication of dimension definitions/embedding sounds like a recipe for a lot of waste and problems with future change, as well as problems from unexpected behavior ("but I did it like that with the customer data, why doesn't it work right for the resellers -- they're in the same state!").... The problem of future change is especially significant: in my experience, it is particularly easy to break existing reports via changes to cube structure, making for very unhappy users (and the worst is when you break your own reports!): thus, solid upfront architecture is especially important.

Of course, to some extent, this is exactly the problem: these BI systems have originated in one world oriented to reporting and analysis, when my application, while still being described in those terms, is in reality quite a bit outside of the typical BI realm: I have pulled quite a bit of business logic into cube space, and have had great sucess with cubes as an effective general compute platform. After all, ALGORITHM+DATA STRUCTURE = PROGRAM, and in cubes we have a general purpose data structure (can model any data structure desired, and can do so very efficiently for exceptionally large structures), and we have a very descriptive algorithm implementation language (that allows effective blending of both declarative and procedural code efficiently over large data sets). The UDM was a huge step in this direction; with a few more improvements the AS05/UDM environment could be an ideal general compute platform....

But all that aside, back to my original question: is this the preferred approach to cross related dimension boundaries and grab properties?

linkmember([Reseller].[Geography Key].currentmember, [Geography].[Geography]).parent.parent.properties('Population', TYPED)

-f

|||

I don't know the complexities involved in why you chose a reference dimension, but I agree with Thomas that you can combine those two tables using a view and put all attributes into one dimension. Then you get auto-exists which should make it easier to efficiently get the population # you need.

It sounds like you're not going to go that direction, so optimizing the MDX is something to look into. That exact MDX you had above returned nulls for me because I don't believe there's a Population member property in the Geography dimension. (But it illustrated what you were doing, so it was fine.) I rewrote it as follows to test performance:

with member [Measures].[popu] as

Exists([Geography].[Geography].[Country].Members, linkmember([Reseller].[Geography Key].currentmember, [Geography].[Geography Key])).Item(0).Name

select Exists([Date].[Month Name].Members*[Reseller].[Reseller].[Reseller].members,,"Reseller Sales") on 1,

[Measures].[popu] on 0

from [Adventure Works]

In terms of performance, the following query outperforms it and returns just the same results I think. It's using Exists with a measure group to find the right combinations of Resellers and Geographies. You might consider trying this as I suspect it will perform better:

select Exists([Reseller].[Reseller].[Reseller].members*[Geography].[Geography].[Country].Members,,"Reseller Sales") on 1,

{} on 0

from [Adventure Works]

attributes and name column property

I'd like to verify my logic on using the name column.

In my dimension i have a two tables

The main table contains the majority of the fields that will create the attributes for the

dimension some of these fields are code values where the description is contained

in a second table.

So here is my question/logic

Instead of adding the Description field as an attribute from the second table,

I should take the code value from the main table as the attribute and then set the

"name Column" property to reference the description from the second table.

The way you describe is definitely better than defining 2 attributes. Having more attributes is less efficient. You will need to define more aggregations to get a better performance.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

Attribute/Tags style search

Hi,

I am trying to write a query that will take a table with arbitrary key-value pairs and perform an inclusive search (ie, everything must match) on a second table with an arbitrary set of key-value search criteria pairs. The first table can get rather large, the search table will remain very small (<20 items).

Any help would be appreciated.

Thanks,

Mike

--
-- defines a source table that holds part attributes. the attribute names are
-- completely arbitrary
declare @.source table (partId int, attrKey varchar(max), attrVal varchar(max))
insert into @.source (partId, attrKey, attrVal)
(
select 1, 'STOCKNUM', '12345'
union
select 1, 'STOCKED', 'Y'
union
select 1, 'WEIGHT', '12'
union
select 2, 'STOCKNUM', '34567'
union
select 2, 'STOCKED', 'Y'
union
select 2, 'WEIGHT', '12'
union
select 3, 'STOCKNUM', '67890'
union
select 3, 'STOCKED', 'Y'
union
select 3, 'WEIGHT', '14'
)

--
-- define some inclusive search criteria to locate the partIds against. Every attribute
-- in the search table must match the record in the source table. ie, find all part ids that have
-- WEIGHT=12 and STOCKED=Y
declare @.search table (attrKey varchar(max), attrVal varchar(max))
insert into @.search (attrKey, attrVal)
(
select 'WEIGHT', '12'
union
select 'STOCKED', 'Y'
-- this could continue with more search values
)

--
-- would like this to return only part ids that matches all the criteria (in this case 1 & 2)
select a.partid from
@.source a join @.search s on a.attrKey=s.attrKey
where a.attrVal = s.attrVal

I took a stab at it... I do not claim this is the best way to do this, but you have an inherent problem, which is multiple rows in your "search" table that end up matching all of the rows in the "source" table. So, with that said, you need to take the first search condition and filter the source, save the results, and then filter those results with the next search condition and so on.

I'm sure performance would suck with my approach, not to mention memory consumption if you have a very large source table.

create table #temp (
partId int
)
create table #temp2 (
partid int
)
declare @.myKey varchar(max), @.myVal varchar(max)
declare c cursor
for select * from @.search
open c
fetch c into @.myKey, @.myVal

insert into #temp select distinct partid from @.source

while (@.@.FETCH_STATUS=0) BEGIN
truncate table #temp2
insert into #temp2 select * from #temp
truncate table #temp
insert into #temp select distinct a.partid from #temp2 a, @.source b where a.partid = b.partid and b.attrkey = @.myKey and b.attrVal = @.myVal
fetch c into @.myKey, @.myVal
end
close c
deallocate c
select distinct partid from #temp
|||

Thanks for the try. Performance would be a major concern with that approach though.

After putting more thought into it, I came up with this simple solution:

select a.partId
from @.source a join @.search s on a.attrKey=s.attrKey
where a.attrVal = s.attrVal
group by partId
having count(a.partId) = (select count(attrKey) from @.search)

Now for the perf testing over the actual db

Thanks again,

Mike

|||This is the generic form of query for this problem known as relational division. This will work fine.|||Thank you.

Saturday, February 25, 2012

Audit in SQL Server

audit in SQL 2005

Audit history for scheduled jobs (SQL Server 2000)

Audit Failures whenever I start the SQL Enterprise Manager

Audit failed logins

Audit Drop table

Audit Design Question

Audit Delete Statements

audit database user creation

audit database user creation

audit database user creation

Audit Data

Audit and Error Log Tables

Audit and Error Log Tables

Audit All SQL Queries against all or specific databases

AUDIT all commands performed by Privileged Users on SQL 2000/2005

Audit Add Windows Login?

Audit a Table

Audit a SQl connection

Audit 3 fields in one table

audit

Audit

Audit

Auction Best Practice

ATX Caribou, ME

Attributes, Properties; Dimensions, Levels, and Hierarchies, oh my!

Attributes of Database

Attributes of Database

Attributes of Database

Attributes of Database

Attributes not visible in Cube when adding with AMO and Hierarchyenabled = false

Attributes in referenced dimension

attributes and name column property

Attribute/Tags style search

Attach remote DB file

Blog Archive

About Me