Database migration and security issues

Dear all,
I have 2 questions:
(1) I would like to migrate a SQL database (named "myDB") from SQL server A
to SQL server B. I am planning to create a new database named "myDB" in
server B, then go back to server A to do the backup. Then restore it in
server B. Is it feasible? Anyone has a better way to do it?
(2) The second question deals with security issue and it makes me
frustrated. There is a database which is maintained by an end-user in my
company currently. He has the "db_owner" rights on this database. Now we
(MIS) are going to take over the admin work of this database. We will assign
"db_reader" & "db_writer" to him only. However, this user wrote some VB
programs to access this database (using ADO) and this programs involves some
actions of deleteing and creating temp tables in the database. If he only has
"db_reader" & "db_writer" rights, then the program would fail to work. It is
not reasonable to create another SQL server login id with "db_owner" right to
this user, this would lose control on our admin work again. I am quite
frustrated by this... can anyone help?
Thanks a lot.
Ivan1. create backup on server A then restore on server B. You must transfer
logins from server A to B
2. ">>this programs involves some
> actions of deleteing and creating temp tables in the database""
create temp tables in tembdb, not in your prod db
OR
add this user in db_ddladmin :(
--
Aleksandar Grbic
MCDBA, Senior Database Administrator
"Ivan" wrote:
> Dear all,
> I have 2 questions:
> (1) I would like to migrate a SQL database (named "myDB") from SQL server A
> to SQL server B. I am planning to create a new database named "myDB" in
> server B, then go back to server A to do the backup. Then restore it in
> server B. Is it feasible? Anyone has a better way to do it?
> (2) The second question deals with security issue and it makes me
> frustrated. There is a database which is maintained by an end-user in my
> company currently. He has the "db_owner" rights on this database. Now we
> (MIS) are going to take over the admin work of this database. We will assign
> "db_reader" & "db_writer" to him only. However, this user wrote some VB
> programs to access this database (using ADO) and this programs involves some
> actions of deleteing and creating temp tables in the database. If he only has
> "db_reader" & "db_writer" rights, then the program would fail to work. It is
> not reasonable to create another SQL server login id with "db_owner" right to
> this user, this would lose control on our admin work again. I am quite
> frustrated by this... can anyone help?
> Thanks a lot.
> Ivan|||Dear Aleksandar,
How to transfer logins ?
Thanks!!
"Aleksandar Grbic" wrote:
> 1. create backup on server A then restore on server B. You must transfer
> logins from server A to B
> 2. ">>this programs involves some
> > actions of deleteing and creating temp tables in the database""
> create temp tables in tembdb, not in your prod db
> OR
> add this user in db_ddladmin :(
> --
> Aleksandar Grbic
> MCDBA, Senior Database Administrator
>
> "Ivan" wrote:
> > Dear all,
> >
> > I have 2 questions:
> >
> > (1) I would like to migrate a SQL database (named "myDB") from SQL server A
> > to SQL server B. I am planning to create a new database named "myDB" in
> > server B, then go back to server A to do the backup. Then restore it in
> > server B. Is it feasible? Anyone has a better way to do it?
> >
> > (2) The second question deals with security issue and it makes me
> > frustrated. There is a database which is maintained by an end-user in my
> > company currently. He has the "db_owner" rights on this database. Now we
> > (MIS) are going to take over the admin work of this database. We will assign
> > "db_reader" & "db_writer" to him only. However, this user wrote some VB
> > programs to access this database (using ADO) and this programs involves some
> > actions of deleteing and creating temp tables in the database. If he only has
> > "db_reader" & "db_writer" rights, then the program would fail to work. It is
> > not reasonable to create another SQL server login id with "db_owner" right to
> > this user, this would lose control on our admin work again. I am quite
> > frustrated by this... can anyone help?
> > Thanks a lot.
> >
> > Ivan|||Hi Ivan,
(i) U can restore then database by backup
(ii) copy the mdf file and attach the database.
(iii) Use copy database wizard.
(iv) script the database and run the script on the server and transfer
the data.
Go to DTS and use transfer logins .
defien source from where u want to transfer the logins and destination
to where ti want to copy.
but before u tranfer u create or restore or attach the database on that
srever.
hope this hel u
from
killer|||Search Knowledgebase for
sp_help_revlogin
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Ivan" <Ivan@.discussions.microsoft.com> wrote in message
news:5E32FF17-7866-40CB-A5CE-6AE5EE0FD64C@.microsoft.com...
> Dear Aleksandar,
> How to transfer logins ?
> Thanks!!
> "Aleksandar Grbic" wrote:
>> 1. create backup on server A then restore on server B. You must transfer
>> logins from server A to B
>> 2. ">>this programs involves some
>> > actions of deleteing and creating temp tables in the database""
>> create temp tables in tembdb, not in your prod db
>> OR
>> add this user in db_ddladmin :(
>> --
>> Aleksandar Grbic
>> MCDBA, Senior Database Administrator
>>
>> "Ivan" wrote:
>> > Dear all,
>> >
>> > I have 2 questions:
>> >
>> > (1) I would like to migrate a SQL database (named "myDB") from SQL server A
>> > to SQL server B. I am planning to create a new database named "myDB" in
>> > server B, then go back to server A to do the backup. Then restore it in
>> > server B. Is it feasible? Anyone has a better way to do it?
>> >
>> > (2) The second question deals with security issue and it makes me
>> > frustrated. There is a database which is maintained by an end-user in my
>> > company currently. He has the "db_owner" rights on this database. Now we
>> > (MIS) are going to take over the admin work of this database. We will assign
>> > "db_reader" & "db_writer" to him only. However, this user wrote some VB
>> > programs to access this database (using ADO) and this programs involves some
>> > actions of deleteing and creating temp tables in the database. If he only has
>> > "db_reader" & "db_writer" rights, then the program would fail to work. It is
>> > not reasonable to create another SQL server login id with "db_owner" right to
>> > this user, this would lose control on our admin work again. I am quite
>> > frustrated by this... can anyone help?
>> > Thanks a lot.
>> >
>> > Ivan|||show BOL for help, "sp_addlogin"
you must create login on server B with same SID and password as logins on
server A
see examples in Books Online
OR
see "HOW TO: Transfer Logins and Passwords Between Instances of SQL Server"
at http://support.microsoft.com/default.aspx?scid=kb;en-us;246133
Aleksandar Grbic
MCDBA, Senior Database Administrator
"Ivan" wrote:
> Dear Aleksandar,
> How to transfer logins ?
> Thanks!!
> "Aleksandar Grbic" wrote:
> > 1. create backup on server A then restore on server B. You must transfer
> > logins from server A to B
> >
> > 2. ">>this programs involves some
> > > actions of deleteing and creating temp tables in the database""
> > create temp tables in tembdb, not in your prod db
> > OR
> > add this user in db_ddladmin :(
> > --
> > Aleksandar Grbic
> > MCDBA, Senior Database Administrator
> >
> >
> > "Ivan" wrote:
> >
> > > Dear all,
> > >
> > > I have 2 questions:
> > >
> > > (1) I would like to migrate a SQL database (named "myDB") from SQL server A
> > > to SQL server B. I am planning to create a new database named "myDB" in
> > > server B, then go back to server A to do the backup. Then restore it in
> > > server B. Is it feasible? Anyone has a better way to do it?
> > >
> > > (2) The second question deals with security issue and it makes me
> > > frustrated. There is a database which is maintained by an end-user in my
> > > company currently. He has the "db_owner" rights on this database. Now we
> > > (MIS) are going to take over the admin work of this database. We will assign
> > > "db_reader" & "db_writer" to him only. However, this user wrote some VB
> > > programs to access this database (using ADO) and this programs involves some
> > > actions of deleteing and creating temp tables in the database. If he only has
> > > "db_reader" & "db_writer" rights, then the program would fail to work. It is
> > > not reasonable to create another SQL server login id with "db_owner" right to
> > > this user, this would lose control on our admin work again. I am quite
> > > frustrated by this... can anyone help?
> > > Thanks a lot.
> > >
> > > Ivansql