Hide Databases

Hi Folks,
I=B4m sharing a SQL Server 2000 SP3a with some users.
These users are using Enterprise Manager and Query=20
Analyzer at client side. It=B4s working well.
My problem is: In Enterprise Manager view, users can see
all databases, but only can manipulate your database.
My Question: In SQL Properties, How can I hide Databases=20
to non-owners ?
Thanks
Alex Decarli.This is not possible in SQL2000 Enterprise Manager. Metadata security is
pretty lax.You can choose to hide System databases if you wish , by going
into Edit SQL Server Registration Properties and uncheck Show system
databases and system objects
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Alex Decarli" <anonymous@.discussions.microsoft.com> wrote in message
news:079401c3dc1a$9849ce20$a501280a@.phx.gbl...
Hi Folks,
Im sharing a SQL Server 2000 SP3a with some users.
These users are using Enterprise Manager and Query
Analyzer at client side. Its working well.
My problem is: In Enterprise Manager view, users can see
all databases, but only can manipulate your database.
My Question: In SQL Properties, How can I hide Databases
to non-owners ?
Thanks
Alex Decarli.

Hide databases in SQL EM from dbo owner

If i create a new SQL login and give that login db ownership for a test db.
When that user adds my SQL Server through his own SQL EM, why does he/she
also see all other objects (databases, sql security logins, DTS, ...)?

Is there a way for the user to open SQL EM but only see his/her db and
nothing
more?

Thank youIn article <0Fp2d.21814$0h7.1558418@.news20.bellglobal.com>,
sergea@.nospam.ehmail.com says...
> If i create a new SQL login and give that login db ownership for a test db.
> When that user adds my SQL Server through his own SQL EM, why does he/she
> also see all other objects (databases, sql security logins, DTS, ...)?
> Is there a way for the user to open SQL EM but only see his/her db and
> nothing
> more?

Now you know why they call it "Enterprise Manager". If you don't want
someone gaining access to your database, and you don't trust the users
with SA access then you are screwed. Even if you did hide it, somehow,
the SA account can gain access to it.

Only DBA's should be using EM.

--
--
spamfree999@.rrohio.com
(Remove 999 to reply to me)|||On Thu, 16 Sep 2004 19:50:20 -0400, serge wrote:

>Is there a way for the user to open SQL EM but only see his/her db and
>nothing
>more?

Hi Serge,

Not as far as I know. This information is available in tables in the
master database that everybody has access to. EM presents it nicely, but
it's possible to retrieve the same information by executing some queries
in Query Analyzer, osql or any other tool.

Users can see the name of the database, but not the contents, so you
shouldn't be too worried about it (unless you have a database called
MyPlansToRipOffTheBoss - but you could always choose a less obvious name).

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hi

EM is not really something you should be giving to just anybody! If you
really want to do this, then you may want to create a separate instance,
although this may be a heavy overhead if you require more than a couple.

John

"serge" <sergea@.nospam.ehmail.com> wrote in message
news:0Fp2d.21814$0h7.1558418@.news20.bellglobal.com ...
> If i create a new SQL login and give that login db ownership for a test
db.
> When that user adds my SQL Server through his own SQL EM, why does he/she
> also see all other objects (databases, sql security logins, DTS, ...)?
> Is there a way for the user to open SQL EM but only see his/her db and
> nothing
> more?
> Thank you|||Thank you for the responses.

What about this SQL Web Data Administrator that can be run
using IIS or VS.Net.

Has anyone tried this? If so, maybe through this web front-end, there
might be better ways to control which tools to show for the users?

Hide databases in EM/QA

I have a MSSQL 2000 SP4 with ~200 databases of our clients.
Each client can access only his own database, but in Enterprise Manager
he able to view all databases on server.
How can I hide all other databases from client?Unfortunately, with SQL Server 2000, using Enterprise Mangler and Query
Analyzer, you will find that almost impossible.
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
<slousch@.gmail.com> wrote in message
news:1161762813.020209.171520@.f16g2000cwb.googlegroups.com...
>I have a MSSQL 2000 SP4 with ~200 databases of our clients.
> Each client can access only his own database, but in Enterprise Manager
> he able to view all databases on server.
> How can I hide all other databases from client?
>|||This is only true for Enterprise Manager. The users will be
able to see all the user databases although they can only
access those that they have been granted access to
explicitly or implicitly (groups, guest account
considerations).
In Query Analyzer, the databases will be restricted or
filtered to those which the user has access to.
-Sue
On Wed, 25 Oct 2006 12:18:33 -0700, "Arnie Rowland"
<arnie@.1568.com> wrote:

>Unfortunately, with SQL Server 2000, using Enterprise Mangler and Query
>Analyzer, you will find that almost impossible.|||Sue,
Maybe I'm doing something wrong, but this seems to work and provide a list
of all databases on the server EVEN if the login does not have permissions
for the database.
SELECT name
FROM master..sysdatabases
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
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:t810k2dq7tpgrmsi4lkvivgvevss73fs7a@.
4ax.com...
> This is only true for Enterprise Manager. The users will be
> able to see all the user databases although they can only
> access those that they have been granted access to
> explicitly or implicitly (groups, guest account
> considerations).
> In Query Analyzer, the databases will be restricted or
> filtered to those which the user has access to.
> -Sue
> On Wed, 25 Oct 2006 12:18:33 -0700, "Arnie Rowland"
> <arnie@.1568.com> wrote:
>
>|||That does not affect the view. Look at the available
databases in QA from the databases drop down.
-Sue
On Wed, 25 Oct 2006 22:48:19 -0700, "Arnie Rowland"
<arnie@.1568.com> wrote:

>Sue,
>Maybe I'm doing something wrong, but this seems to work and provide a list
>of all databases on the server EVEN if the login does not have permissions
>for the database.
>SELECT name
>FROM master..sysdatabases|||While I agree that the databases won't show up in the Object Explorer, I
read the OP's question as "How can I hide all other databases from client?",
and I replied that that was virtually impossible.
Your rejoinder seems to indicate to the OP that is was safe to use QA since
"the databases will be restricted or filtered to those which the user has
access to."
And I disagree that is it possible to 'hide all other databases" while using
QA.
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
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:mr91k2921p9mn4ousa61lj4govgj0brt44@.
4ax.com...
> That does not affect the view. Look at the available
> databases in QA from the databases drop down.
> -Sue
> On Wed, 25 Oct 2006 22:48:19 -0700, "Arnie Rowland"
> <arnie@.1568.com> wrote:
>
>

Hide Databases

Hi Folks,
I=B4m sharing a SQL Server 2000 SP3a with some users.
These users are using Enterprise Manager and Query=20
Analyzer at client side. It=B4s working well.
My problem is: In Enterprise Manager view, users can see
all databases, but only can manipulate your database.
My Question: In SQL Properties, How can I hide Databases=20
to non-owners ?
Thanks
Alex Decarli.This is not possible in SQL2000 Enterprise Manager. Metadata security is
pretty lax.You can choose to hide System databases if you wish , by going
into Edit SQL Server Registration Properties and uncheck Show system
databases and system objects
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Alex Decarli" <anonymous@.discussions.microsoft.com> wrote in message
news:079401c3dc1a$9849ce20$a501280a@.phx.gbl...
Hi Folks,
Im sharing a SQL Server 2000 SP3a with some users.
These users are using Enterprise Manager and Query
Analyzer at client side. Its working well.
My problem is: In Enterprise Manager view, users can see
all databases, but only can manipulate your database.
My Question: In SQL Properties, How can I hide Databases
to non-owners ?
Thanks
Alex Decarli.

Hidden Master, Model, MSDB, and Tempdb Databases.

Thanks in advance.
I have an instance of Enterprise Manager for SQL Server 2000 on my
desktop that does not list the above databases. When I attempt to add
any of them, I receive: "Error 5123: CREATE FILE encountered operating
system error 32.". I see all the databases in the server's copy of EM.
My questions are twofold. What is the likely reason my workstation
copy of EM got this way? How do I best fix this? I suspect I may have
to wait for no users to be on the system, but I hope someone knows a
way to fix this without doing so. Also the TempDB does not appear as
an mdf file, which I guess is because it is a temporary construct that
exists in memory only. So I do not even being to know how to make that
appear in my list.
Mark Simmerman
SQL Learner
Napa, CaliforniaRight-click on the Server, select 'Edit SQL Server Registration properties
and make sure 'show system databases and system objects' is checked.
Hope this helps.
Dan Guzman
SQL Server MVP
"Mark" <xxxdbaxxx@.gmail.com> wrote in message
news:1148081170.238568.164540@.38g2000cwa.googlegroups.com...
> Thanks in advance.
> I have an instance of Enterprise Manager for SQL Server 2000 on my
> desktop that does not list the above databases. When I attempt to add
> any of them, I receive: "Error 5123: CREATE FILE encountered operating
> system error 32.". I see all the databases in the server's copy of EM.
>
> My questions are twofold. What is the likely reason my workstation
> copy of EM got this way? How do I best fix this? I suspect I may have
> to wait for no users to be on the system, but I hope someone knows a
> way to fix this without doing so. Also the TempDB does not appear as
> an mdf file, which I guess is because it is a temporary construct that
> exists in memory only. So I do not even being to know how to make that
> appear in my list.
>
> Mark Simmerman
> SQL Learner
> Napa, California
>|||Except for MSDB, those system databases MUST exist on your server, so trying
to 'add' them is pointless. They're already there which is why you're
getting the message you reported.
I presume you also have Query Analyzer, so you could run a query:
exec sp_helpdb
and you should see the system databases listed with all the others.
Right click on the name of your server (or LOCAL) in Enterprise Manager.
Choose 'Edit SQL Server Registration Properties'
Check the box near the button of the dialog box that says 'Show system
databases and system objects'
Click OK
Now when you expand your Databases node in Enterprise Manager, you should
see the system databases.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Mark" <xxxdbaxxx@.gmail.com> wrote in message
news:1148081170.238568.164540@.38g2000cwa.googlegroups.com...
> Thanks in advance.
> I have an instance of Enterprise Manager for SQL Server 2000 on my
> desktop that does not list the above databases. When I attempt to add
> any of them, I receive: "Error 5123: CREATE FILE encountered operating
> system error 32.". I see all the databases in the server's copy of EM.
>
> My questions are twofold. What is the likely reason my workstation
> copy of EM got this way? How do I best fix this? I suspect I may have
> to wait for no users to be on the system, but I hope someone knows a
> way to fix this without doing so. Also the TempDB does not appear as
> an mdf file, which I guess is because it is a temporary construct that
> exists in memory only. So I do not even being to know how to make that
> appear in my list.
>
> Mark Simmerman
> SQL Learner
> Napa, California
>|||That was sooooo simple. Thanks, Kalen and Dan.

Hidden Databases?

Hello,
on an instances log I see entrys which say that Databases "model4IDR" and
"master4IDR" are started. But through E.M. or sp_helpdb I don't see such
databases, among other things only the normal system databases.
What could be that?
Thank You
Joachim
These are not created by SQL Server. They are created by some 3:rd party program, possibly Backup
Exec (if my memory serves me).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Joachim Hofmann" <speicher@.freenet.de> wrote in message
news:%237HVA1q5FHA.2956@.TK2MSFTNGP12.phx.gbl...
> Hello,
> on an instances log I see entrys which say that Databases "model4IDR" and "master4IDR" are
> started. But through E.M. or sp_helpdb I don't see such databases, among other things only the
> normal system databases.
> What could be that?
> Thank You
> Joachim
sql

Hidden Databases?

Hello,
on an instances log I see entrys which say that Databases "model4IDR" and
"master4IDR" are started. But through E.M. or sp_helpdb I don't see such
databases, among other things only the normal system databases.
What could be that?
Thank You
JoachimThese are not created by SQL Server. They are created by some 3:rd party program, possibly Backup
Exec (if my memory serves me).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Joachim Hofmann" <speicher@.freenet.de> wrote in message
news:%237HVA1q5FHA.2956@.TK2MSFTNGP12.phx.gbl...
> Hello,
> on an instances log I see entrys which say that Databases "model4IDR" and "master4IDR" are
> started. But through E.M. or sp_helpdb I don't see such databases, among other things only the
> normal system databases.
> What could be that?
> Thank You
> Joachim

Hidden Databases?

Hello,
on an instances log I see entrys which say that Databases "model4IDR" and
"master4IDR" are started. But through E.M. or sp_helpdb I don't see such
databases, among other things only the normal system databases.
What could be that?
Thank You
JoachimThese are not created by SQL Server. They are created by some 3:rd party pro
gram, possibly Backup
Exec (if my memory serves me).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Joachim Hofmann" <speicher@.freenet.de> wrote in message
news:%237HVA1q5FHA.2956@.TK2MSFTNGP12.phx.gbl...
> Hello,
> on an instances log I see entrys which say that Databases "model4IDR" and
"master4IDR" are
> started. But through E.M. or sp_helpdb I don't see such databases, among o
ther things only the
> normal system databases.
> What could be that?
> Thank You
> Joachim

Hi availability & DR for Large No. of DBs

Hi,
I want some help for this particular scenario.
I have more than 350 Databases growing on an average of 5 Databases per month.
I am concernd about the DR Strategy to be adopted.
Can any one help meall databases are on the same server

Help: MS Access to SQL Server 2005 using SSMA

I am new to databases and need some info that I'm having trouble researching. I just migrated a access database to SQL server 2005 using SQL Server Migration assistant (SSMA). I wondered how SSMA handles NULL values? Also how it treats differences between Access and SQL? Any help would be appreciated.

SSMA will create a table in SQL Server that has equivalent fields to those in Access and then transfer the data. So if you have a Text field in Access that is not Required (i.e. it allows NULL values) you will get an NVarchar(255) filed in SQL with the Nullable property set to True. When the data is migrated, the NULLs will be migrated as well.

Mike

HELP: Merge Agent failed - Error number: MSSQL_REPL-2147199402)

We have 50+ clients that use a pull merge subscription. There are 2
databases and each databases has only 1 publication. Each client has ONE
subscription to each of the 2 databases.
We had to set the retention to unlimited to avoid the following error:
The Merge Agent failed after detecting that retention-based metadata cleanup
has deleted metadata at the Publisher for changes not yet sent to the
Subscriber. You must reinitialize the subscription (without upload).
(Source: MSSQL_REPL, Error number: MSSQL_REPL-2147199402)
Get help: http://help/MSSQL_REPL-2147199402
The article http://support.microsoft.com/kb/919929/kb/919929 has a hotfix
BUT it says that it is if a Subscriber subscribes to two publications in the
same Subscriber database. This is not the case.
We had a ticket open with Miscrosoft but they were unable to find the
solution for it after 4 or 5 weeks.
The clients use their subscription databases directly and they are synced to
the server every 30 minutes.
Anybody have any idea what is causing this and how to solve this problem.
The LDF's are getting quite large and we need to find a solution soon.
Thanks in advance!
I fail to understand how the ldf's fit in here. It sounds like there are
some long running transactions which transcend the log dump boundaries. Have
you investigated what these open transactions are? Are they replication
related?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"RichardD" <RichardD@.discussions.microsoft.com> wrote in message
news:B0445940-5C26-403B-8589-692458B82098@.microsoft.com...
> We have 50+ clients that use a pull merge subscription. There are 2
> databases and each databases has only 1 publication. Each client has ONE
> subscription to each of the 2 databases.
> We had to set the retention to unlimited to avoid the following error:
> The Merge Agent failed after detecting that retention-based metadata
> cleanup
> has deleted metadata at the Publisher for changes not yet sent to the
> Subscriber. You must reinitialize the subscription (without upload).
> (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147199402)
> Get help: http://help/MSSQL_REPL-2147199402
> The article http://support.microsoft.com/kb/919929/kb/919929 has a hotfix
> BUT it says that it is if a Subscriber subscribes to two publications in
> the
> same Subscriber database. This is not the case.
> We had a ticket open with Miscrosoft but they were unable to find the
> solution for it after 4 or 5 weeks.
> The clients use their subscription databases directly and they are synced
> to
> the server every 30 minutes.
> Anybody have any idea what is causing this and how to solve this problem.
> The LDF's are getting quite large and we need to find a solution soon.
> Thanks in advance!
>
|||I also expect, the size of the log file has nothing to do with your
retention period.
I'm afraid I can't help on your retention period problem, but perhaps
your ldfs are growing because of a combination of the recovery model
your database is using, and your backup strategy.
Have a look at the following articles, perhaps they will help:
http://msdn2.microsoft.com/en-us/library/ms175987.aspx
http://msdn2.microsoft.com/en-us/library/ms175495.aspx
Tim Hill
On Feb 23, 6:24 pm, "Hilary Cotter" <hilary.cot...@.gmail.com> wrote:[vbcol=seagreen]
> I fail to understand how the ldf's fit in here. It sounds like there are
> some long running transactions which transcend the log dump boundaries. Have
> you investigated what these open transactions are? Are they replication
> related?
> --
> Hilary Cotter
> Looking for a SQL Server replication book?http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTShttp://www.indexserverfaq.com
> "RichardD" <Richa...@.discussions.microsoft.com> wrote in message
> news:B0445940-5C26-403B-8589-692458B82098@.microsoft.com...
>
>
>
>
|||You are right. I have already fixed the issue with the log files.
Regarding the error MSSQL_REPL-2147199402 we suspect that the subscribers
changed their computer date beyond 14 days (default retention) and it caused
this error.
One reason we suspect this is that out of 40+ subscribers to the one
publication, only a few had this problem and a couple of them had it happen
twice.
Does this sound like a possibility to you?
We are about to change their retention from unlimited back to 14 days. Is
there anything we should be aware of before doing this?
Thanks so much for your help.
|||Yes, we figured out the log file issue. Thank you anyway.
We are still looking for the solution for the Error number:
MSSQL_REPL-2147199402.
Any ideas?

HELP: Installing SQl server databases

Dear all,
One of our application consist of configuring an SQL server database with
user rights to tables and preparing corresponding store procedure.
usually when we do this, we prepare the server in our test environment and
then ship the whole hardware pre configure to customers.
in some cases we do not have the customer server, so we need to do the whole
configuration on site and in cas of errors its hard to find.
is there an easy way to prepare the SQL confisuration database, store
procedure, table rights on a test PC, then do a kind of straight forward
deployement on any server running SQL ?
thanks for your help
regards
SergeHi,
You can create the customer database in your local sql server. Do all the
setups in that new database. backup the database or detach the database.
Copy the backup or MDF and LDF file and take it in a tape. Restore the files
from tape to Customer side server and
do a Database restore or Attach the MDF and LDF.
Incase if you have login an user suid mismacth use the system stored
procedure sp_change_users_login to fix.
This will ensure that all the configurations are avaiable.
Thanks
Hari
SQL Server MVP
"serge calderara" <sergecalderara@.discussions.microsoft.com> wrote in
message news:EDEAF3C8-903B-49D6-8A48-9FD1B71E379F@.microsoft.com...
> Dear all,
> One of our application consist of configuring an SQL server database with
> user rights to tables and preparing corresponding store procedure.
> usually when we do this, we prepare the server in our test environment and
> then ship the whole hardware pre configure to customers.
> in some cases we do not have the customer server, so we need to do the
> whole
> configuration on site and in cas of errors its hard to find.
> is there an easy way to prepare the SQL confisuration database, store
> procedure, table rights on a test PC, then do a kind of straight forward
> deployement on any server running SQL ?
> thanks for your help
> regards
> Serge|||I have seen the method suggested earlier about taking the mdf and ldf and
using sp_attach_Db and in most cases you should be OK ( except for
permissions.) However the risk you are taking is with the file version...
The physical files will only have a limited life span, as MS changes things
usually attach works for one release backwards...
Another way is to generate scripts... These take longer to run, but are
usually safer because basic syntax does not change that often...One thing
you might do is combine the two... In your release, generate the scripts.
Then when you are about to go and setup a db for a user, THEN run the
scripts, and take the resulting mdf and ldf...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"serge calderara" <sergecalderara@.discussions.microsoft.com> wrote in
message news:EDEAF3C8-903B-49D6-8A48-9FD1B71E379F@.microsoft.com...
> Dear all,
> One of our application consist of configuring an SQL server database with
> user rights to tables and preparing corresponding store procedure.
> usually when we do this, we prepare the server in our test environment and
> then ship the whole hardware pre configure to customers.
> in some cases we do not have the customer server, so we need to do the
> whole
> configuration on site and in cas of errors its hard to find.
> is there an easy way to prepare the SQL confisuration database, store
> procedure, table rights on a test PC, then do a kind of straight forward
> deployement on any server running SQL ?
> thanks for your help
> regards
> Serge|||I am a bit confuse as I am new in that part.
let say that my ServerA is my test server in my office on which I prepare
every thing
lets call ServerB the target server on which th final DB will run
Could you please details me which operation to do on which server?
What is the content of those MDF and LDF files ?
How to deploy rights or group from server A to ServerB at the same time
thanks and sorry to disturb
experince will come with the time :-)
serge
"Wayne Snyder" wrote:
> I have seen the method suggested earlier about taking the mdf and ldf and
> using sp_attach_Db and in most cases you should be OK ( except for
> permissions.) However the risk you are taking is with the file version...
> The physical files will only have a limited life span, as MS changes things
> usually attach works for one release backwards...
> Another way is to generate scripts... These take longer to run, but are
> usually safer because basic syntax does not change that often...One thing
> you might do is combine the two... In your release, generate the scripts.
> Then when you are about to go and setup a db for a user, THEN run the
> scripts, and take the resulting mdf and ldf...
>
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "serge calderara" <sergecalderara@.discussions.microsoft.com> wrote in
> message news:EDEAF3C8-903B-49D6-8A48-9FD1B71E379F@.microsoft.com...
> > Dear all,
> >
> > One of our application consist of configuring an SQL server database with
> > user rights to tables and preparing corresponding store procedure.
> >
> > usually when we do this, we prepare the server in our test environment and
> > then ship the whole hardware pre configure to customers.
> >
> > in some cases we do not have the customer server, so we need to do the
> > whole
> > configuration on site and in cas of errors its hard to find.
> >
> > is there an easy way to prepare the SQL confisuration database, store
> > procedure, table rights on a test PC, then do a kind of straight forward
> > deployement on any server running SQL ?
> >
> > thanks for your help
> > regards
> > Serge
>
>|||On ServerA you will prepare the database to be installed, call it DBa. Once
this is complete you have 2 choices. 1) You can dump DBa and use the
resulting dump file, or 2) you can detach DBa and use the MDF and LDF files.
Copy either the dump file or MDF and LDF files to ServerB.
If you selected the database dump option, restore DBa. See Books Online for
the syntax.
If you selected the detach option, attach DBa. Again see Books Online for
the syntax.
As mentioned in one of the earlier posts if you created any data server
logins and database users you will need to fix those up via the
sp_change_users_login stored proc. See Books Online for documentation.
"serge calderara" wrote:
> I am a bit confuse as I am new in that part.
> let say that my ServerA is my test server in my office on which I prepare
> every thing
> lets call ServerB the target server on which th final DB will run
> Could you please details me which operation to do on which server?
> What is the content of those MDF and LDF files ?
> How to deploy rights or group from server A to ServerB at the same time
> thanks and sorry to disturb
> experince will come with the time :-)
> serge
> "Wayne Snyder" wrote:
> > I have seen the method suggested earlier about taking the mdf and ldf and
> > using sp_attach_Db and in most cases you should be OK ( except for
> > permissions.) However the risk you are taking is with the file version...
> > The physical files will only have a limited life span, as MS changes things
> > usually attach works for one release backwards...
> >
> > Another way is to generate scripts... These take longer to run, but are
> > usually safer because basic syntax does not change that often...One thing
> > you might do is combine the two... In your release, generate the scripts.
> > Then when you are about to go and setup a db for a user, THEN run the
> > scripts, and take the resulting mdf and ldf...
> >
> >
> > --
> > Wayne Snyder, MCDBA, SQL Server MVP
> > Mariner, Charlotte, NC
> > www.mariner-usa.com
> > (Please respond only to the newsgroups.)
> >
> > I support the Professional Association of SQL Server (PASS) and it's
> > community of SQL Server professionals.
> > www.sqlpass.org
> >
> > "serge calderara" <sergecalderara@.discussions.microsoft.com> wrote in
> > message news:EDEAF3C8-903B-49D6-8A48-9FD1B71E379F@.microsoft.com...
> > > Dear all,
> > >
> > > One of our application consist of configuring an SQL server database with
> > > user rights to tables and preparing corresponding store procedure.
> > >
> > > usually when we do this, we prepare the server in our test environment and
> > > then ship the whole hardware pre configure to customers.
> > >
> > > in some cases we do not have the customer server, so we need to do the
> > > whole
> > > configuration on site and in cas of errors its hard to find.
> > >
> > > is there an easy way to prepare the SQL confisuration database, store
> > > procedure, table rights on a test PC, then do a kind of straight forward
> > > deployement on any server running SQL ?
> > >
> > > thanks for your help
> > > regards
> > > Serge
> >
> >
> >

HELP...Need to create query to place multiple databases offline

Hi All,
I am currently working on a project on a sql server that has like 500
databases. I need to place like 80% of the databases offline. I have been
using dboption or EM but they are time comsuming since I have to do this one
database at a time. I have like 5 more servers with 500 databases to do this
on. Any way I can do this via script? IS there a away to perform the
sp_dboption to all databases with an exception to a few? Any help is greatly
appreciated.
Regards,
RichardSeveral ways...
One way is to have a table with either the exception databases or the other
way around. Then you
have a .sql script which does something like:
SELECT name FROM sysdatabases
WHERE name NOT IN (SELECT name FROM theExceptionTable)
AND NAME NOT IN('master', tempdb', 'msdb', model')
You create a cursor of above (see DECLARE CURSOR) and loop the cursor. Insid
e the cursor, you use
dynamic SQL to execute ALTER DATABASE command to set the database name offli
ne.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Richard" <Richard@.discussions.microsoft.com> wrote in message
news:2CA216A7-C93E-46D2-96C2-5B0E99A3E671@.microsoft.com...
> Hi All,
> I am currently working on a project on a sql server that has like 500
> databases. I need to place like 80% of the databases offline. I have been
> using dboption or EM but they are time comsuming since I have to do this o
ne
> database at a time. I have like 5 more servers with 500 databases to do th
is
> on. Any way I can do this via script? IS there a away to perform the
> sp_dboption to all databases with an exception to a few? Any help is great
ly
> appreciated.
>
> Regards,
> Richard|||sp_MSforeachdb 'USE ? IF DB_NAME() NOT IN '' ...exception db list...'' EXE
C
sp_dboption ?, ''read only'', ''true'''
"Richard" wrote:

> Hi All,
> I am currently working on a project on a sql server that has like 500
> databases. I need to place like 80% of the databases offline. I have been
> using dboption or EM but they are time comsuming since I have to do this o
ne
> database at a time. I have like 5 more servers with 500 databases to do th
is
> on. Any way I can do this via script? IS there a away to perform the
> sp_dboption to all databases with an exception to a few? Any help is great
ly
> appreciated.
>
> Regards,
> Richard