Authentication

Hi! I have a problem enabling access to my 'sa' login to a couple of
application db's it fails each time. I think the problem lies herein:I am on
Sql 2005 BTW...When I go to server properties/security it shows,
appropriately, Mixed mode authentication. However, when I click on "View
Connection Properties" in the connection section of the server properties
page it shows windows authentiication. Therefore, I believe that when I try
to add the db's to the mapping for 'sa' the server is looking for a windows
account which of course does not exist. Any advice as to how I might proceed?
Thanks all...I do not want to create a windows account for sa...Adios...
Well, 'view connection properties' refers to your current conn props... so
that just means that you're connecting to the server with windows auth... it
has nothing to do with the server security setting.
Since you didn't really provide an error message I'll say one thing to check
would be whether sa owns the DB in question. You can't add an acct as a user
if that acct already owns the DB.
"Walt Herman" wrote:

> Hi! I have a problem enabling access to my 'sa' login to a couple of
> application db's it fails each time. I think the problem lies herein:I am on
> Sql 2005 BTW...When I go to server properties/security it shows,
> appropriately, Mixed mode authentication. However, when I click on "View
> Connection Properties" in the connection section of the server properties
> page it shows windows authentiication. Therefore, I believe that when I try
> to add the db's to the mapping for 'sa' the server is looking for a windows
> account which of course does not exist. Any advice as to how I might proceed?
> Thanks all...I do not want to create a windows account for sa...Adios...
|||If you install SQL Server in Windows Authentication Mode, then change to
Mixed Authentication Mode, the sa account is disabled.
Use ALTER LOGIN to enable the account. For more information, see the topic
How to: Change Server Authentication Mode in Books Online.
Rick Byham (MSFT)
This posting is provided "AS IS" with no warranties, and confers no rights.
"Walt Herman" <WaltHerman@.discussions.microsoft.com> wrote in message
news:4C4AED35-F285-477F-BE3C-8961098826E8@.microsoft.com...
> Hi! I have a problem enabling access to my 'sa' login to a couple of
> application db's it fails each time. I think the problem lies herein:I am
> on
> Sql 2005 BTW...When I go to server properties/security it shows,
> appropriately, Mixed mode authentication. However, when I click on "View
> Connection Properties" in the connection section of the server properties
> page it shows windows authentiication. Therefore, I believe that when I
> try
> to add the db's to the mapping for 'sa' the server is looking for a
> windows
> account which of course does not exist. Any advice as to how I might
> proceed?
> Thanks all...I do not want to create a windows account for sa...Adios...

Authentication

Hi! I have a problem enabling access to my 'sa' login to a couple of
application db's it fails each time. I think the problem lies herein:I am on
Sql 2005 BTW...When I go to server properties/security it shows,
appropriately, Mixed mode authentication. However, when I click on "View
Connection Properties" in the connection section of the server properties
page it shows windows authentiication. Therefore, I believe that when I try
to add the db's to the mapping for 'sa' the server is looking for a windows
account which of course does not exist. Any advice as to how I might proceed
?
Thanks all...I do not want to create a windows account for sa...Adios...Well, 'view connection properties' refers to your current conn props... so
that just means that you're connecting to the server with windows auth... it
has nothing to do with the server security setting.
Since you didn't really provide an error message I'll say one thing to check
would be whether sa owns the DB in question. You can't add an acct as a use
r
if that acct already owns the DB.
"Walt Herman" wrote:

> Hi! I have a problem enabling access to my 'sa' login to a couple of
> application db's it fails each time. I think the problem lies herein:I am
on
> Sql 2005 BTW...When I go to server properties/security it shows,
> appropriately, Mixed mode authentication. However, when I click on "View
> Connection Properties" in the connection section of the server properties
> page it shows windows authentiication. Therefore, I believe that when I tr
y
> to add the db's to the mapping for 'sa' the server is looking for a window
s
> account which of course does not exist. Any advice as to how I might proce
ed?
> Thanks all...I do not want to create a windows account for sa...Adios...|||If you install SQL Server in Windows Authentication Mode, then change to
Mixed Authentication Mode, the sa account is disabled.
Use ALTER LOGIN to enable the account. For more information, see the topic
How to: Change Server Authentication Mode in Books Online.
--
Rick Byham (MSFT)
This posting is provided "AS IS" with no warranties, and confers no rights.
"Walt Herman" <WaltHerman@.discussions.microsoft.com> wrote in message
news:4C4AED35-F285-477F-BE3C-8961098826E8@.microsoft.com...
> Hi! I have a problem enabling access to my 'sa' login to a couple of
> application db's it fails each time. I think the problem lies herein:I am
> on
> Sql 2005 BTW...When I go to server properties/security it shows,
> appropriately, Mixed mode authentication. However, when I click on "View
> Connection Properties" in the connection section of the server properties
> page it shows windows authentiication. Therefore, I believe that when I
> try
> to add the db's to the mapping for 'sa' the server is looking for a
> windows
> account which of course does not exist. Any advice as to how I might
> proceed?
> Thanks all...I do not want to create a windows account for sa...Adios...

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 single user mode

Any way to audit when/and by whom a DB has been changed to single user mode?
We have a couple of DB's that are being locked, and it appears to be some
process. Nothing shows up in SQL logs / event logs indicating the change.
Any way I can turn on some kind of verbose logging or set some kind of
trigger to watch for this? We think there is some process doing it, but
without a security context and/or time stamp of exactly when it happens it's
been quite difficult to track this event down. Help! Chuck
My bet is that you have a maintenance job for integrity checking that has
the 'Repair minor problems' option checked. This will put the database into
single-user mode if it finds any errors and tries to repair them. You
shouldn't run with this option on as its always better to investigate any
errors rather than blindly repairing them.
Regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Chuck" <Chuck@.discussions.microsoft.com> wrote in message
news:88AAB265-3719-4D97-AD08-E61D20DC83B8@.microsoft.com...
> Any way to audit when/and by whom a DB has been changed to single user
mode?
> We have a couple of DB's that are being locked, and it appears to be some
> process. Nothing shows up in SQL logs / event logs indicating the change.
> Any way I can turn on some kind of verbose logging or set some kind of
> trigger to watch for this? We think there is some process doing it, but
> without a security context and/or time stamp of exactly when it happens
it's
> been quite difficult to track this event down. Help! Chuck

audit single user mode

Any way to audit when/and by whom a DB has been changed to single user mode?
We have a couple of DB's that are being locked, and it appears to be some
process. Nothing shows up in SQL logs / event logs indicating the change.
Any way I can turn on some kind of verbose logging or set some kind of
trigger to watch for this? We think there is some process doing it, but
without a security context and/or time stamp of exactly when it happens it's
been quite difficult to track this event down. Help! ChuckMy bet is that you have a maintenance job for integrity checking that has
the 'Repair minor problems' option checked. This will put the database into
single-user mode if it finds any errors and tries to repair them. You
shouldn't run with this option on as its always better to investigate any
errors rather than blindly repairing them.
Regards.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Chuck" <Chuck@.discussions.microsoft.com> wrote in message
news:88AAB265-3719-4D97-AD08-E61D20DC83B8@.microsoft.com...
> Any way to audit when/and by whom a DB has been changed to single user
mode?
> We have a couple of DB's that are being locked, and it appears to be some
> process. Nothing shows up in SQL logs / event logs indicating the change.
> Any way I can turn on some kind of verbose logging or set some kind of
> trigger to watch for this? We think there is some process doing it, but
> without a security context and/or time stamp of exactly when it happens
it's
> been quite difficult to track this event down. Help! Chuck

audit single user mode

Any way to audit when/and by whom a DB has been changed to single user mode?
We have a couple of DB's that are being locked, and it appears to be some
process. Nothing shows up in SQL logs / event logs indicating the change.
Any way I can turn on some kind of verbose logging or set some kind of
trigger to watch for this? We think there is some process doing it, but
without a security context and/or time stamp of exactly when it happens it's
been quite difficult to track this event down. Help! ChuckMy bet is that you have a maintenance job for integrity checking that has
the 'Repair minor problems' option checked. This will put the database into
single-user mode if it finds any errors and tries to repair them. You
shouldn't run with this option on as its always better to investigate any
errors rather than blindly repairing them.
Regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Chuck" <Chuck@.discussions.microsoft.com> wrote in message
news:88AAB265-3719-4D97-AD08-E61D20DC83B8@.microsoft.com...
> Any way to audit when/and by whom a DB has been changed to single user
mode?
> We have a couple of DB's that are being locked, and it appears to be some
> process. Nothing shows up in SQL logs / event logs indicating the change.
> Any way I can turn on some kind of verbose logging or set some kind of
> trigger to watch for this? We think there is some process doing it, but
> without a security context and/or time stamp of exactly when it happens
it's
> been quite difficult to track this event down. Help! Chuck

attach SQL 2000 DB files to 2005?

I have a couple large databases (20 databases, many GB of data in each).
the vendor has told me that their application will work fine with 2005,
but, I've not done the migration yet.
Is it as simple as a backup from 2000 /restore to 2005?
Can I detach the files for each DB and then just reattach them to the
2005 server?
Anyone done this before?
spam999free@.rrohio.com
remove 999 in order to email meAbsolutely! Backup and Restore is an excellent way to migrate the databases.
If you are using SQL Logins, you will also have to migrate the SQL Logins to
the new server.
These articles should help:
Moving Server/Database/Logins
http://vyaskn.tripod.com/moving_sql_server.htm Moving DBs
http://msdn2.microsoft.com/en-us/library/ms345408(en-US,SQL.90).aspx Moving
system dbs 2005
http://www.support.microsoft.com/?id=314546 Moving DB's between Servers
http://www.support.microsoft.com/?id=224071 Moving SQL Server Databases to a
New Location with Detach/Attach
http://www.support.microsoft.com/?id=221465 Using WITH MOVE in a Restore
http://www.sqlservercentral.com/col...se
s.asp
Moving Users
http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
Passwords Between SQL Servers
http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after a
Restore
http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to users
http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
Errors After Restoring Dump
http://support.microsoft.com/kb/274188 Troubleshooting Orphan Logins
http://www.support.microsoft.com/?id=240872 Resolve Permission
Issues -Database Is Moved Between SQL Servers
http://www.sqlservercentral.com/scr...sp?scriptid=599
Restoring a .mdf
http://www.support.microsoft.com/?id=307775 Disaster Recovery Articles for
SQL Server
http://www.support.microsoft.com/?id=320125 Moving a Diagram
http://www.sqlservercentral.com/scr...utions/1598.asp Script Roles
and Permissions
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Leythos" <void@.nowhere.lan> wrote in message
news:4582dde5$0$22165$4c368faf@.roadrunne
r.com...
>I have a couple large databases (20 databases, many GB of data in each).
> the vendor has told me that their application will work fine with 2005,
> but, I've not done the migration yet.
> Is it as simple as a backup from 2000 /restore to 2005?
> Can I detach the files for each DB and then just reattach them to the
> 2005 server?
> Anyone done this before?
>
> --
> spam999free@.rrohio.com
> remove 999 in order to email me|||In article <e5EkGDIIHHA.1240@.TK2MSFTNGP03.phx.gbl>, arnie@.1568.com
says...
> Absolutely! Backup and Restore is an excellent way to migrate the database
s.
> If you are using SQL Logins, you will also have to migrate the SQL Logins
to
> the new server.
Thanks, I already have several scripts to built user scripts that assign
and give access/permission to the databases. We manually restore
production to QA every night, but they are both SQL 2000.
Thanks again.
spam999free@.rrohio.com
remove 999 in order to email me