Showing posts with label databases. Show all posts
Showing posts with label databases. Show all posts

Friday, March 30, 2012

How do I Merge two databases?

I have two databases with each one on a different server. I can copy
both databases to the same server with no problems. But how do I merge
them?

This is what I came up with so far but how do I do the same process for
multiple rows and tables?

IF NOT EXISTS (SELECT tmp_DB1.dbo.tb1.key1 FROM tmpDB1.dbo.tb1 WHERE
tmp_DB1.dbo.tb1.key1 = tmp_DB2.dbo.tb1.key1)
INSERT INTO tmp_DB1.dbo.tb1 (<all fields>)
VALUES (<all fields>)

Thanks in advance.You probably want to do something like this (insert into a new table only
the rows that don't already exist):

INSERT INTO DB1.dbo.TargetTable (key_col, col1, col2, ...)
SELECT S.key_col, S.col1, S.col2, ...
FROM DB2.dbo.SourceTable AS S
LEFT JOIN DB1.dbo.TargetTable AS T
ON S.key_col = T.key_col
WHERE T.key_col IS NULL

Obviously this won't be useful if Key_col is an IDENTITY column. Use the
natural key of your table(s).

--
David Portas
SQL Server MVP
--|||Thanks, it works great. Now, I just have to do this for about 20 more
tables. :)sql

Monday, March 26, 2012

How do I kill a restore?

Question: I have a few databases that were told to restore via a
script. They've been stuck in a 'Restoring...' state for two days now,
and I need to kill it.
What can I do from administration standpoint to stop this?
Thanks,
MichaelThe restore process is probably done by now, and the databases are left in t
he restoring state
(either because the restore process was terminated, or because you restore u
sing the NORECOVERY
option). If it is the later, try:
RESTORE DATABASE dbname WITH RECOVERY
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Michael Gorsuch" <michael.gorsuch@.gmail.com> wrote in message
news:1162563793.422412.281130@.m73g2000cwd.googlegroups.com...
> Question: I have a few databases that were told to restore via a
> script. They've been stuck in a 'Restoring...' state for two days now,
> and I need to kill it.
> What can I do from administration standpoint to stop this?
> Thanks,
> Michael
>|||I believe it is the former - when I try and run "RESTORE DATABASE
trial75441 WITH RECOVERY", I receive the following:
Msg 4319, Level 16, State 3, Line 1
A previous restore operation was interrupted and did not complete
processing on file 'trial75441_log'. Either restore the backup set that
was interrupted or restart the restore sequence.
Is there a way to force this restore to terminate via a command? If
not, what command allows me to restart?
Again, I apologize for my ignorance. I've away from the Windows
sysadmin side for a couple of years, and am now getting back into it
heavily. By the way, is there a good book that you can recommend for
running SQL Server 2005?
On Nov 3, 9:58 am, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:[vbcol=seagreen]
> The restore process is probably done by now, and the databases are left in
the restoring state
> (either because the restore process was terminated, or because you restore
using the NORECOVERY
> option). If it is the later, try:
> RESTORE DATABASE dbname WITH RECOVERY
> --
> Tibor Karaszi, SQL Server MVPhttp://www.karaszi.com/sqlserver/default.asph
ttp://www.solidqualitylearning.com/
> "Michael Gorsuch" <michael.gors...@.gmail.com> wrote in messagenews:1162563
793.422412.281130@.m73g2000cwd.googlegroups.com...
>
>
>
>|||Since the RESTORE process creates the database for you, the easiest way is p
robably to just DROP
DATABASE and then start the restore operation from scratch.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Michael Gorsuch" <michael.gorsuch@.gmail.com> wrote in message
news:1162567958.824002.311440@.k70g2000cwa.googlegroups.com...
>I believe it is the former - when I try and run "RESTORE DATABASE
> trial75441 WITH RECOVERY", I receive the following:
> Msg 4319, Level 16, State 3, Line 1
> A previous restore operation was interrupted and did not complete
> processing on file 'trial75441_log'. Either restore the backup set that
> was interrupted or restart the restore sequence.
> Is there a way to force this restore to terminate via a command? If
> not, what command allows me to restart?
> Again, I apologize for my ignorance. I've away from the Windows
> sysadmin side for a couple of years, and am now getting back into it
> heavily. By the way, is there a good book that you can recommend for
> running SQL Server 2005?
> On Nov 3, 9:58 am, "Tibor Karaszi"
> <tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
>|||I just had the same problem and doing what Tibor Karaszi mentioned
worked.
I dropped the databases that were created by the failed restore and
re-ran the restore script and it worked the 2nd time around.
Tibor Karaszi wrote:[vbcol=seagreen]
> Since the RESTORE process creates the database for you, the easiest way is
probably to just DROP
> DATABASE and then start the restore operation from scratch.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Michael Gorsuch" <michael.gorsuch@.gmail.com> wrote in message
> news:1162567958.824002.311440@.k70g2000cwa.googlegroups.com...|||Perfect guys. I did that, and I'm in good shape.
Thank you all for your help.
On Nov 3, 11:38 am, "Hulicat" <dennis_A_wh...@.yahoo.com> wrote:[vbcol=seagreen]
> I just had the same problem and doing what Tibor Karaszi mentioned
> worked.
> I dropped the databases that were created by the failed restore and
> re-ran the restore script and it worked the 2nd time around.
> Tibor Karaszi wrote:
>
>
>
>
>
>
>
>
>
>
>
>
>

How do I kill a restore?

Question: I have a few databases that were told to restore via a
script. They've been stuck in a 'Restoring...' state for two days now,
and I need to kill it.
What can I do from administration standpoint to stop this?
Thanks,
Michael
I believe it is the former - when I try and run "RESTORE DATABASE
trial75441 WITH RECOVERY", I receive the following:
Msg 4319, Level 16, State 3, Line 1
A previous restore operation was interrupted and did not complete
processing on file 'trial75441_log'. Either restore the backup set that
was interrupted or restart the restore sequence.
Is there a way to force this restore to terminate via a command? If
not, what command allows me to restart?
Again, I apologize for my ignorance. I've away from the Windows
sysadmin side for a couple of years, and am now getting back into it
heavily. By the way, is there a good book that you can recommend for
running SQL Server 2005?
On Nov 3, 9:58 am, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:[vbcol=seagreen]
> The restore process is probably done by now, and the databases are left in the restoring state
> (either because the restore process was terminated, or because you restore using the NORECOVERY
> option). If it is the later, try:
> RESTORE DATABASE dbname WITH RECOVERY
> --
> Tibor Karaszi, SQL Server MVPhttp://www.karaszi.com/sqlserver/default.asphttp://www.solidqualitylearning.com/
> "Michael Gorsuch" <michael.gors...@.gmail.com> wrote in messagenews:1162563793.422412.281130@.m73g2000cwd.g ooglegroups.com...
>
>
|||I just had the same problem and doing what Tibor Karaszi mentioned
worked.
I dropped the databases that were created by the failed restore and
re-ran the restore script and it worked the 2nd time around.
Tibor Karaszi wrote:[vbcol=seagreen]
> Since the RESTORE process creates the database for you, the easiest way is probably to just DROP
> DATABASE and then start the restore operation from scratch.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Michael Gorsuch" <michael.gorsuch@.gmail.com> wrote in message
> news:1162567958.824002.311440@.k70g2000cwa.googlegr oups.com...
|||Perfect guys. I did that, and I'm in good shape.
Thank you all for your help.
On Nov 3, 11:38 am, "Hulicat" <dennis_A_wh...@.yahoo.com> wrote:[vbcol=seagreen]
> I just had the same problem and doing what Tibor Karaszi mentioned
> worked.
> I dropped the databases that were created by the failed restore and
> re-ran the restore script and it worked the 2nd time around.
> Tibor Karaszi wrote:
>
>
>
>
>
>

How do I kill a restore?

Question: I have a few databases that were told to restore via a
script. They've been stuck in a 'Restoring...' state for two days now,
and I need to kill it.
What can I do from administration standpoint to stop this?
Thanks,
MichaelThe restore process is probably done by now, and the databases are left in the restoring state
(either because the restore process was terminated, or because you restore using the NORECOVERY
option). If it is the later, try:
RESTORE DATABASE dbname WITH RECOVERY
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Michael Gorsuch" <michael.gorsuch@.gmail.com> wrote in message
news:1162563793.422412.281130@.m73g2000cwd.googlegroups.com...
> Question: I have a few databases that were told to restore via a
> script. They've been stuck in a 'Restoring...' state for two days now,
> and I need to kill it.
> What can I do from administration standpoint to stop this?
> Thanks,
> Michael
>|||I believe it is the former - when I try and run "RESTORE DATABASE
trial75441 WITH RECOVERY", I receive the following:
Msg 4319, Level 16, State 3, Line 1
A previous restore operation was interrupted and did not complete
processing on file 'trial75441_log'. Either restore the backup set that
was interrupted or restart the restore sequence.
Is there a way to force this restore to terminate via a command? If
not, what command allows me to restart?
Again, I apologize for my ignorance. I've away from the Windows
sysadmin side for a couple of years, and am now getting back into it
heavily. By the way, is there a good book that you can recommend for
running SQL Server 2005?
On Nov 3, 9:58 am, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> The restore process is probably done by now, and the databases are left in the restoring state
> (either because the restore process was terminated, or because you restore using the NORECOVERY
> option). If it is the later, try:
> RESTORE DATABASE dbname WITH RECOVERY
> --
> Tibor Karaszi, SQL Server MVPhttp://www.karaszi.com/sqlserver/default.asphttp://www.solidqualitylearning.com/
> "Michael Gorsuch" <michael.gors...@.gmail.com> wrote in messagenews:1162563793.422412.281130@.m73g2000cwd.googlegroups.com...
> > Question: I have a few databases that were told to restore via a
> > script. They've been stuck in a 'Restoring...' state for two days now,
> > and I need to kill it.
> > What can I do from administration standpoint to stop this?
> > Thanks,
> > Michael|||Since the RESTORE process creates the database for you, the easiest way is probably to just DROP
DATABASE and then start the restore operation from scratch.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Michael Gorsuch" <michael.gorsuch@.gmail.com> wrote in message
news:1162567958.824002.311440@.k70g2000cwa.googlegroups.com...
>I believe it is the former - when I try and run "RESTORE DATABASE
> trial75441 WITH RECOVERY", I receive the following:
> Msg 4319, Level 16, State 3, Line 1
> A previous restore operation was interrupted and did not complete
> processing on file 'trial75441_log'. Either restore the backup set that
> was interrupted or restart the restore sequence.
> Is there a way to force this restore to terminate via a command? If
> not, what command allows me to restart?
> Again, I apologize for my ignorance. I've away from the Windows
> sysadmin side for a couple of years, and am now getting back into it
> heavily. By the way, is there a good book that you can recommend for
> running SQL Server 2005?
> On Nov 3, 9:58 am, "Tibor Karaszi"
> <tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
>> The restore process is probably done by now, and the databases are left in the restoring state
>> (either because the restore process was terminated, or because you restore using the NORECOVERY
>> option). If it is the later, try:
>> RESTORE DATABASE dbname WITH RECOVERY
>> --
>> Tibor Karaszi, SQL Server
>> MVPhttp://www.karaszi.com/sqlserver/default.asphttp://www.solidqualitylearning.com/
>> "Michael Gorsuch" <michael.gors...@.gmail.com> wrote in
>> messagenews:1162563793.422412.281130@.m73g2000cwd.googlegroups.com...
>> > Question: I have a few databases that were told to restore via a
>> > script. They've been stuck in a 'Restoring...' state for two days now,
>> > and I need to kill it.
>> > What can I do from administration standpoint to stop this?
>> > Thanks,
>> > Michael
>|||I just had the same problem and doing what Tibor Karaszi mentioned
worked.
I dropped the databases that were created by the failed restore and
re-ran the restore script and it worked the 2nd time around.
Tibor Karaszi wrote:
> Since the RESTORE process creates the database for you, the easiest way is probably to just DROP
> DATABASE and then start the restore operation from scratch.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Michael Gorsuch" <michael.gorsuch@.gmail.com> wrote in message
> news:1162567958.824002.311440@.k70g2000cwa.googlegroups.com...
> >I believe it is the former - when I try and run "RESTORE DATABASE
> > trial75441 WITH RECOVERY", I receive the following:
> >
> > Msg 4319, Level 16, State 3, Line 1
> > A previous restore operation was interrupted and did not complete
> > processing on file 'trial75441_log'. Either restore the backup set that
> > was interrupted or restart the restore sequence.
> >
> > Is there a way to force this restore to terminate via a command? If
> > not, what command allows me to restart?
> >
> > Again, I apologize for my ignorance. I've away from the Windows
> > sysadmin side for a couple of years, and am now getting back into it
> > heavily. By the way, is there a good book that you can recommend for
> > running SQL Server 2005?
> >
> > On Nov 3, 9:58 am, "Tibor Karaszi"
> > <tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> >> The restore process is probably done by now, and the databases are left in the restoring state
> >> (either because the restore process was terminated, or because you restore using the NORECOVERY
> >> option). If it is the later, try:
> >>
> >> RESTORE DATABASE dbname WITH RECOVERY
> >>
> >> --
> >> Tibor Karaszi, SQL Server
> >> MVPhttp://www.karaszi.com/sqlserver/default.asphttp://www.solidqualitylearning.com/
> >>
> >> "Michael Gorsuch" <michael.gors...@.gmail.com> wrote in
> >> messagenews:1162563793.422412.281130@.m73g2000cwd.googlegroups.com...
> >>
> >> > Question: I have a few databases that were told to restore via a
> >> > script. They've been stuck in a 'Restoring...' state for two days now,
> >> > and I need to kill it.
> >>
> >> > What can I do from administration standpoint to stop this?
> >>
> >> > Thanks,
> >>
> >> > Michael
> >|||Perfect guys. I did that, and I'm in good shape.
Thank you all for your help.
On Nov 3, 11:38 am, "Hulicat" <dennis_A_wh...@.yahoo.com> wrote:
> I just had the same problem and doing what Tibor Karaszi mentioned
> worked.
> I dropped the databases that were created by the failed restore and
> re-ran the restore script and it worked the 2nd time around.
> Tibor Karaszi wrote:
> > Since the RESTORE process creates the database for you, the easiest way is probably to just DROP
> > DATABASE and then start the restore operation from scratch.
> > --
> > Tibor Karaszi, SQL Server MVP
> >http://www.karaszi.com/sqlserver/default.asp
> >http://www.solidqualitylearning.com/
> > "Michael Gorsuch" <michael.gors...@.gmail.com> wrote in message
> >news:1162567958.824002.311440@.k70g2000cwa.googlegroups.com...
> > >I believe it is the former - when I try and run "RESTORE DATABASE
> > > trial75441 WITH RECOVERY", I receive the following:
> > > Msg 4319, Level 16, State 3, Line 1
> > > A previous restore operation was interrupted and did not complete
> > > processing on file 'trial75441_log'. Either restore the backup set that
> > > was interrupted or restart the restore sequence.
> > > Is there a way to force this restore to terminate via a command? If
> > > not, what command allows me to restart?
> > > Again, I apologize for my ignorance. I've away from the Windows
> > > sysadmin side for a couple of years, and am now getting back into it
> > > heavily. By the way, is there a good book that you can recommend for
> > > running SQL Server 2005?
> > > On Nov 3, 9:58 am, "Tibor Karaszi"
> > > <tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> > >> The restore process is probably done by now, and the databases are left in the restoring state
> > >> (either because the restore process was terminated, or because you restore using the NORECOVERY
> > >> option). If it is the later, try:
> > >> RESTORE DATABASE dbname WITH RECOVERY
> > >> --
> > >> Tibor Karaszi, SQL Server
> > >> MVPhttp://www.karaszi.com/sqlserver/default.asphttp://www.solidqualityle...
> > >> "Michael Gorsuch" <michael.gors...@.gmail.com> wrote in
> > >> messagenews:1162563793.422412.281130@.m73g2000cwd.googlegroups.com...
> > >> > Question: I have a few databases that were told to restore via a
> > >> > script. They've been stuck in a 'Restoring...' state for two days now,
> > >> > and I need to kill it.
> > >> > What can I do from administration standpoint to stop this?
> > >> > Thanks,
> > >> > Michaelsql

How do I join tables from different databases (under same server though)

Hey, I have two databases (db1 and db2) under the same server. How do I combine tables from both of them?

I searched the forum and tried

SELECT

t1.col1

,t2.col2

FROM

db1.dbo.table1 t1

INNER JOIN

db2.dbo.table2 t2

ON t1.somecol = t2.somecol

I get Error Message: Invalid object name 'db1.dbo.table1
Error source: .Net SqlClient Data Provider

I read somewhere that the connectionstring could cause the problem since I'm trying to access two databases. I'm using SQL Server Express

<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\db1.mdf;Integrated Security=True;User Instance=True"
ProviderName="System.Data.SqlClient" SelectCommand="SELECT t1.col1, t2.col2 FROM db1.dbo.table1 t1 INNER JOIN db2.dbo.table2 t2 ON t1.somecol = t2.somecol"></asp:SqlDataSource>


Anyway, how can I solve this?ahhh comon, someone must know a solution|||

I guess that may because the 2 databases can not 'recognize' each other. Have you ever tried register the 2 databases into SQL Express by Detach/Attach? You can firstly detach the databases from your project, and then attach them in Management Studio:

http://msdn2.microsoft.com/en-us/library/ms190209.aspx

|||thanks for the idea, i'll look into it and see what I can find out.sql

How do i join 2 databases on seperate servers?

Trying to extract data from two databases on different servers... I know how
to write Select statements to get data from them seperately but have not
managed to join the two together.
Can anyone help with this issue?
Thank you
ck
use the four-part-notation
SELECT Somecolumns
>From SomelocalTable T1
INNER JOIN LinkedServername.Databasename.Ownername.SomeTableN ame T1
ON T1.SomejoinColumn = T2.SomejoinColumn
HTH, Jens Suessmeyer.

How do i join 2 databases on seperate servers?

Trying to extract data from two databases on different servers... I know how
to write Select statements to get data from them seperately but have not
managed to join the two together.
Can anyone help with this issue?
Thank you
ckuse the four-part-notation
SELECT Somecolumns
>From SomelocalTable T1
INNER JOIN LinkedServername.Databasename.Ownername.SomeTableName T1
ON T1.SomejoinColumn = T2.SomejoinColumn
HTH, Jens Suessmeyer.

Wednesday, March 21, 2012

How do I hide databases that I don't need to see?

Hello,
How do I hide databases that I don't need to see on a server? I am using
SQL Server Management Studio 2005.
Thanks!
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Best free software
http://www.vbmark.com
Freeware-dedicated search engine
http://www.eurekster.com/parties/Freeware1.htm
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-I don't think you can hide them in management studio, but you can
restrict access to them
by using
deny connect to guest.
So that only authorised user to particular database can access it.
Regards
Amish Shah|||"amish" <shahamishm@.gmail.com> wrote in news:1138703226.471823.58290
@.g43g2000cwa.googlegroups.com:

> I don't think you can hide them in management studio, but you can
> restrict access to them
> by using
> deny connect to guest.
> So that only authorised user to particular database can access it.
> Regards
> Amish Shah
>
Thank you Amish.
P.S. Cool name!
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Best free software
http://www.vbmark.com
Freeware-dedicated search engine
http://www.eurekster.com/parties/Freeware1.htm
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-|||Thanks
Amish is indian name, meaning is honesty and love.
Regards
Amish Shah

How do I hide databases that I don't need to see?

Hello,
How do I hide databases that I don't need to see on a server? I am using
SQL Server Management Studio 2005.
Thanks!
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Best free software
http://www.vbmark.com
Freeware-dedicated search engine
http://www.eurekster.com/parties/Freeware1.htm
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
I don't think you can hide them in management studio, but you can
restrict access to them
by using
deny connect to guest.
So that only authorised user to particular database can access it.
Regards
Amish Shah
|||"amish" <shahamishm@.gmail.com> wrote in news:1138703226.471823.58290
@.g43g2000cwa.googlegroups.com:

> I don't think you can hide them in management studio, but you can
> restrict access to them
> by using
> deny connect to guest.
> So that only authorised user to particular database can access it.
> Regards
> Amish Shah
>
Thank you Amish.
P.S. Cool name!
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Best free software
http://www.vbmark.com
Freeware-dedicated search engine
http://www.eurekster.com/parties/Freeware1.htm
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
|||Thanks
Amish is indian name, meaning is honesty and love.
Regards
Amish Shah

How do I hide databases that I don't need to see?

Hello,
How do I hide databases that I don't need to see on a server? I am using
SQL Server Management Studio 2005.
Thanks!
--
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Best free software
http://www.vbmark.com
Freeware-dedicated search engine
http://www.eurekster.com/parties/Freeware1.htm
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-I don't think you can hide them in management studio, but you can
restrict access to them
by using
deny connect to guest.
So that only authorised user to particular database can access it.
Regards
Amish Shah|||"amish" <shahamishm@.gmail.com> wrote in news:1138703226.471823.58290
@.g43g2000cwa.googlegroups.com:
> I don't think you can hide them in management studio, but you can
> restrict access to them
> by using
> deny connect to guest.
> So that only authorised user to particular database can access it.
> Regards
> Amish Shah
>
Thank you Amish.
P.S. Cool name!
--
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Best free software
http://www.vbmark.com
Freeware-dedicated search engine
http://www.eurekster.com/parties/Freeware1.htm
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-|||Thanks
Amish is indian name, meaning is honesty and love.
Regards
Amish Shah

How do I Hide Databases from External Users?

I am going to give user rights for an external user to connect to my SQL Server via Client Network Utility.

Atlough I have given user permissions to only access one database and not the whole list, how do I make sure that they cannot see all the other databases on my SQL Server?

I have 20 instances of databases on my SQL Server and ideally I would like to give 20 different people access - but each of them when they enter my SQL Server, should not even know that the other databases exist.

Thanks.RE:
Q1 I am going to give user rights for an external user to connect to my SQL Server via Client Network Utility.

Q2 Although I have given user permissions to only access one database and not the whole list, how do I make sure that they cannot see all the other databases on my SQL Server?

I have 20 instances of databases on my SQL Server and ideally I would like to give 20 different people access -

Q3 but each of them when they enter my SQL Server, should not even know that the other databases exist. Thanks.

A1 {What was meant is not 100% clear; however, bestowing "[user rights for an external user to connect to SQL Server]" would generally be accomplished within Enterprise Manager, *Query Analyser, osql, isql, or programmatically, i.e.(via an API: ADO, CLib, DMO, OLEDB, etc.,); but not via the Client Network Utility (at least not directly)?}

A2 One may meet such requirements by designing the user application(s), i.e.(the ones the users will use with their respective DBs), such that other DB catalogs are not displayed. (This is arguably probably one of the better means of meeting such a requirement.)

Note: Various "all encompassing" implementation approaches may entail significant negative consequences. For example this includes many implementations that:
a Deny users Select and Exec rights to certain objects in the Master DB.
b Modify / add objects to the Master DB (this option especially, is NOT recommended).
c Require special connectivity software that supports the desired feature set (custom made ODBC, Sql Server, OLEDB, etc., drivers).

A3 You may wish to verify that this is in fact an important requirement. (That is, one that is worth the ramifications of the means chosen to implement a design that will meet the requirement.) As noted in A2, maintainability and / or functionality may be impaired with some kinds of "DB hiding" implementation approaches.

--

* The following Special Stored Procedures are supported in MS Sql Server 2k (execute from QA, etc.) for managing security:
sp_addalias
sp_addapprole
sp_addgroup
sp_addlinkedsrvlogin
sp_addlogin
sp_addremotelogin
sp_addrole
sp_addrolemember
sp_addserver
sp_addsrvrolemember
sp_adduser
sp_approlepassword
sp_change_users_login
sp_changedbowner
sp_changegroup
sp_changeobjectowner
sp_dbfixedrolepermission
sp_defaultdb
sp_defaultlanguage
sp_denylogin
sp_dropalias
sp_dropapprole
sp_dropgroup
sp_droplinkedsrvlogin
sp_droplogin
sp_dropremotelogin
sp_droprole
sp_droprolemember
sp_dropserver
sp_dropsrvrolemember
sp_dropuser
sp_grantdbaccess
sp_grantlogin
sp_helpdbfixedrole
sp_helpgroup
sp_helplinkedsrvlogin
sp_helplogins
sp_helpntgroup
sp_helpremotelogin
sp_helprole
sp_helprolemember
sp_helprotect
sp_helpsrvrole
sp_helpsrvrolemember
sp_helpuser
sp_password
sp_remoteoption
sp_revokedbaccess
sp_revokelogin
sp_setapprole
sp_srvrolepermission
sp_validatelogins|||With what tools will your users be connecting to your Server? ODBC, OSQL, Enterprise Manager?|||Originally posted by Paul Young
With what tools will your users be connecting to your Server? ODBC, OSQL, Enterprise Manager?

Thank you for your posts :)

I am using EM to connect|||Great, what will your users use to connect?

If your users will use EM or Query Analyzer then they have enough to see the diffrent DBs with little to no effort. A knowlegable user can even see system tables via I/OSQL. Past that, in ODBC a use will still be able to get a list of DB's if they specify a database when setting up the DSN.sql

Monday, March 12, 2012

How do I force new databases to be created on another drive on same server?

I successfuly installed SQL Server 2005 Enterprise Edition today (on the first shot!) but can't figure out how to force new databases, etc to be created on another drive on the server. SQL installed on the c: drive (I had no choice!) but it is a very small partition. I need to install Sharepoint Portal 2003 next (on a different server) and want to make sure everything goes on the other drive.

Help me, please!!

Thanks :-)

As far as I can remember Sharepoint let you choose the destination of the data files. Anyway the default data directory can be changed in the registry:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\Setup

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de
|||Thanks, I'll give it a try.|||

I just tried it and it is still creating new databases on c: drive. We had to slick the server for another reason, so I have a fresh install and told it to install to d:\Program Files\.... during installation and the registry entry did not change; it still pointed to c: drive. I created a d:\Program Files\Microsoft SQL Server\Data directory and changed the registry and no joy. Even restarted the server a few times and it didn't matter.

Shouldn't there be a way to change this default in the Server Management Studio?!?

If any can help me, it would be appreciated! This problem is holding up installation of a few more apps.

Thanks!!

|||Sorry for that I copied the wrong key :-(

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=407351&SiteID=1

HTH, jens Suessmeyer.

http://www.sqlserver2005.de
|||

Hi,

Thanks. I actually found out where to do it in the SQL Server Management Studio!

SERVER--PROPERTIES--DATABASE SETTINGS

The path for databases and logs are blank. After I selected my folders on the D: drive I created a new db and it went to the correct path! Whew. Glad that's over with :-)

Wednesday, March 7, 2012

How do I export my database from SQL Server Management Studio Express

Hi

I am using SQL Server Management Studio Express to make my databases but I noticed if I make my database in Visual studios and go new Item and make a new database I see it in app_data folder and server express tab and if I make it in SQL Server Management Studio Express I only see it in the server explorer.

So if I have to move my files to another computer how do I move my database easly with SQL Server Management Studio Express? Since when you make it with the visual studio the file gets stored with all the other files of your project so if you move it all to another computer you prob won't run into a problem.


So how do I make it that so I can do everything in SQL Server Management Studio Express(since I like working in it) then export it into a file that I can then go into my app_data folder and add it is an exist item?

Thanks

Hi chobo2,

Generally speaking, when you create a new database through Management Studio, the database file will be stored at: %yoursqlinstallationpath%MSSQL.1\MSSQL\Data. If you want to access it through your Visual Studio you must first connect to it(server exploer--> new connection).

And if you create a new database through Visual Studio, the database file will be stored in you application App_Data folder.You can access to it directly through both Visual Studio and Management Studio.

As to export a database file to new location(or migrate databae), as far as i know, you have 2 options:

1: Detach and Attach:

Detach the database from original database Server and copy the physical database files(*.mdf and *.ldf) to the new location.At the new server side, you put the physical database files to a certain place(generally speaking it's the default sql server database folder), and attach to the database again;

2:Use Backup and Restore:

In your original database server you can make a backup of your database file and store it to a certain place(*.bak).Take the *.bak file to the new server and in the new database server, restore that database.

You can implement the above 2 methods in sql2000, sql express, or sql 2005. However, detailed operations could be different if you use different verstions of sql server.

Hope my suggestion helps