Auto date through priority

Hi,

I have a table which contains a Create datetimefield which has a default on the current date , a priorityfield and another datetimefield which will be the due date and has to be calculated by the first date and the priority field,

How can i do this and what fields must i have.

Does someone does this?
can someone help me with this?

Kind regards Wimwhat whould happen to the due-date if the priority changed?|||what whould happen to the due-date if the priority changed?

The due date has to change to!|||use a computed column for the due date, fe:
CREATE TABLE TAB1 (
FIRSTDATE DATETIME DEFAULT GETDATE()
, PRIO INTEGER
, DUEDATE AS DATEADD(DD, PRIO, GETDATE())
)
GO

INSERT INTO TAB1 (PRIO) VALUES (1)
GO

SELECT * FROM TAB1
UPDATE TAB1 SET PRIO = 10
SELECT * FROM TAB1
GO

DROP TABLE TAB1
GO

EDIT: Just to be sure; either have a default prio or have a not null constraint|||use a computed column for the due date, fe:
CREATE TABLE TAB1 (
FIRSTDATE DATETIME DEFAULT GETDATE()
, PRIO INTEGER
, DUEDATE AS DATEADD(DD, PRIO, GETDATE())
)
GO

INSERT INTO TAB1 (PRIO) VALUES (1)
GO

SELECT * FROM TAB1
UPDATE TAB1 SET PRIO = 10
SELECT * FROM TAB1
GO

DROP TABLE TAB1
GO

EDIT: Just to be sure; either have a default prio or have a not null constraint

And what if i use the priority through an foreign key an have several prioritys which uses different time like:

prio 1 = 1day
prio 2 =1 week
prio3 = 1 month

How will it look like then?|||well, it could look like:

CREATE TABLE TAB1 (
FIRSTDATE DATETIME DEFAULT GETDATE()
, PRIO INTEGER NOT NULL
, DUEDATE AS CASE PRIO
WHEN 1 THEN DATEADD(DD, 1, FIRSTDATE)
WHEN 2 THEN DATEADD(WW, 1, FIRSTDATE)
WHEN 3 THEN DATEADD(MM, 1, FIRSTDATE)
ELSE DATEADD(YY, 1, FIRSTDATE)
END
)
GO|||well, it could look like:

CREATE TABLE TAB1 (
FIRSTDATE DATETIME DEFAULT GETDATE()
, PRIO INTEGER NOT NULL
, DUEDATE AS CASE PRIO
WHEN 1 THEN DATEADD(DD, 1, FIRSTDATE)
WHEN 2 THEN DATEADD(WW, 1, FIRSTDATE)
WHEN 3 THEN DATEADD(MM, 1, FIRSTDATE)
ELSE DATEADD(YY, 1, FIRSTDATE)
END
)
GO
Thanx alot man, that hit the spot!!
I really appreciated your help..

Cheers Wim

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

Audit Trail - getting the current username.

Hi, I have a couple of questions on writing an audit trail/last updated
feature for my database.
First, How can I programatically get the current user in SQL when
executing a stored procedure.I have tried using sysusers table and
CURRENT_USER but only get the value dbo. I am using integrated
security. sp_who seesm to have the data but how can i use it?
Second, is it actually prefered approach to pass this value in from IIS
or the calling application as a parameter? I'd prefer it be hardcoded
in the stoerd procedure. Are there any divantages to this ?
Third question, I am doing this for a simple audit of LastUpdated and
LastUpDatedBy with additional columns on tables (see below), I know a
trigger and history table is much more comprehensive, but are there any
other approaches to consider or built in tools for tracking changes to
records and capturing who/when data.
thanks
hals_left
CREATE PROCEDURE [add_record]
@.RecordID smallInt,
AS
Begin
INSERT INTO myTable(RecordID,LastUpdated,LastUpdated
By )
VALUES ( @.RecordID , GetDate(), CURRENT_USER )
End
GOSELECT SYSTEM_USER
That will return the SQL login name or the windows domain and username.
HTH
--
Gail Shaw (MCSD)
http://gail.rucus.net/
"cc900630@.ntu.ac.uk" wrote:

> Hi, I have a couple of questions on writing an audit trail/last updated
> feature for my database.
> First, How can I programatically get the current user in SQL when
> executing a stored procedure.I have tried using sysusers table and
> CURRENT_USER but only get the value dbo. I am using integrated
> security. sp_who seesm to have the data but how can i use it?
> Second, is it actually prefered approach to pass this value in from IIS
> or the calling application as a parameter? I'd prefer it be hardcoded
> in the stoerd procedure. Are there any divantages to this ?
> Third question, I am doing this for a simple audit of LastUpdated and
> LastUpDatedBy with additional columns on tables (see below), I know a
> trigger and history table is much more comprehensive, but are there any
> other approaches to consider or built in tools for tracking changes to
> records and capturing who/when data.
> thanks
> hals_left
> CREATE PROCEDURE [add_record]
> @.RecordID smallInt,
> AS
> Begin
> INSERT INTO myTable(RecordID,LastUpdated,LastUpdated
By )
> VALUES ( @.RecordID , GetDate(), CURRENT_USER )
> End
> GO
>|||
"cc900630@.ntu.ac.uk" schrieb:

> Hi, I have a couple of questions on writing an audit trail/last updated
> feature for my database.
> First, How can I programatically get the current user in SQL when
> executing a stored procedure.I have tried using sysusers table and
> CURRENT_USER but only get the value dbo. I am using integrated
> security. sp_who seesm to have the data but how can i use it?
> Second, is it actually prefered approach to pass this value in from IIS
> or the calling application as a parameter? I'd prefer it be hardcoded
> in the stoerd procedure. Are there any divantages to this ?
> Third question, I am doing this for a simple audit of LastUpdated and
> LastUpDatedBy with additional columns on tables (see below), I know a
> trigger and history table is much more comprehensive, but are there any
> other approaches to consider or built in tools for tracking changes to
> records and capturing who/when data.
> thanks
> hals_left
> CREATE PROCEDURE [add_record]
> @.RecordID smallInt,
> AS
> Begin
> INSERT INTO myTable(RecordID,LastUpdated,LastUpdated
By )
> VALUES ( @.RecordID , GetDate(), CURRENT_USER )
> End
> GO
Try SUser_SName() ...|||Hi,
For the first question:
You can capture the current user in SQL using SYSTEM_USER.
For the second question:
When you can capture this in SQL through SYSTEM_USER keyword, i hope you can
avoid hardcoding.
For the third Question:
You can launch the enterprise manager. Right click on the database to look
for properties. In the properties window, go to the security tab and set the
Audit level to your choice.After you change audit settings, you need to
restart the server. But this writes to Application Log might degrade
performance. I think the approach you are using should be fair enough.
I hope this will be of some help to you.
"cc900630@.ntu.ac.uk" wrote:

> Hi, I have a couple of questions on writing an audit trail/last updated
> feature for my database.
> First, How can I programatically get the current user in SQL when
> executing a stored procedure.I have tried using sysusers table and
> CURRENT_USER but only get the value dbo. I am using integrated
> security. sp_who seesm to have the data but how can i use it?
> Second, is it actually prefered approach to pass this value in from IIS
> or the calling application as a parameter? I'd prefer it be hardcoded
> in the stoerd procedure. Are there any divantages to this ?
> Third question, I am doing this for a simple audit of LastUpdated and
> LastUpDatedBy with additional columns on tables (see below), I know a
> trigger and history table is much more comprehensive, but are there any
> other approaches to consider or built in tools for tracking changes to
> records and capturing who/when data.
> thanks
> hals_left
> CREATE PROCEDURE [add_record]
> @.RecordID smallInt,
> AS
> Begin
> INSERT INTO myTable(RecordID,LastUpdated,LastUpdated
By )
> VALUES ( @.RecordID , GetDate(), CURRENT_USER )
> End
> GO
>

Audit Table

The current environment is an ASP frontend with IIS 5.0
using (windows authentication) and SQL Server 2000(windows
authentication) on the same machine.
I would like to add two columns onto several tables and
have a timestamp and username inserted into them when a
user performs an update or insert. I believe it's a good
idea to use an insert or update trigger, but I'm not sure
how the asp application delegates who is logged to sql
server.
What is the best way to do this?
Do you need to have IIS and Sql server configured a
certain way in order to grab the username from the asp
application?Front-end code typically has to influence on a trigger. The trigger fires
as a result of the triggering action - INSERT, UPDATE or DELETE. Here's an
example to do what you want:
create trigger triu_MyTable on MyTable after insert, update
as
if @.@.ROWCOUNT = 0
return
update MyTable
set
LastModBy = CURRENT_USER
, LastUpdateDateTime = CURRENT_TIMESTAMP
where
PK in (select PK from inserted)
go
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
.
"Michelle" <michelle.vanden@.eglin.af.mil> wrote in message
news:034501c3cdc5$1a2f3a60$a401280a@.phx.gbl...
The current environment is an ASP frontend with IIS 5.0
using (windows authentication) and SQL Server 2000(windows
authentication) on the same machine.
I would like to add two columns onto several tables and
have a timestamp and username inserted into them when a
user performs an update or insert. I believe it's a good
idea to use an insert or update trigger, but I'm not sure
how the asp application delegates who is logged to sql
server.
What is the best way to do this?
Do you need to have IIS and Sql server configured a
certain way in order to grab the username from the asp
application?|||Hi Michelle,
Thanks for your post. According to your description, I understand that you
want to record and return the current login username to certain table in
SQL Server, when you performed insert or update action. If I have
misunderstood, please feel free to let me know.
Before we go any further, I would like to collect more information from
you: 1. Which username do you want to record, the usernames used to log on
IIS or SQL Server?
2. Which authentication do you choose to log on IIS?
So far as I know, if we want to record the usernames used for SQL Server,
we can try to use suser_sname() to return the string of the current login
identification name
For more information regarding suser_sname function, please refer to the
following article on SQL Server Books Online.
Topic: "SUSER_SNAME"
On the SQL Server side, it seems hard to record the usernames which are
used to log on IIS.
Thanks for using MSDN newsgroup.
Regards,
Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.|||1). Both IIS and SQL Server if possible. It would be
better if we get the username from IIS and have it
delegated to SQL Server.
2). Basic Authentication w/SSL on IIS on one machine and
windows Authentication w/SSL on another.

quote:

---

>--Original Message--
>Hi Michelle,
>Thanks for your post. According to your description, I

---

understand that you

quote:

---

>want to record and return the current login username to

---

certain table in

quote:

---

>SQL Server, when you performed insert or update action.

---

If I have

quote:

---

>misunderstood, please feel free to let me know.
>Before we go any further, I would like to collect more

---

information from

quote:

---

>you: 1. Which username do you want to record, the

---

usernames used to log on

quote:

---

>IIS or SQL Server?
>2. Which authentication do you choose to log on IIS?
>So far as I know, if we want to record the usernames used

---

for SQL Server,

quote:

---

>we can try to use suser_sname() to return the string of

---

the current login

quote:

---

>identification name
>For more information regarding suser_sname function,

---

please refer to the

quote:

---

>following article on SQL Server Books Online.
>Topic: "SUSER_SNAME"
>On the SQL Server side, it seems hard to record the

---

usernames which are

quote:

---

>used to log on IIS.
>Thanks for using MSDN newsgroup.
>Regards,
>Michael Shao
>Microsoft Online Partner Support
>Get Secure! - www.microsoft.com/security
>This posting is provided "as is" with no warranties and

---

confers no rights.

quote:

---

>
>.
>

---

|||Hi Michelle,
Thanks for your feedback. In this case, as IIS and SQL Server are on the
same machine, a user's credentials (username:password) will be used to
login to SQL Server after that user has logged into IIS using Basic
authentication.
We are able to record the login information (current login username and
timestamp) for SQL Server using the trigger and the related functions
(SUSER_SNAME, GETDATE() etc.). However, it seems impossible to monitor the
logins to IIS from the SQL Server side. SQL Server is unable to be used to
monitor the logins to IIS.
Please feel free to post in the group if this solves your problem or if you
would like further assistance.
Regards,
Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.|||Hi Michelle,
How is this issue going on your side? Based on my further research, it
seems possible to monitor and record the logins to IIS via ASP programming.
To obtain the detailed information regarding monitoring the logins to IIS
using ASP, it is best that you can post in the ASP newsgroup, such as
microsoft.public.inetserver.asp.general,
microsoft.public.inetserver.asp.db. The ASP newsgroup is primarily for
issues involving ASP programming. The reason why we recommend posting
appropriately is you will get the most qualified pool of respondents, and
other partners who read the newsgroups regularly can either share their
knowledge or learn from your interaction with us.
Thanks for using Microsoft newsgroup.
Regards,
Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.

attaching database on create

Hi,

My current server going nuts it restaring every one hour, I have 200+GB (80+ files) database size. The network guyes don't have backup. This is what I am trying to do. I have detach my database. I have copied all the file to new server. Now i am trying to attach using create, I can't just use attach because 16+ files. When I run following on my server it gives me an error.

Any help will be highly appreciated.

please email me the answer if you have.

Thanks.

Samir

CREATE DATABASE KEYFILE
ON PRIMARY (NAME= 'KEYFILE001' FILENAME='F:\KEYFILEDB\KEYFILE001.MDF')
ON SECONDARY (NAME='KEYFILE002' FILENAME='F:\KEYFILEDB\KEYFILE002.NDF')
ON SECONDARY (NAME='KEYFILE003' FILENAME='F:\KEYFILEDB\KEYFILE003.NDF')
ON SECONDARY (NAME='KEYFILE004' FILENAME='F:\KEYFILEDB\KEYFILE004.NDF')
ON SECONDARY (NAME='KEYFILE005' FILENAME='F:\KEYFILEDB\KEYFILE005.NDF')
ON SECONDARY (NAME='KEYFILE006' FILENAME='F:\KEYFILEDB\KEYFILE006.NDF')
ON SECONDARY (NAME='KEYFILE007' FILENAME='F:\KEYFILEDB\KEYFILE007.NDF')
ON SECONDARY (NAME='KEYFILE008' FILENAME='F:\KEYFILEDB\KEYFILE008.NDF')
LOG ON SECONDARY (NAME= 'KEYFILE_LOG' FILENAME='F:\KEYFILEDB\KEYFILE_LOG.LDF')

FOR ATTACHWhat's the error message that you are getting?

A superfluous comment would be that 80+ files seems a bit much. Is there not some way for you to decrease the number of files (moving objects, etc)?

Regards,

hmscott