changing the authenticaon mode

How can I change the authentication mode with t-sql?

Here you go...

Code Snippet

xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\MSSQLServer', 'LoginMode', N'REG_DWORD', 1 -- Windows Only

xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\MSSQLServer', 'LoginMode', N'REG_DWORD', 2 -- Mixed Mode (Windows & Sql Server)

reconfigure with override

After the execution restart the sql server..(other wise the changes will not be taken effect)

CHanging Recovery Options SQL 2000

Howdy,

I want to run a Re-Indexing job against a database that normally has Tran Log backups ( it uses FULL recovery mode ) on SQL 2000. But when this job runs, it causes HUGE tran logs and huge tran log backups ( which then use up 99% of our disk space).

The job needs to be run, so I thought of changing recovery mode to SIMPLE, then run the job , then changing the recovery mode back to FULL. .........Problem solved, you would think.....

But, if the database were to corrupt after the job ( i.e. hours later ) where do I stand with what was in the tran logs both before the revovery mode change and then after? i.e. when I change to SIMPLE does the contents of the tran log get written to the db
or what?

By the way, we normally run tran log backups until 8am-6pm then do the re-indexing job at 4am and re-start the tran log backups at 8am.

Thanks for any help,

SGSafest way would be to backup the DB, change the recov mode, run the reindex, change recov mode back and back the db up again.

Never read it anywhere but pretty sure if you change the recov mode it would checkpoint the tran log.

HTH

Changing Recovery Mode

If I change recovery mode on a database from FULL to SIMPLE, when is this
change effective?Immediately.
--
Andrew J. Kelly SQL MVP
"Dave" <davefrick@.newsgroup.nospam> wrote in message
news:eTlP26olEHA.2500@.TK2MSFTNGP09.phx.gbl...
> If I change recovery mode on a database from FULL to SIMPLE, when is this
> change effective?
>|||Hi,
Change will be effective immediately. But if you need to change the Recovery
model back to FULL then you need to do a FULL database
backup to recreate the backup chain. After that you could start your
Transaction log backup.
Thanks
Hari
MCDBA
"Dave" <davefrick@.newsgroup.nospam> wrote in message
news:eTlP26olEHA.2500@.TK2MSFTNGP09.phx.gbl...
> If I change recovery mode on a database from FULL to SIMPLE, when is this
> change effective?
>

Changing Recovery Mode

If I change recovery mode on a database from FULL to SIMPLE, when is this
change effective?
Immediately.
Andrew J. Kelly SQL MVP
"Dave" <davefrick@.newsgroup.nospam> wrote in message
news:eTlP26olEHA.2500@.TK2MSFTNGP09.phx.gbl...
> If I change recovery mode on a database from FULL to SIMPLE, when is this
> change effective?
>
|||Hi,
Change will be effective immediately. But if you need to change the Recovery
model back to FULL then you need to do a FULL database
backup to recreate the backup chain. After that you could start your
Transaction log backup.
Thanks
Hari
MCDBA
"Dave" <davefrick@.newsgroup.nospam> wrote in message
news:eTlP26olEHA.2500@.TK2MSFTNGP09.phx.gbl...
> If I change recovery mode on a database from FULL to SIMPLE, when is this
> change effective?
>

Changing Licensing mode of SQL server

Hi All
Please can someone tell me if there is a way of changing the Licensing mode
of SQL Server 2000 from Device/User CALs to a Single CPU License mode ?
I ask because I am currently running Windows SBS 2000 and want to use the
SBS Migration Pack to split it down to the separate packages. And from what
I can see all the packages, including SQL server, are put in Device/User CAL
licensing more with 5 CALs (although I could be wrong!).
I need around 50+ CALs. So it would be cheaper to go for a Single CPU
license rather than all those CALs !
Any advice or suggestions much appreciated.
Benny
The following is unsupported but you may want to check the
link:
Q:How to change the license mode after SQL2000 setup?
http://www.tkdinesh.com/faq/ans/license.html
-Sue
On Wed, 4 Aug 2004 18:56:27 +0100, "Benny"
<NoSpam@.Thanks.net> wrote:

>Hi All
>Please can someone tell me if there is a way of changing the Licensing mode
>of SQL Server 2000 from Device/User CALs to a Single CPU License mode ?
>I ask because I am currently running Windows SBS 2000 and want to use the
>SBS Migration Pack to split it down to the separate packages. And from what
>I can see all the packages, including SQL server, are put in Device/User CAL
>licensing more with 5 CALs (although I could be wrong!).
>I need around 50+ CALs. So it would be cheaper to go for a Single CPU
>license rather than all those CALs !
>Any advice or suggestions much appreciated.
>Benny
>

changing in sysaltfiles doesnt work

HI All,

I have started sqlserver in single user mode and changed filename in sysaltfiles for tempdb too point to new location. While starting sqlserver in normal it points to the old path and doesnt get updated with the new path.

1.)is there any systable still to be altered?
2.) i have even tried alter database, doesnt work.
3.)The master files have been taken from server1 (where tempdb points to d:\data) to server2 ( where i need to point tempdb to point E:\mssql\data). i can detach and attach msdb and model successfully, but in the case of tempdb, i cant either alter sysaltfiles or detach and attach tempdb to new path.

, too tired in trying all the possiblities... Is there any possibility to update tempdb to point to new path?

These should work and take effect after a restart.

ALTER DATABASE tempdb MODIFY FILE (NAME='tempdev', FILENAME='e:\mssql\data\tempdb.mdf')

GO

ALTER DATABASE tempdb MODIFY FILE (NAME='templog', FILENAME='e:\mssql\data\tempdb.ldf')

GO

SHUTDOWN
GO

What version of SQL Server are you running?

|||Hi Peter,

If tempdb exists on D:\data drive then above script is working fine. Here in my case no D:\data exists, ive copied master from another server and i need this to point to E: drive. I have tried the above and got a error as

tempdb not found
connection broken

hence i cant change the tempdb path alone.

Thx|||

Could you please post what version of SQL Server are you running? Did you run the statment from master? What is in the error log?

-Sue

|||

You can start the server with the -f parameter to get a minimal sized tempdb in the same directory as master, then move it where you want.

i.e., for the default instance, you would do:

net start mssqserver /f

changing in sysaltfiles doesnt work

HI All,

I have started sqlserver in single user mode and changed filename in sysaltfiles for tempdb too point to new location. While starting sqlserver in normal it points to the old path and doesnt get updated with the new path.

1.)is there any systable still to be altered?
2.) i have even tried alter database, doesnt work.
3.)The master files have been taken from server1 (where tempdb points to d:\data) to server2 ( where i need to point tempdb to point E:\mssql\data). i can detach and attach msdb and model successfully, but in the case of tempdb, i cant either alter sysaltfiles or detach and attach tempdb to new path.

, too tired in trying all the possiblities... Is there any possibility to update tempdb to point to new path?

These should work and take effect after a restart.

ALTER DATABASE tempdb MODIFY FILE (NAME='tempdev', FILENAME='e:\mssql\data\tempdb.mdf')

GO

ALTER DATABASE tempdb MODIFY FILE (NAME='templog', FILENAME='e:\mssql\data\tempdb.ldf')

GO

SHUTDOWN
GO

What version of SQL Server are you running?

|||Hi Peter,

If tempdb exists on D:\data drive then above script is working fine. Here in my case no D:\data exists, ive copied master from another server and i need this to point to E: drive. I have tried the above and got a error as

tempdb not found
connection broken

hence i cant change the tempdb path alone.

Thx|||

Could you please post what version of SQL Server are you running? Did you run the statment from master? What is in the error log?

-Sue

|||

You can start the server with the -f parameter to get a minimal sized tempdb in the same directory as master, then move it where you want.

i.e., for the default instance, you would do:

net start mssqserver /f

Changing From Windows To Mixed Authentication

Hi All

I am trying to switch from Windows to Mixed mode authentication on SQL
Server 2000. I am right-clicking the server instance in Enterprise
Manager and selecting the correct option from the Security tab.

Unfortunately, the change I make does not persist when I click OK. I
get no error message, but when I re-open the server properties, the
option has returned to being Windows authentication.

Any ideas?

Thanks,

Kulgan.Did you restart the service ?

HTH, Jens Suessmeyer.|||Hi

I stopped and started the service using Enterprise Manager (i.e. right
click the server in Enterprise Manager and select Stop from the menu,
the start again using Start from the same menu).

Do I need to stop and start using some other method?

Thanks,

Kulgan.|||--------------------
http://support.microsoft.com/defaul...b;EN-US;q285097
--------------------
INF: How to Change the Default Login Authentication Mode to SQL While
Installing SQL Server 2000 Desktop Engine by Using Windows Installer
--------------------
<snip>
Another way to change the security mode after installation is to stop
SQL Server and set the appropriate registry key for your installation:

Default instance:
HKLM\Software\Microsoft\MSSqlserver\MSSqlServer\Lo ginMode

Named instance:
HKLM\Software\Microsoft\Microsoft SQL Server\Instance
Name\MSSQLServer\LoginMode

to 2 for mixed-mode or 1 for integrated. (Integrated is the default
setup for the SQL Server 2000 Data Engine.)
</snip
-URL--------------------|||Fantastic! Worked a treat!

Kulgan.

Changing from Windows Authentication to Mixed Mode?

Is there a way to change from Windows Authentication mode to Mixed
Mode on SQL Server 2000 without having to reinstall it?
Thanks,
Ed B.
buch75@.comcast.net
Yes. EM, right-click your server, properties, security.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Ed B." <buch75@.comcast.net> wrote in message
news:51aac685.0410070933.18bb5234@.posting.google.c om...
> Is there a way to change from Windows Authentication mode to Mixed
> Mode on SQL Server 2000 without having to reinstall it?
> Thanks,
> Ed B.
> buch75@.comcast.net
|||Of course, go to Enterprise Manager, then go to the SQL Server Properties
then go to Security and change the option over there.
After aplying that you must restart the SQL Server.
--
Roberto Rodrigues
Consultor Tcnico
Dep. Tcnico
Sofistica - Solues Informticas, Lda.
WWW.SOFISTICAPORTUGAL.COM
"Ed B." <buch75@.comcast.net> escreveu na mensagem
news:51aac685.0410070933.18bb5234@.posting.google.c om...
> Is there a way to change from Windows Authentication mode to Mixed
> Mode on SQL Server 2000 without having to reinstall it?
> Thanks,
> Ed B.
> buch75@.comcast.net

Changing from Windows Authentication to Mixed Mode?

Is there a way to change from Windows Authentication mode to Mixed
Mode on SQL Server 2000 without having to reinstall it?
Thanks,
Ed B.
buch75@.comcast.netYes. EM, right-click your server, properties, security.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Ed B." <buch75@.comcast.net> wrote in message
news:51aac685.0410070933.18bb5234@.posting.google.com...
> Is there a way to change from Windows Authentication mode to Mixed
> Mode on SQL Server 2000 without having to reinstall it?
> Thanks,
> Ed B.
> buch75@.comcast.net|||Of course, go to Enterprise Manager, then go to the SQL Server Properties
then go to Security and change the option over there.
After aplying that you must restart the SQL Server.
--
--
Roberto Rodrigues
Consultor Técnico
Dep. Técnico
Sofistica - Soluções Informáticas, Lda.
WWW.SOFISTICAPORTUGAL.COM
"Ed B." <buch75@.comcast.net> escreveu na mensagem
news:51aac685.0410070933.18bb5234@.posting.google.com...
> Is there a way to change from Windows Authentication mode to Mixed
> Mode on SQL Server 2000 without having to reinstall it?
> Thanks,
> Ed B.
> buch75@.comcast.net

Changing from Administration Mode to Application Mode

I have a W2K server hosting SQL2K and have need to switch it to running in A
pplication mode as the number of remote SQL users has made it difficult to w
ork with only 2 allowed connections as in Administration mode. I am wonderi
ng what the best practice/m
ethod to use to go about accomplishing this task with the least disruption t
o the SQL server (as when I was about to do it I received an error message i
ndicating that SQL would break). Thanks for a quick response.Terminal services probably shouldn't be on the same box as SQL if you want
to run application mode, but the warning you are likely getting is not
neccesarily going to break SQL but since it was installed before App mode
Terminal services were enabled it thinks that this app will need to be
reinstalled to take advantage of Application mode terminal services.
I would purchase a copy of VMware (my favourite tool of all time
www.vmware.com ) and try it out. install a VM session, install Win2k OS into
it, enable term services in admin mode, install SQL and then flip it to app
mode. see what happens.
My guess is you will be fine, but the most impact would be a reboot so a
weekender would be a good idea, but this is a change I would recommend
against. it will adversely affect performance... my $0.02
Neil MacMurchy
"FColvais" <fcolvais1@.comcast.net> wrote in message
news:758C7A26-D01B-4228-89E0-3BB33205560F@.microsoft.com...
> I have a W2K server hosting SQL2K and have need to switch it to running in
Application mode as the number of remote SQL users has made it difficult to
work with only 2 allowed connections as in Administration mode. I am
wondering what the best practice/method to use to go about accomplishing
this task with the least disruption to the SQL server (as when I was about
to do it I received an error message indicating that SQL would break).
Thanks for a quick response.

Changing database status from Read Only

Ive attached a database from 2 MDF files I copied from a production
database. The attach works fine, except it always attaches in Read Only
mode.
I notice when attaching that, despite me specifying sa as the database
owner, it always makes my domain account the database owner. When I try to
make the owner sa using sp_changedbowner, I get error messages about the
database read only.
How can I make the database turn back to normal status?
ThanksElliot,
try ALTER DATABASE dbname SET READ_WRITE
HTH,
Paul Ibison|||Paul:
Thanks for the reply.
Running this command returns the following errors:
Server: Msg 5105, Level 16, State 4, Line 1
Device activation error. The physical file name
'F:\sqldata\DOCUMENTS_Data.MDF' may be incorrect.
There are 2 primary files, along with a single log file. Ive confirmed their
locations and all hae their read/write attributes set properly. The only
other thing I can think of is permissions, which should happen because I am
logged into QA and EM as sa.
Any other thoughts?
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:%23tRADqASEHA.3016@.tk2msftngp13.phx.gbl...
> Elliot,
> try ALTER DATABASE dbname SET READ_WRITE
> HTH,
> Paul Ibison
>|||Your files are probably in 'Read Only' mode before you
even restore the database (Usually if it was taken out of
the CD). Change the file properties first by checking OFF
the 'read-only' option before restoring the
database......
'F:\sqldata\DOCUMENTS_Data.MDF' may be incorrect.
error is stating that the server you are restoring it to
does not have that path (It is the old server's path).
Change that in the 'Options'--> 'Restore AS' part.
>--Original Message--
>Paul:
>Thanks for the reply.
>Running this command returns the following errors:
>Server: Msg 5105, Level 16, State 4, Line 1
>Device activation error. The physical file name
>'F:\sqldata\DOCUMENTS_Data.MDF' may be incorrect.
>There are 2 primary files, along with a single log file.
Ive confirmed their
>locations and all hae their read/write attributes set
properly. The only
>other thing I can think of is permissions, which should
happen because I am
>logged into QA and EM as sa.
>Any other thoughts?
>
>
>"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
>news:%23tRADqASEHA.3016@.tk2msftngp13.phx.gbl...
>> Elliot,
>> try ALTER DATABASE dbname SET READ_WRITE
>> HTH,
>> Paul Ibison
>>
>
>.
>|||Jack:
I'm doing an attach from the MDF, NDF, and LDF files. I'm not restoring from
a backup.
"Jack" <anonymous@.discussions.microsoft.com> wrote in message
news:16aec01c44812$477d4620$a501280a@.phx.gbl...
> Your files are probably in 'Read Only' mode before you
> even restore the database (Usually if it was taken out of
> the CD). Change the file properties first by checking OFF
> the 'read-only' option before restoring the
> database......
> 'F:\sqldata\DOCUMENTS_Data.MDF' may be incorrect.
> error is stating that the server you are restoring it to
> does not have that path (It is the old server's path).
> Change that in the 'Options'--> 'Restore AS' part.
>
> >--Original Message--
> >Paul:
> >
> >Thanks for the reply.
> >
> >Running this command returns the following errors:
> >Server: Msg 5105, Level 16, State 4, Line 1
> >Device activation error. The physical file name
> >'F:\sqldata\DOCUMENTS_Data.MDF' may be incorrect.
> >
> >There are 2 primary files, along with a single log file.
> Ive confirmed their
> >locations and all hae their read/write attributes set
> properly. The only
> >other thing I can think of is permissions, which should
> happen because I am
> >logged into QA and EM as sa.
> >
> >Any other thoughts?
> >
> >
> >
> >
> >"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
> >news:%23tRADqASEHA.3016@.tk2msftngp13.phx.gbl...
> >> Elliot,
> >> try ALTER DATABASE dbname SET READ_WRITE
> >> HTH,
> >> Paul Ibison
> >>
> >>
> >
> >
> >.
> >|||Check those MDF, NDF and LDF files by right clicking on
them and choosing Properties -- General tab. See if
the 'Read Only' attribute is checked ON. If it is, check
it OFF , then restore the database........
>--Original Message--
>Jack:
>I'm doing an attach from the MDF, NDF, and LDF files. I'm
not restoring from
>a backup.
>
>"Jack" <anonymous@.discussions.microsoft.com> wrote in
message
>news:16aec01c44812$477d4620$a501280a@.phx.gbl...
>> Your files are probably in 'Read Only' mode before you
>> even restore the database (Usually if it was taken out
of
>> the CD). Change the file properties first by checking
OFF
>> the 'read-only' option before restoring the
>> database......
>> 'F:\sqldata\DOCUMENTS_Data.MDF' may be incorrect.
>> error is stating that the server you are restoring it to
>> does not have that path (It is the old server's path).
>> Change that in the 'Options'--> 'Restore AS' part.
>>
>> >--Original Message--
>> >Paul:
>> >
>> >Thanks for the reply.
>> >
>> >Running this command returns the following errors:
>> >Server: Msg 5105, Level 16, State 4, Line 1
>> >Device activation error. The physical file name
>> >'F:\sqldata\DOCUMENTS_Data.MDF' may be incorrect.
>> >
>> >There are 2 primary files, along with a single log
file.
>> Ive confirmed their
>> >locations and all hae their read/write attributes set
>> properly. The only
>> >other thing I can think of is permissions, which should
>> happen because I am
>> >logged into QA and EM as sa.
>> >
>> >Any other thoughts?
>> >
>> >
>> >
>> >
>> >"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in
message
>> >news:%23tRADqASEHA.3016@.tk2msftngp13.phx.gbl...
>> >> Elliot,
>> >> try ALTER DATABASE dbname SET READ_WRITE
>> >> HTH,
>> >> Paul Ibison
>> >>
>> >>
>> >
>> >
>> >.
>> >
>
>.
>|||I see what you mean now.....
Maybe the database was in a 'Read Only' status at the time
it was detached ?
>--Original Message--
>Jack:
>I'm doing an attach from the MDF, NDF, and LDF files. I'm
not restoring from
>a backup.
>
>"Jack" <anonymous@.discussions.microsoft.com> wrote in
message
>news:16aec01c44812$477d4620$a501280a@.phx.gbl...
>> Your files are probably in 'Read Only' mode before you
>> even restore the database (Usually if it was taken out
of
>> the CD). Change the file properties first by checking
OFF
>> the 'read-only' option before restoring the
>> database......
>> 'F:\sqldata\DOCUMENTS_Data.MDF' may be incorrect.
>> error is stating that the server you are restoring it to
>> does not have that path (It is the old server's path).
>> Change that in the 'Options'--> 'Restore AS' part.
>>
>> >--Original Message--
>> >Paul:
>> >
>> >Thanks for the reply.
>> >
>> >Running this command returns the following errors:
>> >Server: Msg 5105, Level 16, State 4, Line 1
>> >Device activation error. The physical file name
>> >'F:\sqldata\DOCUMENTS_Data.MDF' may be incorrect.
>> >
>> >There are 2 primary files, along with a single log
file.
>> Ive confirmed their
>> >locations and all hae their read/write attributes set
>> properly. The only
>> >other thing I can think of is permissions, which should
>> happen because I am
>> >logged into QA and EM as sa.
>> >
>> >Any other thoughts?
>> >
>> >
>> >
>> >
>> >"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in
message
>> >news:%23tRADqASEHA.3016@.tk2msftngp13.phx.gbl...
>> >> Elliot,
>> >> try ALTER DATABASE dbname SET READ_WRITE
>> >> HTH,
>> >> Paul Ibison
>> >>
>> >>
>> >
>> >
>> >.
>> >
>
>.
>