Re: Move 10 DBs and provide dev access thru EM?

Thanx Dan,
Have got the users sorted now, by adding roles as you suggested & it works a
treat. I am having problems however with moving databases from C to D and
wondered if you could shed any light. I detached a database, then tried to
move it to the D Drive (240GB) but it claims the database is still in use
somewhere. I know this cant be true as I also tried with NorthWind and got
the same result. As a test I then tried to rename the logfile -
database_log.ldf but got the same message. Would it help if I actually
stopped the SQL service to do this (Live enviroment) or is there something
else?

I know my procedure is correct as I did this on a test server this morning
without any drama..

Thanx again
G

"Dan Guzman" wrote:

detaching the DBs moving them to D then
attaching seemed the option, but I tried this on a test server and the DBs
lost permissions!

Permissions should not have been lost if you simply detached and re-attached
to the same instance. I can see how you could run into issues if you
attached to a different instance due to mismatched logins/users, though.

Secondly, can I provide access to the databases for our developers through
their local Enterprise manager as read only access? Do I need to create
user
roles in all databases or can I assign a single role and apply to all?

You'll need to grant the developers access to each database. The default
public role permissions will allow viewing schema and objects. You can add
them to the db_datareader fixed database role in order to read data.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"GaryB" <GaryB@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:C9034D52-59AA-4590-B4B0-70573E10B140@xxxxxxxxxxxxxxxx
Hi,
We are running a live SQL2000 server with 10 dbs on the servers default
SQL
install location (C:\Programme Files\MSSQL\Data\etc). For various reasons
I
want to move this location to the same servers D Drive and wondered if
anyone
could point me in the right direction how to achive this wihtout losing
permissions & more importantly data.. detaching the DBs moving them to D
then
attaching seemed the option, but I tried this on a test server and the DBs
lost permissions!

Secondly, can I provide access to the databases for our developers through
their local Enterprise manager as read only access? Do I need to create
user
roles in all databases or can I assign a single role and apply to all?

Thanx for your time
G



.

Relevant Pages

  • Re: Exchange Move Issues?
    ... I'm a bit confused on what permissions to assign for SBS, ... When you finish moving the databases, ... You can move the log files and database files to any folder that you want to ... Note Only assign permissions to the Server Operators group if the Exchange ...
    (microsoft.public.windows.server.sbs)
  • Re: AS2005 ... what is wrong with it?
    ... What I have seen is that the dev server is *faster* than the prod server ... And does your dev server also have all 6 databases with all these roles? ... When I restore the "PROV" i got approx 20 sec. ... Starting from an empty data folder should show if this theory is true ...
    (microsoft.public.sqlserver.olap)
  • Re: How to I get a list of databases?
    ... Same permissions quandary, but EM uses the sp_databases system stored ... Since I don't have admin rights to their server, ... was able to deduce which databases I can access. ... I have used ADOX to get a list of tables in a database, ...
    (microsoft.public.inetserver.asp.db)
  • 2nd Post - Trouble Getting VS.Net 2003 WalkThrough working
    ... the server. ... MSDE either from the Setup or from the SQL2KDeskSP3 execute. ... it does look like some of the sample databases have been ... >> and they directed me to install MSDE and they attached a ConfigSamples ...
    (microsoft.public.sqlserver.msde)
  • RE: Server Explorer & Databases
    ... As for the visual studio's database server explorer, ... oracle, visual studio generally use the OLD DB provider which is for Oracle ... and support of most general funcctionalitis in latest version of Oracle. ... #Server Explorer for Oracle Databases ...
    (microsoft.public.vsnet.ide)