Authorization problems with dso and asp (vbscript)

Hi all,
I have an authorization problem while trying to access to Metadata informations of Analysis Services via DSO with vbscript over IIS.
Calling from Browser an ASP page with the following content:
---
dim dsoServer, dsoDatabase, dsoCube
set dsoServer = Server.CreateObject ("DSO.Server")
dsoServer.Connect ("<myServerName>")
...
--
produces the following error:
Unable to connect to the registry on the server (DSCWDMT), or you are not a member of
the OLAP Administrators group on this server.

I found in this forum a similar post, and its proposed solution (apply sp1 to AS) will not work, because I have already SP1 installed (see also article Q297232 on MSDN).

I did the following tests:
1) run examples on \Programs\Microsoft Analysis Services\Samples
Yes, they work, but without DSO. I need DSO
2) Build a dll and register it for wwwroot.
Same error message
3) follow instruction on Q224973 (MSDN)
Same error message

I have the suspect, that the installation and/or the authorizations on W2K are not correct, but I do not know what.
I run SQL2K with unthrusted authorization (i.e. with user logon and password). This use is also allowed to see cubes (at least SELECT) in Analysis services. MDX Commands work fine and I get results.

So the question is: which user is trying to access DSO Services via Internet ?
if it is IUSR_<ServerName>, than it cannot work, because it has only guest auth. on ServerName, and it would not be correct to change it.

Do you have any hint ?

Background Infos:
OS: W2K with sp2
IS: IIS 5
Database: MS SQL2K with sp1
AS: SP1
Language: Visual Basic scripting edition (VBSCRIPT)

Thanks in advance
MatteoHi,

Remember that using Windows 2000 and your web server - IIS 5.0 - all connections and all things that you use from WEB use the default Windows 2000 user account that's IIS_<machine name>...

For example, if you must use a different Regional Settings for your WEB Aplications - ASP - and set it on the server by <b>Administrator</b> account, nothing will be changed by WEB users but the account used to local permissions on IIS is the IIS account ( IIS_<machine name> or IWS_<machine name> - I don't remember now ) .

[ ]'s|||Yes, this is correct. Everything works under the account IUSR_<MachineName>.
The problem ist meanwhile solved.
The error message came because IIUSR_<MachineName> could not read Registry Entries for HKLM/SOFTWARE/Microsoft/OLAP Server/...
So I added IUSR_<MachineName> wit readonly authorisation. This solved the problem.
Anyway this cannot be a good solution, because You change punctually something without a general application rule.
So I keep on searching.

Thanks,
Matteo

Authentication methods for connections to SQL Server in ASP Pages

I am trying to access SQL Server via an ASP page using a Trusted Connection,
but it is not working. When I run the page I receive the following error
message:
Microsoft OLE DB Service Components error '80040e21'
Multiple-step OLE DB operation generated errors. Check each OLE DB status
value, if available. No work was done.
line 35
My connection string is in a separate file: cst = "data
source=X099789\Widgets;Initial Catalog=Automotive; Integrated Security=SSPI;
"
My code snipet looks like the following:
set OBJRST = Server.CreateObject("ADODB.Recordset")
Set objComm = Server.CreateObject("ADODB.Command")
objComm.ActiveConnection = cst '****LIne 35
MotorsSQL = "usp_MotorAll"
UIPWSQL = "usp_UIPW '" & struserid &"', '"& strpassword &"';"
objConnAll.open cst
What is the correct coding to connect to the SQL Server using Windows
Authentication in an ASP page?
I have read the instructions on:
http://support.microsoft.com/default.aspx/kb/247931, made the changes,
however the page still will not work.
Kindly assist. I will be thankful.
AuntieAuntieAuntie> Microsoft OLE DB Service Components error '80040e21'
> Multiple-step OLE DB operation generated errors. Check each OLE DB status
> value, if available. No work was done.
These errors are probably because there is no 'Provider' keyword in your
OLEDB connection string. Try adding 'Provider=SQLOLEDB'.
Hope this helps.
Dan Guzman
SQL Server MVP
"AuntieAuntieAuntie" <AuntieAuntieAuntie@.discussions.microsoft.com> wrote in
message news:EE4C5259-1F64-4893-9AF1-657A1C03917B@.microsoft.com...
>I am trying to access SQL Server via an ASP page using a Trusted
>Connection,
> but it is not working. When I run the page I receive the following error
> message:
> Microsoft OLE DB Service Components error '80040e21'
> Multiple-step OLE DB operation generated errors. Check each OLE DB status
> value, if available. No work was done.
> line 35
> My connection string is in a separate file: cst = "data
> source=X099789\Widgets;Initial Catalog=Automotive; Integrated
> Security=SSPI;"
> My code snipet looks like the following:
> set OBJRST = Server.CreateObject("ADODB.Recordset")
> Set objComm = Server.CreateObject("ADODB.Command")
> objComm.ActiveConnection = cst '****LIne 35
> MotorsSQL = "usp_MotorAll"
> UIPWSQL = "usp_UIPW '" & struserid &"', '"& strpassword &"';"
> objConnAll.open cst
> What is the correct coding to connect to the SQL Server using Windows
> Authentication in an ASP page?
> I have read the instructions on:
> http://support.microsoft.com/default.aspx/kb/247931, made the changes,
> however the page still will not work.
> Kindly assist. I will be thankful.
> AuntieAuntieAuntie
>

Authentication

I have 2 instance on SQL Server, One remote the other local. Both authenticate via window NT.
How can I change one or both?
To connect to a application do I setup a usergroup? Grranted this is still in the testing stages.How can I change one or both?

Huh? (Was not a long enough message...)

If they want to parse something, they should parse the questions...|||1. How can I change one or both for authenicating throw SQL Server & Windows. From the application standpoint eash database within the instance could have restricted access.

2. Most user will only have access to the data via there particular application that interfaces with the dataBASE. Would you say this would fall under the category of Usergroup Brett. Where your buddy, who always has great instinct and insigght.|||I'm sorry...

Are you saying 1 Application is connecting to multiple databases?|||No, application Users should have access through authentication of Windows NT. If it's a outside user(offsite) and I want to make fo-sure he is allowed to have access. How would that be done.|||Are you planning to change server authentication mode from Windows Only to Mixed through FE code?|||Yes, that sound like what I'm looking for. What is FE Mode?|||FE Mode? It's front-end code ;)


Authentication mode is not a dynamic property. And I don't think that "messing" with server security is a proper role for an application. Any thoughts?|||The connection string you use will determine how you authenticate to the SQL Server. If you specify a User ID and a Password, then you will be authenticated based on the SQL Login. If you supply Integrated Security=SSPI, then you get windows authentication. Windows authentication is preferred, in case you try to put both in.

From this, you could try to modify the connection logic of the application to chose a connection string on startup. Or are the people in fixed locations just on different sides of the firewall?|||Mixed authentication. Different sides of the firewall using VMware product as the base repository. It come down to setting up the agent on the user site with the proper setting. After all they dont have access through EM, Just there application.

Thanks|||It's in the profile Check out the attachments

Authenticate via Active Directory from PC that's not joined the do

I have several people on my team that are working remotely from PCs that are
stand-alone, ie. they have not been joined to the domain. They do have an
account on the domain, though, which they use to map drives and such. How
can I set it up so that they can use Enterprise Manager or Query Analyzer by
authenticating with their domain account?Hello,
I suggest that you create a SQL login for each user. You can refer to the
following article:
Adding a SQL Server Login
http://msdn.microsoft.com/library/d...-us/adminsql/ad
_security_9m0e.asp
How to set up Mixed Mode security (Enterprise Manager)
http://msdn.microsoft.com/library/d...-us/howtosql/ht
_6_secrty_68c9.asp
Make sure SQL server use mixed mode Authentication.
If the client can join the domain with a domain user account, you can add
the domain user account as a Windows login. You can refer to the following
web site:
How to grant a Windows user or group login access to SQL Server (Enterprise
Manager)
http://msdn.microsoft.com/library/d...-us/howtosql/ht
_6_secrty_68c9.asp
Granting a Windows User or Group Access to a Database
http://msdn.microsoft.com/library/d...-us/adminsql/ad
_security_2wit.asp
I hope the information is helpful.
Sophie Guo
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
========================================
=============
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.

Auditing DB Changes

It seems to me there are two common strategies for doing DB audit trails via a trigger:
1. On an update to a row, duplicate that row in another table withidentical rows, except for perhaps the extra columns which representchange date and changed by. Eg. When there is an update to the Customertable, record the changes in Customer_Audit.
2. On an update to a row, check which fields were updated. Then in acommon audit table record the table, row ID, field, previous value andnew value of the field.
I'm wondering about the pros and cons of each. More specifically, dothe pros and cons change if you are using an O/R mapper (I'm usingNHibernate.)
Some thoughts on method 1 . It seems nice for an O/R mapper, since youcould have, say, a CustomerAudit Object inherit from your Customerobject and just add the properties change date and changed by. Aproblem with this is you're going to add a whole lot of objects - onefor each object which you want to audit. Another drawback is that itcould be difficult to generate a history for a particular propertywhich was updated. Let's say I want to see the history of changes tothe customer's status. I have to load a collection of CustomerAuditobjects (which could be costly). Then I have to iterate through themand compare the status properties to generate a history of statuses.This is a pretty labor instensive method if you compare it to method 2,where the change is recorded by field, not row.
Some thoughts on method 2. It's nice since the changes are by field,not row, which (as above) makes generating a history easy. On the otherhand, you can never have a snapshot of a particular object at aparticular point in time. Moreover, I'm not sure how foreign keys wouldbe handled elegantly. I record that customer.statusID changes from 3 to6. I'd have to do a seperate join to the customerstatus table to getmeaning for 3 and 6 (which method 1 would do automatically).
Thoughts? Any preferred way to do this with an O/R mapper?
Thanks

nick7272:

It seems to me there are two common strategies for doing DB audit trails via a trigger:

1. On an update to a row, duplicate that row in another table with identical rows, except for perhaps the extra columns which represent change date and changed by. Eg. When there is an update to the Customer table, record the changes in Customer_Audit.

2. On an update to a row, check which fields were updated. Then in a common audit table record the table, row ID, field, previous value and new value of the field.
...

Nick7272 --

I am surprised there were no replies to your post.

You have the gist of the standard analysis above. Well done.

I want to pick up the topic because I am about to build yet another auditing system.

I think that there is at least one other option in addition to the ones you mention above. It is similar to similar to option 1. Note that in option 1, a developer must propogate schema changes in the tables-to-be-audited into the tables-doing-the-auditing. That can be a chore. I have implemented option 1 and it works OK for < 50 tables or so. More than that and it could be a problem if the schema changes. I am thinking that Option 3 (as yet never implemented by me) one could avoid the need for schema synchronization by using XML. Serialize an object to XML and store that. Sure, an audit recored for an object saved 1 year ago might have a different schema from an object saved 1 day ago, but all of the data would be there. I would be ugly; but, it would "mineable". It would tough to search; but, it would require no schema synchronizatoin. And so on. The problem would come from the complexity of the objects being serialized. Maybe the object could have a custom SerializeEx method that would be a lightweight version of the object, with just the necessary field names and data. Maybe. Anyway, I just wanted to mention this.

Regarding your post, I am wondering...

Since you made the post, have you discovered anything new?

What did you choose for a solution?

Please advise.

Thank you.

-- Mark Kamoski

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

attaching DAT files

**** Post for FREE via your newsreader at post.usenet.com ****
Hello,
I have solved my problem with connecting to SQL 6.5. Thank you for
answering.
Still I have two more questions.
I have copied DAT file from one computer with SQL 6.5 and put it to another
computer with SQL 6.5. How can I attach this file to database on second
computer? This file seems to beDatabase Device's file, and this is not a
backup. I have no chances to do a backup from first computer. Only I have is
Dat files from C:\MSSQL\DATA folder. Is there any solution?
And second question. I can see three database devices on my MSSQL 6.5
server. How do I know which device's databases I see?
Thank you.
Darius
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= *** Usenet.com - The #1 Usenet Newsgroup Service on The Planet! ***
http://www.usenet.com
Unlimited Download - 19 Seperate Servers - 90,000 groups - Uncensored
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=You need to use DISK REINIT and DISK REFIT. Read about the commands in Books
Online...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Darius" <darius.ram@.takas.lt> wrote in message
news:40487d61$1@.post.usenet.com...
> **** Post for FREE via your newsreader at post.usenet.com ****
> Hello,
> I have solved my problem with connecting to SQL 6.5. Thank you for
> answering.
> Still I have two more questions.
> I have copied DAT file from one computer with SQL 6.5 and put it to
another
> computer with SQL 6.5. How can I attach this file to database on second
> computer? This file seems to beDatabase Device's file, and this is not a
> backup. I have no chances to do a backup from first computer. Only I have
is
> Dat files from C:\MSSQL\DATA folder. Is there any solution?
> And second question. I can see three database devices on my MSSQL 6.5
> server. How do I know which device's databases I see?
> Thank you.
> Darius
>
> -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=> *** Usenet.com - The #1 Usenet Newsgroup Service on The Planet! ***
> http://www.usenet.com
> Unlimited Download - 19 Seperate Servers - 90,000 groups - Uncensored
> -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=|||Hi,
In SQL 6.5 it is always better to take a DUMP of the database and then copy
it to destination and Load it
1. DUMP DATABASE
2. COPY the DMP file to destination
3. LOAD DATABASE
In SQL 6.5 the commands Tiber mentioned can be used during worst cases,
Normally when master database is damaged and
incomplete master database is incomplete. This case you have the physical
files. Each physical files (DAT files)can be mapped to
sysdevices table using DISK REINIT command. After that run DISK REFIT to
recreate the SYSUSAGES table.
After REFIT stop and start SQL server service.
Thanks
Hari
MCDBA
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OQJeohrAEHA.232@.TK2MSFTNGP10.phx.gbl...
> You need to use DISK REINIT and DISK REFIT. Read about the commands in
Books
> Online...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
>
> "Darius" <darius.ram@.takas.lt> wrote in message
> news:40487d61$1@.post.usenet.com...
> > **** Post for FREE via your newsreader at post.usenet.com ****
> >
> > Hello,
> >
> > I have solved my problem with connecting to SQL 6.5. Thank you for
> > answering.
> > Still I have two more questions.
> > I have copied DAT file from one computer with SQL 6.5 and put it to
> another
> > computer with SQL 6.5. How can I attach this file to database on second
> > computer? This file seems to beDatabase Device's file, and this is not a
> > backup. I have no chances to do a backup from first computer. Only I
have
> is
> > Dat files from C:\MSSQL\DATA folder. Is there any solution?
> > And second question. I can see three database devices on my MSSQL 6.5
> > server. How do I know which device's databases I see?
> >
> > Thank you.
> >
> > Darius
> >
> >
> >
> > -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=> > *** Usenet.com - The #1 Usenet Newsgroup Service on The Planet! ***
> > http://www.usenet.com
> > Unlimited Download - 19 Seperate Servers - 90,000 groups - Uncensored
> > -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=>|||I just want to add that I fully agree with Hari. If you have the option to
use DUMP and LOAD, go for that. What we nowadays call detach and attach was
no fun thing in the old architecture...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Hari" <hari_prasad_k@.hotmail.com> wrote in message
news:OVXHN1rAEHA.688@.tk2msftngp13.phx.gbl...
> Hi,
> In SQL 6.5 it is always better to take a DUMP of the database and then
copy
> it to destination and Load it
> 1. DUMP DATABASE
> 2. COPY the DMP file to destination
> 3. LOAD DATABASE
> In SQL 6.5 the commands Tiber mentioned can be used during worst cases,
> Normally when master database is damaged and
> incomplete master database is incomplete. This case you have the physical
> files. Each physical files (DAT files)can be mapped to
> sysdevices table using DISK REINIT command. After that run DISK REFIT to
> recreate the SYSUSAGES table.
> After REFIT stop and start SQL server service.
> Thanks
> Hari
> MCDBA
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in
> message news:OQJeohrAEHA.232@.TK2MSFTNGP10.phx.gbl...
> > You need to use DISK REINIT and DISK REFIT. Read about the commands in
> Books
> > Online...
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> >
> >
> > "Darius" <darius.ram@.takas.lt> wrote in message
> > news:40487d61$1@.post.usenet.com...
> > > **** Post for FREE via your newsreader at post.usenet.com ****
> > >
> > > Hello,
> > >
> > > I have solved my problem with connecting to SQL 6.5. Thank you for
> > > answering.
> > > Still I have two more questions.
> > > I have copied DAT file from one computer with SQL 6.5 and put it to
> > another
> > > computer with SQL 6.5. How can I attach this file to database on
second
> > > computer? This file seems to beDatabase Device's file, and this is not
a
> > > backup. I have no chances to do a backup from first computer. Only I
> have
> > is
> > > Dat files from C:\MSSQL\DATA folder. Is there any solution?
> > > And second question. I can see three database devices on my MSSQL 6.5
> > > server. How do I know which device's databases I see?
> > >
> > > Thank you.
> > >
> > > Darius
> > >
> > >
> > >
> > > -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=> > > *** Usenet.com - The #1 Usenet Newsgroup Service on The Planet! ***
> > > http://www.usenet.com
> > > Unlimited Download - 19 Seperate Servers - 90,000 groups - Uncensored
> > > -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=> >
> >
>

attaching DAT files

**** Post for FREE via your newsreader at post.mcse.ms ****
Hello,
I have solved my problem with connecting to SQL 6.5. Thank you for
answering.
Still I have two more questions.
I have copied DAT file from one computer with SQL 6.5 and put it to another
computer with SQL 6.5. How can I attach this file to database on second
computer? This file seems to beDatabase Device's file, and this is not a
backup. I have no chances to do a backup from first computer. Only I have is
Dat files from C:\MSSQL\DATA folder. Is there any solution?
And second question. I can see three database devices on my MSSQL 6.5
server. How do I know which device's databases I see?
Thank you.
Darius
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
*** mcse.ms - The #1 Usenet Newsgroup Service on The Planet! ***
http://www.mcse.ms
Unlimited Download - 19 Seperate Servers - 90,000 groups - Uncensored
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=You need to use DISK REINIT and DISK REFIT. Read about the commands in Books
Online...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Darius" <darius.ram@.takas.lt> wrote in message
news:40487d61$1@.post.mcse.ms...
> **** Post for FREE via your newsreader at post.mcse.ms ****
> Hello,
> I have solved my problem with connecting to SQL 6.5. Thank you for
> answering.
> Still I have two more questions.
> I have copied DAT file from one computer with SQL 6.5 and put it to
another
> computer with SQL 6.5. How can I attach this file to database on second
> computer? This file seems to beDatabase Device's file, and this is not a
> backup. I have no chances to do a backup from first computer. Only I have
is
> Dat files from C:\MSSQL\DATA folder. Is there any solution?
> And second question. I can see three database devices on my MSSQL 6.5
> server. How do I know which device's databases I see?
> Thank you.
> Darius
>
> -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
> *** mcse.ms - The #1 Usenet Newsgroup Service on The Planet! ***
> http://www.mcse.ms
> Unlimited Download - 19 Seperate Servers - 90,000 groups - Uncensored
> -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=|||Hi,
In SQL 6.5 it is always better to take a DUMP of the database and then copy
it to destination and Load it
1. DUMP DATABASE
2. COPY the DMP file to destination
3. LOAD DATABASE
In SQL 6.5 the commands Tiber mentioned can be used during worst cases,
Normally when master database is damaged and
incomplete master database is incomplete. This case you have the physical
files. Each physical files (DAT files)can be mapped to
sysdevices table using DISK REINIT command. After that run DISK REFIT to
recreate the SYSUSAGES table.
After REFIT stop and start SQL server service.
Thanks
Hari
MCDBA
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OQJeohrAEHA.232@.TK2MSFTNGP10.phx.gbl...
> You need to use DISK REINIT and DISK REFIT. Read about the commands in
Books
> Online...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
>
> "Darius" <darius.ram@.takas.lt> wrote in message
> news:40487d61$1@.post.mcse.ms...
> another
have
> is
>|||I just want to add that I fully agree with Hari. If you have the option to
use DUMP and LOAD, go for that. What we nowadays call detach and attach was
no fun thing in the old architecture...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Hari" <hari_prasad_k@.hotmail.com> wrote in message
news:OVXHN1rAEHA.688@.tk2msftngp13.phx.gbl...
> Hi,
> In SQL 6.5 it is always better to take a DUMP of the database and then
copy
> it to destination and Load it
> 1. DUMP DATABASE
> 2. COPY the DMP file to destination
> 3. LOAD DATABASE
> In SQL 6.5 the commands Tiber mentioned can be used during worst cases,
> Normally when master database is damaged and
> incomplete master database is incomplete. This case you have the physical
> files. Each physical files (DAT files)can be mapped to
> sysdevices table using DISK REINIT command. After that run DISK REFIT to
> recreate the SYSUSAGES table.
> After REFIT stop and start SQL server service.
> Thanks
> Hari
> MCDBA
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in
> message news:OQJeohrAEHA.232@.TK2MSFTNGP10.phx.gbl...
> Books
second
a
> have
>|||**** Post for FREE via your newsreader at post.mcse.ms ****
Hello,
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OQJeohrAEHA.232@.TK2MSFTNGP10.phx.gbl...
> You need to use DISK REINIT and DISK REFIT. Read about the commands in
Books
> Online...
I have done everything as explained in example, but I encountered errors
such as "can't open file" or "start server with -m options". I don't know
what to do? There is no way to get a database dump. I have only *.dat files
Darius
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
*** mcse.ms - The #1 Usenet Newsgroup Service on The Planet! ***
http://www.mcse.ms
Unlimited Download - 19 Seperate Servers - 90,000 groups - Uncensored
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=|||As I said, this is not simple in the old architecture. All information that
is needed *is* available in Books Online. However, if you still encounter
problems, I recommend that you hire a consultant who know how to do this or
open a case with MS Support to assist you with this.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Darius" <darius.ram@.takas.lt> wrote in message
news:404c35d1@.post.mcse.ms...
> **** Post for FREE via your newsreader at post.mcse.ms ****
> Hello,
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in
> message news:OQJeohrAEHA.232@.TK2MSFTNGP10.phx.gbl...
> Books
> I have done everything as explained in example, but I encountered errors
> such as "can't open file" or "start server with -m options". I don't know
> what to do? There is no way to get a database dump. I have only *.dat
files
> Darius
>
> -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
> *** mcse.ms - The #1 Usenet Newsgroup Service on The Planet! ***
> http://www.mcse.ms
> Unlimited Download - 19 Seperate Servers - 90,000 groups - Uncensored
> -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=

attach/detach db by user with no priviliges

Hello,
I am looking for a way to have users with normal security (= not system
admin and so on) to attach and detach a specific database. Maybe via a
stored proc. Does anyone have an idea?
Thank you
Frank"Frank" schrieb:
> Hello,
> I am looking for a way to have users with normal security (= not system
> admin and so on) to attach and detach a specific database. Maybe via a
> stored proc. Does anyone have an idea?
> Thank you
> Frank
If the users are members of the dbcreator server role they can do it
(symin is not necessary).
If the users are not supposed to be member of any serverroles then I see no
solution - the right to create a db is needed ... (of xourse you could also
to give the users statement permission to create a db in the master, and the
n
write a stored proc that takes the same parameters like sp_attach_db and
executes the sp_attach_db for the user - watch out for a broken ownership
chain! But if you are ready to go that far, you can also add them to the
dbcreators server role.)

attach/deattach database

is it possible to create a dts job or sproc that can be launched via a vbs
file or wsh script that:
1. unattaches a sql 2000 database
2. copies it to a removable drive
3. re-attaches original db
4. and does opposite at office to keep a sql in sync?
any ideas on best method and syntax would be appreciated.Scott,
If youo are trying to have a remote database that is in sync, or at least
is in syn some what, is it possible to use replication? A snap shot
replication using the SQL Server as both the publisher and the distributor
can create a push a snapshot of the entire database, or selected tables from
the database. If the databases are not connected (I noted that you want to
use a removeable drive) then a process can be developed to copy the files to
a drive and then mount the databases on the remote server. Here is just one
method:
1. Create a job that backups up the database
2. Create a DTS package that will copy the backup files to the removable
drive.
3. Step 2 of the job would be to execute the DTS package in step 2.
4. Do the reverse on the remote server but in place of the backup create a
job that restores the database.
Detaching and reattaching can be an option as well as long are not
connections to the database when it is detached. A Backup can be done
regardless if the database is in use or not.
Hope this helps.
Rick
"scott" wrote:
> is it possible to create a dts job or sproc that can be launched via a vbs
> file or wsh script that:
> 1. unattaches a sql 2000 database
> 2. copies it to a removable drive
> 3. re-attaches original db
> 4. and does opposite at office to keep a sql in sync?
> any ideas on best method and syntax would be appreciated.
>
>