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

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: 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)
  • Re: Lost User rights / permissions
    ... You might try running SQL Profiler and capturing some events in the Security Audit class to see if someone is "messing" with the permissions, ... > No databases have been restored when this happens. ... >>SQL Server FAQ, articles, code samples, interview ...
    (microsoft.public.sqlserver.security)
  • Re: Move 10 DBs and provide dev access thru EM?
    ... I know my procedure is correct as I did this on a test server this morning ... but I tried this on a test server and the DBs ... Permissions should not have been lost if you simply detached and re-attached ... roles in all databases or can I assign a single role and apply to all? ...
    (microsoft.public.sqlserver.security)
  • Re: public role???
    ... What exactly do you mean by "new users have access to all DBs"? ... If you mean that new users can see the names of the other databases, ... Documentation on the "guest" account is available in the SQL Server Books ... > permissions. ...
    (microsoft.public.sqlserver.security)