Auto created statistics and missing statistics

Hello group.

I have an issue, which has bothered me for a while now:

I'm wondering why the column statistics, which SQL Server wants me to
create, if I turn off auto-created statistics, are so important to the
optimizer?

Example: from Northwind (with auto create stats off), I do the following:

SELECT * FROM Customers WHERE Country = 'Sweden'

My query plan show a clustered index scan, which is expected - no index
exists for Country. BUT, the query plan also shows, that the optimizer is
missing a statistic on Country, which tells me, that the optimizer would
benefit from knowing this.

I cannot see why? (and I've been trying for a while now).

If I create the missing statistics, nothing happens in the query plan (and
why should it?). I could understand it, if the optimizer suggested an index
on Country - this would make sense, but if creating the missing index, query
analyzer creates the statistics with an empty index, which seems to me to be
less than usable.

I've been thinking long and hard about this, but haven't been able to reach
a conclusion :) It has some relevance to my work, because allowing the
optimizer to create missing statistics limits my options for designing
indexes (e.g. covering) for some rather wide tables, so I'm thinking why not
turn it off altogether. But I would like to know the consequences - hope
somebody has already delved into this, and knows a good explanation.

Rgds
Jesper"Jesper Jensen" <moellemand@.post.tdcadsl.dk> wrote in message
news:40727f2e$0$237$edfadb0f@.dread12.news.tele.dk. ..
> Hello group.
> I have an issue, which has bothered me for a while now:
> I'm wondering why the column statistics, which SQL Server wants me to
> create, if I turn off auto-created statistics, are so important to the
> optimizer?
> Example: from Northwind (with auto create stats off), I do the following:
> SELECT * FROM Customers WHERE Country = 'Sweden'
> My query plan show a clustered index scan, which is expected - no index
> exists for Country. BUT, the query plan also shows, that the optimizer is
> missing a statistic on Country, which tells me, that the optimizer would
> benefit from knowing this.
> I cannot see why? (and I've been trying for a while now).
> If I create the missing statistics, nothing happens in the query plan (and
> why should it?). I could understand it, if the optimizer suggested an
index
> on Country - this would make sense, but if creating the missing index,
query
> analyzer creates the statistics with an empty index, which seems to me to
be
> less than usable.
> I've been thinking long and hard about this, but haven't been able to
reach
> a conclusion :) It has some relevance to my work, because allowing the
> optimizer to create missing statistics limits my options for designing
> indexes (e.g. covering) for some rather wide tables, so I'm thinking why
not
> turn it off altogether. But I would like to know the consequences - hope
> somebody has already delved into this, and knows a good explanation.
> Rgds
> Jesper

http://msdn.microsoft.com/library/d...l/statquery.asp

Simon|||Thanks, Simon, informative article, but ...

... it doesn't really explain the stuff, that I wrote. The closest I get to
an explanation, when reading this is 'These statistics are created for
columns where the optimizer would have to estimate the approximate density
or distribution otherwise'.

I knew this, but I still do not know, why the optimizer needs to know the
density and/or distribution?? I can see no valid reason, and therefore I can
see no good reason for enabling auto-creation of stats.

What I probably looking for is a good example, where the use of an
automatically created stat saves time, cycles and IOs :)

Best Rgds - Jesper

"Simon Hayes" <sql@.hayes.ch> skrev i en meddelelse
news:4072f05a$1_2@.news.bluewin.ch...
> "Jesper Jensen" <moellemand@.post.tdcadsl.dk> wrote in message
> news:40727f2e$0$237$edfadb0f@.dread12.news.tele.dk. ..
> > Hello group.
> > I have an issue, which has bothered me for a while now:
> > I'm wondering why the column statistics, which SQL Server wants me to
> > create, if I turn off auto-created statistics, are so important to the
> > optimizer?
> > Example: from Northwind (with auto create stats off), I do the
following:
> > SELECT * FROM Customers WHERE Country = 'Sweden'
> > My query plan show a clustered index scan, which is expected - no index
> > exists for Country. BUT, the query plan also shows, that the optimizer
is
> > missing a statistic on Country, which tells me, that the optimizer would
> > benefit from knowing this.
> > I cannot see why? (and I've been trying for a while now).
> > If I create the missing statistics, nothing happens in the query plan
(and
> > why should it?). I could understand it, if the optimizer suggested an
> index
> > on Country - this would make sense, but if creating the missing index,
> query
> > analyzer creates the statistics with an empty index, which seems to me
to
> be
> > less than usable.
> > I've been thinking long and hard about this, but haven't been able to
> reach
> > a conclusion :) It has some relevance to my work, because allowing the
> > optimizer to create missing statistics limits my options for designing
> > indexes (e.g. covering) for some rather wide tables, so I'm thinking why
> not
> > turn it off altogether. But I would like to know the consequences - hope
> > somebody has already delved into this, and knows a good explanation.
> > Rgds
> > Jesper
>
http://msdn.microsoft.com/library/d...l/statquery.asp
> Simon|||"Jesper Jensen" <moellemand@.post.tdcadsl.dk> wrote in message
news:4072f91b$0$300$edfadb0f@.dread12.news.tele.dk. ..
> Thanks, Simon, informative article, but ...
> ... it doesn't really explain the stuff, that I wrote. The closest I get
to
> an explanation, when reading this is 'These statistics are created for
> columns where the optimizer would have to estimate the approximate density
> or distribution otherwise'.
> I knew this, but I still do not know, why the optimizer needs to know the
> density and/or distribution?? I can see no valid reason, and therefore I
can
> see no good reason for enabling auto-creation of stats.
> What I probably looking for is a good example, where the use of an
> automatically created stat saves time, cycles and IOs :)
> Best Rgds - Jesper

OK, here's another informative article :-)

http://www.winnetmag.com/SQLServer/...2075/22075.html

In summary, index statistics exist only for the first column in an index,
but auto-created (or manually created) statistics can exist for any column.
This gives the optimizer extra information, which might mean it chooses a
different, more efficient index for a query.

Check out the example on the second page of the article - on my system, this
reduced the logical reads required for the query from 104 to 43.

But you're correct to consider that there can be an impact on performance in
some situations:

http://support.microsoft.com/defaul...kb;en-us;195565

Simon|||Thanks, Simon, that one did the trick.

One less mystery.

On my machine, QA tells me that the two queries (the index scan on
ProductID/Quantity vs. the clustered index scan) takes 43.65 and 56.35% cost
respectively. I would argue, that this saving is not worth the 'used up
index space'. In my professional life, I've seen tables, which are wide
enough (200+ columns) to demand, that precious index space is saved.

Basically, I think there are too many 'ifs' before an auto-created index
saves performance, but I appreciate the optimization idea behind it.

Thanks - Jesper

"Simon Hayes" <sql@.hayes.ch> skrev i en meddelelse
news:4073121e_1@.news.bluewin.ch...
> "Jesper Jensen" <moellemand@.post.tdcadsl.dk> wrote in message
> news:4072f91b$0$300$edfadb0f@.dread12.news.tele.dk. ..
> > Thanks, Simon, informative article, but ...
> > ... it doesn't really explain the stuff, that I wrote. The closest I get
> to
> > an explanation, when reading this is 'These statistics are created for
> > columns where the optimizer would have to estimate the approximate
density
> > or distribution otherwise'.
> > I knew this, but I still do not know, why the optimizer needs to know
the
> > density and/or distribution?? I can see no valid reason, and therefore I
> can
> > see no good reason for enabling auto-creation of stats.
> > What I probably looking for is a good example, where the use of an
> > automatically created stat saves time, cycles and IOs :)
> > Best Rgds - Jesper
> OK, here's another informative article :-)
> http://www.winnetmag.com/SQLServer/...2075/22075.html
> In summary, index statistics exist only for the first column in an index,
> but auto-created (or manually created) statistics can exist for any
column.
> This gives the optimizer extra information, which might mean it chooses a
> different, more efficient index for a query.
> Check out the example on the second page of the article - on my system,
this
> reduced the logical reads required for the query from 104 to 43.
> But you're correct to consider that there can be an impact on performance
in
> some situations:
> http://support.microsoft.com/defaul...kb;en-us;195565
> Simon|||"Jesper Jensen" <moellemand@.post.tdcadsl.dk> wrote in message news:<40732877$0$274$edfadb0f@.dread12.news.tele.dk>...
> Thanks, Simon, that one did the trick.
> One less mystery.
> On my machine, QA tells me that the two queries (the index scan on
> ProductID/Quantity vs. the clustered index scan) takes 43.65 and 56.35% cost
> respectively. I would argue, that this saving is not worth the 'used up
> index space'. In my professional life, I've seen tables, which are wide
> enough (200+ columns) to demand, that precious index space is saved.
> Basically, I think there are too many 'ifs' before an auto-created index
> saves performance, but I appreciate the optimization idea behind it.
> Thanks - Jesper

<snip
Well, you have to be careful about reaching conclusions based on
simple queries using small data sets. It's possible that a complex
join involving millions of rows would give a more significant
difference. To get a definite answer for your environment, you would
have to do some benchmarking, with and without statistics.

Simon

Authentification problem with SQL 2k, IIS and Linked Server

Hello,

I have successfully created an Exchange 2000 linked server in Microsoft SQL server 2k. When I use my NT login, I can query it.

I'm now developping a website (ASP.NET in IIS 5). I have successfully created a web page querying my database. But now, I would like to query my linked server via my SQL server. I have try various authentification settings but I always got this error : OLE DB provider 'Exoledb.DataSource.1' reported an error. Authentication failed.

I know it is because the IIS process don't have any rights on the Exchange server but I would like to know what is the way to get around this without given rights to the IIS process (and creating huge security holes).

Thank a lot

Felix Pageau
fpageau@.str.cadid you try distributed queries ?

for example :
INSERT INTO [Database1name].[Owner].[Table]
SELECT *
FROM OPENQUERY ([LinkedServerName], SELECT Database2name.Table.* FROM Database2Name.Table)|||Hello,

this is exactly what I'm trying to do... I would like to know if it is possible to "impersonate" an Exchange Linked Server (with a user defined in the domain...) ?

Thank|||Review information from this KBA (http://support.microsoft.com/default.aspx?scid=kb;EN-US;285833) to go with Linked server authentication.|||Hello,
this is working fine with access but Exchange is using Active Directory login... Those example use plain text login & password. I have already try this way.

Thank a lot

Felix Pageau

Authentication Issues

Current Setup:
Windows 2003 Server
SQL Server 2000 w/ SP3
Windows Sharepoint Servics

Problem:
I have created a group on our Domain (INT) called Domain Users. Inside this group I have added individual users that need to be there.

On the SQL Server when I try to add INT\Domain Users I get an error stateing that the user does not exist. Next I tried typing in INT and selecting the browse button. The window opens up listing all Domain users and groups including the one I added 'Domain Users'. I select Domain users from the Drop down list and hit OK. I then hit OK at the bottom of the Add User window and get the error User does not exist.

Any help or insight would be greatly appreciated.

Thank You
Tom McClung

Can you execute the following statement in Query Analyzer and post the output here:

sp_grantlogin 'INT\Domain Users'

Please post both the line containing the error number and error state, and the line containing the error message.

Thanks
Laurentiu

|||Server: Msg 15401, Level 11, State 1, Procedure sp_grantlogin, Line 41
Windows NT User or Group 'INT\Domain Users' not found. Check the name again.

Tom
|||Just a bit more information incase it's needed.

This server is not the domain controller. The domain controller is running Windows 2k Server software.

Tom
|||

Thanks for the information.

I have two additional questions:

(1) Are you able to add any INT user as a SQL login, or do you hit the same error for any INT principal that you attempt to add with sp_grantlogin.

(2) What was the tool that you were using to browse the domain users, which you mentioned in the initial email?

Not sure if it is related, but have you considered upgrading to SP4?

Thanks
Laurentiu

|||(1) The only one that was added was INT\Administrator. Every other INT account delivers the same error using sp_grantlogin.

(2) In the SQL server enterprise manager I went to the security folder and then clicked on logins. Then groups. In the group window at the top I typed in INT and clicked the browse button next to it. This brought up a drop down menu of all the INT users and groups correctly.

(3) I will download SP4 and test.

Tom
|||SP4 installed and I still have the same authentication issues.

I can see the INT domain users in the SQL Server Login menu but when I attempt to add the error comes up with user does not exist.

Tom
|||

What is the service account that SQL Server is running under? One possibility might be that the service account cannot query the INT domain. Did you add the Administrator account manually?

You could also attempt to install SQL Server Express and perform the same operation (as a precaution, you should backup your existing databases to avoid any loss). The error messages in SQL Server 2005 provide additional information that could help identify the issue. Make sure you set SQL Server Express to run under the same account as the existing SQL Server 2000 installation.

Thanks
Laurentiu

|||This is likely to be unrelated to SQL Server; check DNS is properly configured otherwise comms with DCs will be problematic - use ping, nslookup and arp commands to verify this.

Also, adding domain groups into local groups on the server, through compmgmt.msc > Local Users and Groups will deterimne if everythings okay at the OS level.

If that is working, it could be that the SQL Service is lacking in user rights, or the account you are interactively logged in with is either local or otherwise unable to enumerate accounts on the domain.|||Make sure u got checked mix authentication mode on ur server.|||

I am having exactly the same problem.

Setup:

SQL Server 2000 Standard Edition SP4; service login account is a domain account that is a member of domain admins (it was not for normal operation; I added it to domain admins to see if login creation would work - it did not)

Windows 2003 Server Standard Edition, SP1 and all subsequent updates installed; it is an AD domain controller in a single-domain forest.

I am logging onto the DC with a domain admin username/password

DCs are replicating fine; I created a test group on one DC and saw it immediately on other DC. For this issue, tried creating a new group on first one DC, then the other (non-SQL Server) one.

As with the initial post, I can see all groups and users - incl. my newly created group - in the security pulldown, but selecting my group fails exactly as initial post specifies. I.e. I am not typing anything (so no typos), just picking from pre-populated lists.

Mixed authentication is enabled. Tried rebooting after creating AD group; still failed. Tried adding SQL Server to AD; still failed. Error message 15401 is unhelpful, nor does MS site have any further helpful info.

Infrastructure works fine; this is a small LAN, everything resolves etc.

The SQL Server has been operational for a month or so. *Nothing* else has been installed on this DC.

We do have some group policy settings in place; very minimal though. Does anyone know if anything in routine group policy could possibly prevent a domain admin logged into Windows 2003 from adding a login for a domain group when the SQL Server service is running under a (separate) domain admin account?

Any help is appreciated. Thanks.

UPDATE: I was able to add my domain group to the BUILTIN Administrators group using AD Users and Computers. The same domain group cannot be added in SQL Server as described above.

Mulhall wrote:

This is likely to be unrelated to SQL Server; check DNS is properly configured otherwise comms with DCs will be problematic - use ping, nslookup and arp commands to verify this.
Also, adding domain groups into local groups on the server, through compmgmt.msc > Local Users and Groups will deterimne if everythings okay at the OS level.
If that is working, it could be that the SQL Service is lacking in user rights, or the account you are interactively logged in with is either local or otherwise unable to enumerate accounts on the domain.

|||

The authentication mode does not matter for the operation of creating a login.

Just to make sure I understand this setup: is SQL Server 2000 installed on the DC machine, or on a different machine?

Thanks
Laurentiu

|||

OK, I figured out how to at least fix the symptom temporarily. Maybe someone more expert than me at AD can come up with a fundamental explanation based on what I did.

First, this is NOT a SQL Server issue. It is an Active Directory replication issue.

Initially, I noticed event log entries on my SQL-hosting DC (which was not a GC server - yet) that the Net Logon service was paused due to replication problems. So I started it and it started, but after reboot it went back to paused.

I decided to use the Windows 2003 replmon.exe support tool to check into my DCs' replication status. Indeed, the DC hosting SQL Server showed broken replication from the PDC/GC DC.

Long story short, I made the DC hosting SQL a GC server also. Then, I opened AD Sites & Services on both DCs and deleted the automatically generated NTDS connections, then added my own manually. Left all settings at default (except of course which server was connected).

Then I used replmon.exe to "Synchronize each directory partition with all servers". Invoked this from both my DCs.

This seemed to do the trick. I could now add domain groups in SQL Server. Replmon.exe showed no more red x glyphs.

I had earlier tried replmon.exe and selecting "replicate now" for DC connections in Sites & Services leaving the automatically-generated connections in place. That was spotty, and while replmon.exe showed success a couple of times (no red x glyphs), shortly thereafter the red x glyphs reappeared and Users & Computers changes were no longer propagating.

That's when I created manual replication connections in Sites & Services. Crossing my fingers at this point... we'll see how it goes.

One final piece of info. My second DC - the one hosting SQL Server - is not a 24/7 machine. It is down (on purpose) quite a bit. Generally it is on every day for several hours, and it may or may not be on on weekends.

So, hopefully an AD wizard out there will see this and have a helpful epiphany.

BTW I can't resist one bit of carping. Why on Earth is a vital system tool like replmon.exe NOT in the default Windows 2003 install - meaning I have to go find the CD, then the support tools dir, then decide which of the msi and exe files to run, when unneeded end-user stuff like Windows Media Player, DirectX, and so on are on a default server install?

pelazem wrote:

I am having exactly the same problem.

Setup:

SQL Server 2000 Standard Edition SP4; service login account is a domain account that is a member of domain admins (it was not for normal operation; I added it to domain admins to see if login creation would work - it did not)

Windows 2003 Server Standard Edition, SP1 and all subsequent updates installed; it is an AD domain controller in a single-domain forest.

I am logging onto the DC with a domain admin username/password

DCs are replicating fine; I created a test group on one DC and saw it immediately on other DC. For this issue, tried creating a new group on first one DC, then the other (non-SQL Server) one.

As with the initial post, I can see all groups and users - incl. my newly created group - in the security pulldown, but selecting my group fails exactly as initial post specifies. I.e. I am not typing anything (so no typos), just picking from pre-populated lists.

Mixed authentication is enabled. Tried rebooting after creating AD group; still failed. Tried adding SQL Server to AD; still failed. Error message 15401 is unhelpful, nor does MS site have any further helpful info.

Infrastructure works fine; this is a small LAN, everything resolves etc.

The SQL Server has been operational for a month or so. *Nothing* else has been installed on this DC.

We do have some group policy settings in place; very minimal though. Does anyone know if anything in routine group policy could possibly prevent a domain admin logged into Windows 2003 from adding a login for a domain group when the SQL Server service is running under a (separate) domain admin account?

Any help is appreciated. Thanks.

UPDATE: I was able to add my domain group to the BUILTIN Administrators group using AD Users and Computers. The same domain group cannot be added in SQL Server as described above.

Mulhall wrote:

This is likely to be unrelated to SQL Server; check DNS is properly configured otherwise comms with DCs will be problematic - use ping, nslookup and arp commands to verify this.

Also, adding domain groups into local groups on the server, through compmgmt.msc > Local Users and Groups will deterimne if everythings okay at the OS level.

If that is working, it could be that the SQL Service is lacking in user rights, or the account you are interactively logged in with is either local or otherwise unable to enumerate accounts on the domain.

Authentication Issues

Current Setup:
Windows 2003 Server
SQL Server 2000 w/ SP3
Windows Sharepoint Servics

Problem:
I have created a group on our Domain (INT) called Domain Users. Inside this group I have added individual users that need to be there.

On the SQL Server when I try to add INT\Domain Users I get an error stateing that the user does not exist. Next I tried typing in INT and selecting the browse button. The window opens up listing all Domain users and groups including the one I added 'Domain Users'. I select Domain users from the Drop down list and hit OK. I then hit OK at the bottom of the Add User window and get the error User does not exist.

Any help or insight would be greatly appreciated.

Thank You
Tom McClung

Can you execute the following statement in Query Analyzer and post the output here:

sp_grantlogin 'INT\Domain Users'

Please post both the line containing the error number and error state, and the line containing the error message.

Thanks
Laurentiu

|||Server: Msg 15401, Level 11, State 1, Procedure sp_grantlogin, Line 41
Windows NT User or Group 'INT\Domain Users' not found. Check the name again.

Tom
|||Just a bit more information incase it's needed.

This server is not the domain controller. The domain controller is running Windows 2k Server software.

Tom
|||

Thanks for the information.

I have two additional questions:

(1) Are you able to add any INT user as a SQL login, or do you hit the same error for any INT principal that you attempt to add with sp_grantlogin.

(2) What was the tool that you were using to browse the domain users, which you mentioned in the initial email?

Not sure if it is related, but have you considered upgrading to SP4?

Thanks
Laurentiu

|||(1) The only one that was added was INT\Administrator. Every other INT account delivers the same error using sp_grantlogin.

(2) In the SQL server enterprise manager I went to the security folder and then clicked on logins. Then groups. In the group window at the top I typed in INT and clicked the browse button next to it. This brought up a drop down menu of all the INT users and groups correctly.

(3) I will download SP4 and test.

Tom
|||SP4 installed and I still have the same authentication issues.

I can see the INT domain users in the SQL Server Login menu but when I attempt to add the error comes up with user does not exist.

Tom
|||

What is the service account that SQL Server is running under? One possibility might be that the service account cannot query the INT domain. Did you add the Administrator account manually?

You could also attempt to install SQL Server Express and perform the same operation (as a precaution, you should backup your existing databases to avoid any loss). The error messages in SQL Server 2005 provide additional information that could help identify the issue. Make sure you set SQL Server Express to run under the same account as the existing SQL Server 2000 installation.

Thanks
Laurentiu

|||This is likely to be unrelated to SQL Server; check DNS is properly configured otherwise comms with DCs will be problematic - use ping, nslookup and arp commands to verify this.

Also, adding domain groups into local groups on the server, through compmgmt.msc > Local Users and Groups will deterimne if everythings okay at the OS level.

If that is working, it could be that the SQL Service is lacking in user rights, or the account you are interactively logged in with is either local or otherwise unable to enumerate accounts on the domain.|||Make sure u got checked mix authentication mode on ur server.|||

I am having exactly the same problem.

Setup:

SQL Server 2000 Standard Edition SP4; service login account is a domain account that is a member of domain admins (it was not for normal operation; I added it to domain admins to see if login creation would work - it did not)

Windows 2003 Server Standard Edition, SP1 and all subsequent updates installed; it is an AD domain controller in a single-domain forest.

I am logging onto the DC with a domain admin username/password

DCs are replicating fine; I created a test group on one DC and saw it immediately on other DC. For this issue, tried creating a new group on first one DC, then the other (non-SQL Server) one.

As with the initial post, I can see all groups and users - incl. my newly created group - in the security pulldown, but selecting my group fails exactly as initial post specifies. I.e. I am not typing anything (so no typos), just picking from pre-populated lists.

Mixed authentication is enabled. Tried rebooting after creating AD group; still failed. Tried adding SQL Server to AD; still failed. Error message 15401 is unhelpful, nor does MS site have any further helpful info.

Infrastructure works fine; this is a small LAN, everything resolves etc.

The SQL Server has been operational for a month or so. *Nothing* else has been installed on this DC.

We do have some group policy settings in place; very minimal though. Does anyone know if anything in routine group policy could possibly prevent a domain admin logged into Windows 2003 from adding a login for a domain group when the SQL Server service is running under a (separate) domain admin account?

Any help is appreciated. Thanks.

UPDATE: I was able to add my domain group to the BUILTIN Administrators group using AD Users and Computers. The same domain group cannot be added in SQL Server as described above.

Mulhall wrote:

This is likely to be unrelated to SQL Server; check DNS is properly configured otherwise comms with DCs will be problematic - use ping, nslookup and arp commands to verify this.
Also, adding domain groups into local groups on the server, through compmgmt.msc > Local Users and Groups will deterimne if everythings okay at the OS level.
If that is working, it could be that the SQL Service is lacking in user rights, or the account you are interactively logged in with is either local or otherwise unable to enumerate accounts on the domain.

|||

The authentication mode does not matter for the operation of creating a login.

Just to make sure I understand this setup: is SQL Server 2000 installed on the DC machine, or on a different machine?

Thanks
Laurentiu

|||

OK, I figured out how to at least fix the symptom temporarily. Maybe someone more expert than me at AD can come up with a fundamental explanation based on what I did.

First, this is NOT a SQL Server issue. It is an Active Directory replication issue.

Initially, I noticed event log entries on my SQL-hosting DC (which was not a GC server - yet) that the Net Logon service was paused due to replication problems. So I started it and it started, but after reboot it went back to paused.

I decided to use the Windows 2003 replmon.exe support tool to check into my DCs' replication status. Indeed, the DC hosting SQL Server showed broken replication from the PDC/GC DC.

Long story short, I made the DC hosting SQL a GC server also. Then, I opened AD Sites & Services on both DCs and deleted the automatically generated NTDS connections, then added my own manually. Left all settings at default (except of course which server was connected).

Then I used replmon.exe to "Synchronize each directory partition with all servers". Invoked this from both my DCs.

This seemed to do the trick. I could now add domain groups in SQL Server. Replmon.exe showed no more red x glyphs.

I had earlier tried replmon.exe and selecting "replicate now" for DC connections in Sites & Services leaving the automatically-generated connections in place. That was spotty, and while replmon.exe showed success a couple of times (no red x glyphs), shortly thereafter the red x glyphs reappeared and Users & Computers changes were no longer propagating.

That's when I created manual replication connections in Sites & Services. Crossing my fingers at this point... we'll see how it goes.

One final piece of info. My second DC - the one hosting SQL Server - is not a 24/7 machine. It is down (on purpose) quite a bit. Generally it is on every day for several hours, and it may or may not be on on weekends.

So, hopefully an AD wizard out there will see this and have a helpful epiphany.

BTW I can't resist one bit of carping. Why on Earth is a vital system tool like replmon.exe NOT in the default Windows 2003 install - meaning I have to go find the CD, then the support tools dir, then decide which of the msi and exe files to run, when unneeded end-user stuff like Windows Media Player, DirectX, and so on are on a default server install?

pelazem wrote:

I am having exactly the same problem.

Setup:

SQL Server 2000 Standard Edition SP4; service login account is a domain account that is a member of domain admins (it was not for normal operation; I added it to domain admins to see if login creation would work - it did not)

Windows 2003 Server Standard Edition, SP1 and all subsequent updates installed; it is an AD domain controller in a single-domain forest.

I am logging onto the DC with a domain admin username/password

DCs are replicating fine; I created a test group on one DC and saw it immediately on other DC. For this issue, tried creating a new group on first one DC, then the other (non-SQL Server) one.

As with the initial post, I can see all groups and users - incl. my newly created group - in the security pulldown, but selecting my group fails exactly as initial post specifies. I.e. I am not typing anything (so no typos), just picking from pre-populated lists.

Mixed authentication is enabled. Tried rebooting after creating AD group; still failed. Tried adding SQL Server to AD; still failed. Error message 15401 is unhelpful, nor does MS site have any further helpful info.

Infrastructure works fine; this is a small LAN, everything resolves etc.

The SQL Server has been operational for a month or so. *Nothing* else has been installed on this DC.

We do have some group policy settings in place; very minimal though. Does anyone know if anything in routine group policy could possibly prevent a domain admin logged into Windows 2003 from adding a login for a domain group when the SQL Server service is running under a (separate) domain admin account?

Any help is appreciated. Thanks.

UPDATE: I was able to add my domain group to the BUILTIN Administrators group using AD Users and Computers. The same domain group cannot be added in SQL Server as described above.

Mulhall wrote:

This is likely to be unrelated to SQL Server; check DNS is properly configured otherwise comms with DCs will be problematic - use ping, nslookup and arp commands to verify this.

Also, adding domain groups into local groups on the server, through compmgmt.msc > Local Users and Groups will deterimne if everythings okay at the OS level.

If that is working, it could be that the SQL Service is lacking in user rights, or the account you are interactively logged in with is either local or otherwise unable to enumerate accounts on the domain.

sql

Authentication Error with HTTP EndPoints

I keep getting these errors when I try to access an
endpoint I created. I have double checked and it is setup
for integrated authentication, grant connect has been
done on the account I am trying to access as and I still
get this error.
Any ideas or suggestions are welcome and appreciated.
Walt
Event Type:Error
Event Source:MSSQLSERVER
Event Category:Logon
Event ID:26026
Date:6/4/2005
Time:7:36:37 PM
User:N/A
Computer:HWCVS17
Description:
HTTP authentication failed. [CLIENT: X.X.X.X]
For more information, see Help and Support Center at
http://go.microsoft.com/fwlink/events.asp.
Data:
0000: aa 65 00 00 0e 00 00 00 e.....
0008: 08 00 00 00 48 00 57 00 ...H.W.
0010: 43 00 56 00 53 00 31 00 C.V.S.1.
0018: 37 00 00 00 00 00 00 00 7......
Hi Jim,
Could you please post the exact steps you performed to create the HTTP
EndPoints and the URL you used to connect to the SQL Server? This will help
us get a clear picture of the problem.
You may want to refer to the SQLXML chapter and sub-chapters in MSDN for
more information:
http://msdn.microsoft.com/library/en...nch_SQLXML.asp
Sincerely,
William Wang
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
This posting is provided "AS IS" with no warranties, and confers no rights.

Authentication Error with HTTP EndPoints

I keep getting these errors when I try to access an
endpoint I created. I have double checked and it is setup
for integrated authentication, grant connect has been
done on the account I am trying to access as and I still
get this error.
Any ideas or suggestions are welcome and appreciated.
Walt
Event Type: Error
Event Source: MSSQLSERVER
Event Category: Logon
Event ID: 26026
Date: 6/4/2005
Time: 7:36:37 PM
User: N/A
Computer: HWCVS17
Description:
HTTP authentication failed. [CLIENT: X.X.X.X]
For more information, see Help and Support Center at
http://go.microsoft.com/fwlink/events.asp.
Data:
0000: aa 65 00 00 0e 00 00 00 e.....
0008: 08 00 00 00 48 00 57 00 ...H.W.
0010: 43 00 56 00 53 00 31 00 C.V.S.1.
0018: 37 00 00 00 00 00 00 00 7......Hi Jim,
Could you please post the exact steps you performed to create the HTTP
EndPoints and the URL you used to connect to the SQL Server? This will help
us get a clear picture of the problem.
You may want to refer to the SQLXML chapter and sub-chapters in MSDN for
more information:
http://msdn.microsoft.com/library/e...anch_SQLXML.asp
Sincerely,
William Wang
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
This posting is provided "AS IS" with no warranties, and confers no rights.

Authentication

it's still 'windows authentication only'. your sql login is created/valid
but it will not allow you to connect to sqlserver.
-oj
"Alan" <alanpltseNOSPAM@.yahoo.com.au> wrote in message
news:eYzuHORkGHA.3512@.TK2MSFTNGP03.phx.gbl...
>I just wonder my office server setup:
> In EM:
> 1) Right click the instance, select 'Edit SQL Server Registration
> properties...'
> 2) The 'Use Windows authentication' radio button is selected
> 3) Right click to select the instance, select 'Properties'
> 4) Security tab
> 5) 'SQL Server and Windows' authentication radio button is selected
> 6) Then I created a SQL Server login, eg. username is 'Alan', password is
> also 'Alan'
> So is this SQL Server 'Windows only' or 'SQL Server and Windows'
> authentication ?
>
>Its mixed mode authentication enabled.
You have registered server using windows authentication but you can
connect also using sql server login.
Regards
Amish Shah
oj wrote:
[vbcol=seagreen]
> it's still 'windows authentication only'. your sql login is created/valid
> but it will not allow you to connect to sqlserver.
>
> --
> -oj
>
> "Alan" <alanpltseNOSPAM@.yahoo.com.au> wrote in message
> news:eYzuHORkGHA.3512@.TK2MSFTNGP03.phx.gbl...|||I just wonder my office server setup:
In EM:
1) Right click the instance, select 'Edit SQL Server Registration
properties...'
2) The 'Use Windows authentication' radio button is selected
3) Right click to select the instance, select 'Properties'
4) Security tab
5) 'SQL Server and Windows' authentication radio button is selected
6) Then I created a SQL Server login, eg. username is 'Alan', password is
also 'Alan'
So is this SQL Server 'Windows only' or 'SQL Server and Windows'
authentication ?|||it's still 'windows authentication only'. your sql login is created/valid
but it will not allow you to connect to sqlserver.
-oj
"Alan" <alanpltseNOSPAM@.yahoo.com.au> wrote in message
news:eYzuHORkGHA.3512@.TK2MSFTNGP03.phx.gbl...
>I just wonder my office server setup:
> In EM:
> 1) Right click the instance, select 'Edit SQL Server Registration
> properties...'
> 2) The 'Use Windows authentication' radio button is selected
> 3) Right click to select the instance, select 'Properties'
> 4) Security tab
> 5) 'SQL Server and Windows' authentication radio button is selected
> 6) Then I created a SQL Server login, eg. username is 'Alan', password is
> also 'Alan'
> So is this SQL Server 'Windows only' or 'SQL Server and Windows'
> authentication ?
>
>|||Its mixed mode authentication enabled.
You have registered server using windows authentication but you can
connect also using sql server login.
Regards
Amish Shah
oj wrote:
[vbcol=seagreen]
> it's still 'windows authentication only'. your sql login is created/valid
> but it will not allow you to connect to sqlserver.
>
> --
> -oj
>
> "Alan" <alanpltseNOSPAM@.yahoo.com.au> wrote in message
> news:eYzuHORkGHA.3512@.TK2MSFTNGP03.phx.gbl...|||Alan wrote:
> I just wonder my office server setup:
> In EM:
> 1) Right click the instance, select 'Edit SQL Server Registration
> properties...'
> 2) The 'Use Windows authentication' radio button is selected
> 3) Right click to select the instance, select 'Properties'
> 4) Security tab
> 5) 'SQL Server and Windows' authentication radio button is selected
> 6) Then I created a SQL Server login, eg. username is 'Alan', password is
> also 'Alan'
> So is this SQL Server 'Windows only' or 'SQL Server and Windows'
> authentication ?
>
>
The authentication mode specified in the Registration properties has
nothing to do with the authentication mode that the server is using, it
only tells Enterprise Manager what sort of authentication method to use
when connecting to the server.
In your case, your SERVER is setup for mixed-mode authentication,
meaning it will accept either Windows credentials, or a valid SQL
login/password combo.
When you connect to it using your current Enterprise Manager
registration, EM is passing your current Windows login credentials to
the server, your SQL login "Alan" isn't being used at all. You can
confirm this by looking at the sysprocesses table after connecting from
EM, you'll see your spid, along with the login name that you're
connected with.|||my bad (should have read your post carefully before answering)...>> 5) 'SQL
Server and Windows' authentication radio button is selected<<
means the server is setup for mixed mode. Under this mode, you can connect
to sqlserver with either windows acct or sql login.
If you have properly created "alan" sql login, you should be able to connect
to sqlserver with that acct. The easiest way to test is to open Query
Analyzer (from Tools menu) and use "alan" as the credential.
-oj
"oj" <nospam_ojngo@.home.com> wrote in message
news:u$4pxhRkGHA.1204@.TK2MSFTNGP02.phx.gbl...
> it's still 'windows authentication only'. your sql login is created/valid
> but it will not allow you to connect to sqlserver.
>
> --
> -oj
>
> "Alan" <alanpltseNOSPAM@.yahoo.com.au> wrote in message
> news:eYzuHORkGHA.3512@.TK2MSFTNGP03.phx.gbl...
>|||Alan wrote:
> I just wonder my office server setup:
> In EM:
> 1) Right click the instance, select 'Edit SQL Server Registration
> properties...'
> 2) The 'Use Windows authentication' radio button is selected
> 3) Right click to select the instance, select 'Properties'
> 4) Security tab
> 5) 'SQL Server and Windows' authentication radio button is selected
> 6) Then I created a SQL Server login, eg. username is 'Alan', password is
> also 'Alan'
> So is this SQL Server 'Windows only' or 'SQL Server and Windows'
> authentication ?
>
>
The authentication mode specified in the Registration properties has
nothing to do with the authentication mode that the server is using, it
only tells Enterprise Manager what sort of authentication method to use
when connecting to the server.
In your case, your SERVER is setup for mixed-mode authentication,
meaning it will accept either Windows credentials, or a valid SQL
login/password combo.
When you connect to it using your current Enterprise Manager
registration, EM is passing your current Windows login credentials to
the server, your SQL login "Alan" isn't being used at all. You can
confirm this by looking at the sysprocesses table after connecting from
EM, you'll see your spid, along with the login name that you're
connected with.|||my bad (should have read your post carefully before answering)...>> 5) 'SQL
Server and Windows' authentication radio button is selected<<
means the server is setup for mixed mode. Under this mode, you can connect
to sqlserver with either windows acct or sql login.
If you have properly created "alan" sql login, you should be able to connect
to sqlserver with that acct. The easiest way to test is to open Query
Analyzer (from Tools menu) and use "alan" as the credential.
-oj
"oj" <nospam_ojngo@.home.com> wrote in message
news:u$4pxhRkGHA.1204@.TK2MSFTNGP02.phx.gbl...
> it's still 'windows authentication only'. your sql login is created/valid
> but it will not allow you to connect to sqlserver.
>
> --
> -oj
>
> "Alan" <alanpltseNOSPAM@.yahoo.com.au> wrote in message
> news:eYzuHORkGHA.3512@.TK2MSFTNGP03.phx.gbl...
>|||So in 'registration properties' determines what mode is using to connect
when opening the 'EM' to connection SQL Server ?
Only the 'Properties' determine how the login connect the server ?
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:%23tqJ3YUkGHA.5036@.TK2MSFTNGP04.phx.gbl...
> Alan wrote:
> The authentication mode specified in the Registration properties has
> nothing to do with the authentication mode that the server is using, it
> only tells Enterprise Manager what sort of authentication method to use
> when connecting to the server.
> In your case, your SERVER is setup for mixed-mode authentication, meaning
> it will accept either Windows credentials, or a valid SQL login/password
> combo.
> When you connect to it using your current Enterprise Manager registration,
> EM is passing your current Windows login credentials to the server, your
> SQL login "Alan" isn't being used at all. You can confirm this by looking
> at the sysprocesses table after connecting from EM, you'll see your spid,
> along with the login name that you're connected with.

Auditing user creation

Hi,
Is it possible to find out ‘Who’ created a specific database user in dat
abase?
Thanks
bbHi,
You need to enable the server side trace for the event "Audit Addlogin
event". This schedule needs to run and log into a file all the time. Once a
week you could
load the trace log to a table and look for the info.
Thanks
Hari
SQL Server MVP
"vinu thomas" <vinuthomas@.discussions.microsoft.com> wrote in message
news:649819CB-8A29-4619-A9DE-C2A27E627B7E@.microsoft.com...
> Hi,
> Is it possible to find out 'Who' created a specific database user in
> database?
> Thanks
> bb

Auditing SQL Server users

I am trying to create a sql script that will check the database instance for any new objects that are created in any database on my system. These objects I want to audit are users, tables, databases, stored procs, etc.. I also want the script to email me and write the information to a text log file. Thanks in advance for any help.Not sure if that can be achieved. Wh don't you run an audit trace|||That's what Profiler is for. In fact, if properly scripted, it can gather whatever info you want and store it in a database, so you don't need to keep it in a text file. And your email notification can be limited to just something like "A new entry was added to the AUDIT table for USER_CREATE event." You can create a trigger on that AUDIT table and have either SQLMail or CDO/XPSMTP send you this short notice. And then you can decide whether to react upon it or let it go.

Auditing DDL and schema changes in SQL Server 2000

Rather than reinvent the wheel, I was hoping that there was someone who has
already created a script allowing for the auditing of DDL statements, schema
changes within the database for SQL Server 2000.
I can easily capture the aforementioned in SQL 2005 by making use of a
trigger on
DDL_DATABASE_LEVEL_EVENTS... What I want, is to do exactly this in SQL 2000.
--
Thx,
Anthony E. Castro - MCDBAFor 2000 Ive used this, and really liked it.
http://lumigent.com/products/le_sql.html
"acmcdba68" wrote:

> Rather than reinvent the wheel, I was hoping that there was someone who ha
s
> already created a script allowing for the auditing of DDL statements, sche
ma
> changes within the database for SQL Server 2000.
> I can easily capture the aforementioned in SQL 2005 by making use of a
> trigger on
> DDL_DATABASE_LEVEL_EVENTS... What I want, is to do exactly this in SQL 200
0.
> --
> Thx,
> Anthony E. Castro - MCDBA

Auditing DDL and schema changes in SQL Server 2000

Rather than reinvent the wheel, I was hoping that there was someone who has
already created a script allowing for the auditing of DDL statements, schema
changes within the database for SQL Server 2000.
I can easily capture the aforementioned in SQL 2005 by making use of a
trigger on
DDL_DATABASE_LEVEL_EVENTS... What I want, is to do exactly this in SQL 2000.
--
Thx,
Anthony E. CastroThere is no easy solution in 2000. If you really want this I recommend you
upgrade vs. spending the effort in 2000. But you can run a trace filtering
on only those type events but this is not a good long term solution. You can
also try one of the 3rd party tools to audit the tran logs but that has down
sides as well. Take a look at the free tool from www.red-gate.com for log
audits.
Andrew J. Kelly SQL MVP
"acmcdba68" <acmcdba68@.hotmail.com> wrote in message
news:8E8FE948-9D8A-4524-BF57-9EBA8CA9DF60@.microsoft.com...
> Rather than reinvent the wheel, I was hoping that there was someone who
> has
> already created a script allowing for the auditing of DDL statements,
> schema
> changes within the database for SQL Server 2000.
> I can easily capture the aforementioned in SQL 2005 by making use of a
> trigger on
> DDL_DATABASE_LEVEL_EVENTS... What I want, is to do exactly this in SQL
> 2000.
> --
> Thx,
> Anthony E. Castro

Auditing DDL and schema changes in SQL Server 2000

Rather than reinvent the wheel, I was hoping that there was someone who has
already created a script allowing for the auditing of DDL statements, schema
changes within the database for SQL Server 2000.
I can easily capture the aforementioned in SQL 2005 by making use of a
trigger on
DDL_DATABASE_LEVEL_EVENTS... What I want, is to do exactly this in SQL 2000.
Thx,
Anthony E. Castro - MCDBA
For 2000 Ive used this, and really liked it.
http://lumigent.com/products/le_sql.html
"acmcdba68" wrote:

> Rather than reinvent the wheel, I was hoping that there was someone who has
> already created a script allowing for the auditing of DDL statements, schema
> changes within the database for SQL Server 2000.
> I can easily capture the aforementioned in SQL 2005 by making use of a
> trigger on
> DDL_DATABASE_LEVEL_EVENTS... What I want, is to do exactly this in SQL 2000.
> --
> Thx,
> Anthony E. Castro - MCDBA

Auditing DDL and schema changes in SQL Server 2000

Rather than reinvent the wheel, I was hoping that there was someone who has
already created a script allowing for the auditing of DDL statements, schema
changes within the database for SQL Server 2000.
I can easily capture the aforementioned in SQL 2005 by making use of a
trigger on
DDL_DATABASE_LEVEL_EVENTS... What I want, is to do exactly this in SQL 2000.
--
Thx,
Anthony E. Castro - MCDBAFor 2000 Ive used this, and really liked it.
http://lumigent.com/products/le_sql.html
"acmcdba68" wrote:
> Rather than reinvent the wheel, I was hoping that there was someone who has
> already created a script allowing for the auditing of DDL statements, schema
> changes within the database for SQL Server 2000.
> I can easily capture the aforementioned in SQL 2005 by making use of a
> trigger on
> DDL_DATABASE_LEVEL_EVENTS... What I want, is to do exactly this in SQL 2000.
> --
> Thx,
> Anthony E. Castro - MCDBA

Auditing and IP addresses

In a database I created an audit table in which, e.g., I insert one record
for each INSERT statement made on another table (via a trigger).
My problem is to recover remote IP address of the connected client which
does the INSERT.
I tried to use DMV sys.dm_exec_connections because it contains a "client_net_address"
field: it was just what was searching for.
But, unfortunately, this DMV requires VIEW SERVER STATE permission, which
is not assigned to any user.
May you tell me how can I solve this problem, avoiding to assign that permission
to everybody?
Thanks all.
PBsoft di Gabriele Bertolucci
www.pbsoft.it
skype:pbsoftsolution
Hi,
this is the only way to get the information. prior to the DMV you had
to retrieve the information from XP_cmdshell:
http://www.sqlserver2005.de/Articles/3/
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de

Auditing and IP addresses

In a database I created an audit table in which, e.g., I insert one record
for each INSERT statement made on another table (via a trigger).
My problem is to recover remote IP address of the connected client which
does the INSERT.
I tried to use DMV sys.dm_exec_connections because it contains a "client_net_address"
field: it was just what was searching for.
But, unfortunately, this DMV requires VIEW SERVER STATE permission, which
is not assigned to any user.
May you tell me how can I solve this problem, avoiding to assign that permission
to everybody?
Thanks all.
--
PBsoft di Gabriele Bertolucci
www.pbsoft.it
skype:pbsoftsolutionHi,
this is the only way to get the information. prior to the DMV you had
to retrieve the information from XP_cmdshell:
http://www.sqlserver2005.de/Articles/3/
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
--

Auditing and IP addresses

In a database I created an audit table in which, e.g., I insert one record
for each INSERT statement made on another table (via a trigger).
My problem is to recover remote IP address of the connected client which
does the INSERT.
I tried to use DMV sys.dm_exec_connections because it contains a "client_net
_address"
field: it was just what was searching for.
But, unfortunately, this DMV requires VIEW SERVER STATE permission, which
is not assigned to any user.
May you tell me how can I solve this problem, avoiding to assign that permis
sion
to everybody?
Thanks all.
PBsoft di Gabriele Bertolucci
www.pbsoft.it
skype:pbsoftsolutionHi,
this is the only way to get the information. prior to the DMV you had
to retrieve the information from XP_cmdshell:
http://www.sqlserver2005.de/Articles/3/
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
--

Auditing and IP addresses

In a database I created an audit table in which, e.g., I insert one record
for each INSERT statement made on another table (via a trigger).
My problem is to recover remote IP address of the connected client which
does the INSERT.
I tried to use DMV sys.dm_exec_connections because it contains a "client_net
_address"
field: it was just what was searching for.
But, unfortunately, this DMV requires VIEW SERVER STATE permission, which
is not assigned to any user.
May you tell me how can I solve this problem, avoiding to assign that permis
sion
to everybody?
Thanks all.
PBsoft di Gabriele Bertolucci
www.pbsoft.it
skype:pbsoftsolutionHi,
this is the only way to get the information. prior to the DMV you had
to retrieve the information from XP_cmdshell:
http://www.sqlserver2005.de/Articles/3/
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
--|||PBsoft (info[REMOVE]@.pbsoft.it) writes:
> In a database I created an audit table in which, e.g., I insert one record
> for each INSERT statement made on another table (via a trigger).
> My problem is to recover remote IP address of the connected client which
> does the INSERT.
> I tried to use DMV sys.dm_exec_connections because it contains a
> "client_net_address"
> field: it was just what was searching for.
> But, unfortunately, this DMV requires VIEW SERVER STATE permission, which
> is not assigned to any user.
> May you tell me how can I solve this problem, avoiding to assign that
> permission to everybody?
Create a certificate and which you sign the trigger with. You need to
have this certificate in master as well. Then create a login from that
certificate, and grant that login the rights.
I have an article on my web site that describes this in a lot more
detail: http://www.sommarskog.se/grantperm.html.
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|||try the auditdatabase
auditing tools ( http://www.auditdatabase.com/AuditTools.html ) for
generate audit triggers (FREE) for SQL Server and other DBMS's)
This triggers save the client IP and MAC information
Delia.
Erland Sommarskog ha escrito:
> PBsoft (info[REMOVE]@.pbsoft.it) writes:
> Create a certificate and which you sign the trigger with. You need to
> have this certificate in master as well. Then create a login from that
> certificate, and grant that login the rights.
> I have an article on my web site that describes this in a lot more
> detail: http://www.sommarskog.se/grantperm.html.
>
> --
> 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 Triggers SQL 2000

In the past I have used a poor mans audit trigger for my SQL tables. I basically created another identical table with "_Audit" at the end of it with the same fields. I simply did a trigger like this to copy the entire contents of the Row being updated, deleted, or Inserted.

CREATE TRIGGER fileAudit ON [dbo].[Table1]
FOR INSERT, UPDATE, DELETE
AS
insert Table1_audit
select
*
from inserted i
return

This is bad in several ways and I know that, but it was giving me Audit history that was better than nothing. The one thing I hate most about the above approach is that it copies the enitre record set even if only one field was updated or deleted and the fact I have an audit table for every table in the database.

I want to enhance my Audit Trail to include only one Audit_Table. I would like to mimic this approach that is using CLR for SQL 2005. My Only problem is I am forced to use SQL 2000 for legacy application on the database server. I do not have the ability to migrate to SQL 2005 at this time.

http://sqljunkies.com/Article/4CD01686-5178-490C-A90A-5AEEF5E35915.scuk

All of my Tables have an Auto-increment PK

Could some one point me in the right way to accomplish this.

I recommend to use the following structure;

TableName NVarchar(1000)

OperationDateTime DateTime

Operation smallint (1-Insert, 2-Update, 3-Delete)

Data Text (Common Delimited or XML value of the affected row)

The real challenge here is getting back – querying - the data. For that probably you can design your own UI where you can parse these values for display.

But again these audit trail operations are additional overhead on your data manipulation.

|||

TableName NVarchar(1000)

OperationDateTime DateTime

Operation smallint (1-Insert, 2-Update, 3-Delete)

Data Text (Common Delimited or XML value of the affected row)

Structure should depends on the purposes of audit. Because using this structure it is possible that you could never answer the question who changed some order or what have done John with another orders.
|||

don't use the '*' for auditting or you have to be very carefull of the datatype. you can't read timestamps, text, ... columns from the virtual inserted and deleted tables...

regards

skafever

Audit Tables and triggers

Dear Group,

I would like to create an audit table that is created with a trigger that
reflects all the changes(insert, update and delete) that occur in table.

Say I have a table with

Subject_ID, visit_number, dob, weight, height, User_name, inputdate

The audit table would have .

Subject_ID, visit_number, dob, weight, height, User_name, inputdate,
edit_action, edit_reason.

Where the edit_action would be insert, update, delete; the edit_reason would
be the reason given for the edit.

Help with this would be great, since I am new to the world of triggers.

Thanks,

JeffJeff Magouirk (magouirkj@.njc.org) writes:
> I would like to create an audit table that is created with a trigger that
> reflects all the changes(insert, update and delete) that occur in table.
> Say I have a table with
> Subject_ID, visit_number, dob, weight, height, User_name,
> inputdate
> The audit table would have .
> Subject_ID, visit_number, dob, weight, height, User_name, inputdate,
> edit_action, edit_reason.
> Where the edit_action would be insert, update, delete; the edit_reason
> would be the reason given for the edit.
> Help with this would be great, since I am new to the world of triggers.

If you need to do to this on a broad scale, consider 3rd-party solutions.
Two that I usually recommend - although I've used none of them myself -
is SQLAudit from Red Matrix and Entegra from Lumigent. SQL Audit is
based on triggers, Entegra works from the transaction log.

But for a one-shot you could do:

CREATE TRIGGER tbl_audit_tri ON tbl FOR INSERT, UPDATE, DELETE

IF @.@.rowcount = 0
RETURN

IF EXISTS(SELECT * FROM inserted)
BEGIN
INSERT logtable (subject_id, ... edit_action)
SELECT subject_id, ...
CASE WHEN EXISTS (SELECT * FROM deleted)
THEN 'UPDATE'
ELSE 'INSERT'
FROM inserted
END
ELSE
BEGIN
INSERT logtable (subject_id, ... edit_action)
SELECT subject_id, ..., 'DELETE'
FROM delete
END

As you see I have left out edit_reason. This is because I don't know
what you mean with "edit_reason", and anyway it sounds like something
that can be quite difficult to get hold of from the trigger.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Audit Info

Is there a way I can find out what things were changed on a table, like new
PK were created or dropped as well as constraints added/deleted?
TIAThere is some information from system tables that will show you dates when
objects where created. Not everything.

> Is there a way I can find out what things were changed on a table, like
> new
> PK were created or dropped as well as constraints added/deleted?
> TIA
new|||Vai2000 wrote:
> Is there a way I can find out what things were changed on a table,
> like new PK were created or dropped as well as constraints
> added/deleted?
> TIA
You can use DDL Triggers in SQL Server 2005.
David Gugick
Quest Software|||this gets you general info about constraints on each table - then
anything with a crdate newer than your baseline date are new. Hope this
helps.
J
select sc.*, so1.name as ownername, so2.*
from sysconstraints sc
inner join sysobjects so1 on sc.id = so1.id
inner join sysobjects so2 on sc.constid = so2.id
David Gugick wrote:
> Vai2000 wrote:
> You can use DDL Triggers in SQL Server 2005.
> --
> David Gugick
> Quest Software