PhillipM


I would like to handover a SQL server 2005 database that I've been using for testing/development to a client for use in a production environment. Besides deleting my test records from the database and making a copy of the MDF file, do I have to handover the datase's LDF file too I guess I should also compress the database before providing a copy. Please advise of "gotchas" I should be a aware of. Also, I guess the client's IT group would need to run my "create user" script first before mounting the database, since the created user is the owner of the database.

Thanks,

Phillip




Re: Handing over SQL Server DB to client company

Madhu K Nair


There are two methods

(a) Backup the database from Test ENV and Restore it in Production : In this you don't have to pass any MDF /LDF file. Backup will create a DAT file and you can compress that and give to the client and ask them to restore with MOVE OPtion

(b) The other method is Detach/Attach method . in this case you have to copy bot MDF and LDF (all the datafile and log files) to the client

I would prefer BACKUP/RESTORE Method. You can create a script for creation of Login and after creating Login you have to remap those login to the DB User, since it was created in two instance and so the SID changes.

From BOL : Make a copy of a database using BACKUP and RESTORE

BACKUP DATABASE Northwind
TO DISK = 'c:\Northwind.bak'
RESTORE FILELISTONLY
FROM DISK = 'c:\Northwind.bak'
RESTORE DATABASE TestDB
FROM DISK = 'c:\Northwind.bak'
WITH MOVE 'Northwind' TO 'c:\test\testdb.mdf',
MOVE 'Northwind_log' TO 'c:\test\testdb.ldf'

To move LOgins refer : http://support.microsoft.com/kb/246133

Move jobs if you have any by scripting the jobs from the source Server and run the script in target

Madhu







Re: Handing over SQL Server DB to client company

PhillipM

Thanks for the tips!! So, would the following steps work

Step 1) DBA at client's company mounts the MDF and LDF files.

Step 2) DBA runs the below script. I used Management Studio to reverse engineer from my environment:

Code Block

/****** Object: Login [Item] Script Date: 10/14/2007 21:03:14 ******/
/* For security reasons the login is created disabled and with a random password. */
/****** Object: Login [Item] Script Date: 10/14/2007 21:03:14 ******/
CREATE LOGIN [Item] WITH PASSWORD=N't¡¥3GFi+i|`¡ÁAež2OAiIcc
}:¡ãe ', DEFAULT_DATABASE=[ItemDB], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
ALTER LOGIN [Item] DISABLE

Step 3) And then DBA at client's company would map that ID with the following script:

Code Block
ALTER AUTHORIZATION ON DATABASE::ItemDB TO Item

Would the above work for the creating user ID Item in the target instance as well as mapping the owner For me, as a novice, I'm more comfortable with using Detach/Attach instead of backups...






Re: Handing over SQL Server DB to client company

Madhu K Nair

Instead of this statement

ALTER AUTHORIZATION ON DATABASE::ItemDB TO Item

To map the Orphaned User (when the user created in one server transfer to other server the User - Login mapping breaks coz SID changes)

Run this script

USE ITEMDB

sp_change_users_login 'update_one', 'Item', 'Item'

For more info : http://support.microsoft.com/kb/274188/

And also why are u disabling the login once its created

Madhu






Re: Handing over SQL Server DB to client company

PhillipM

Great! Didn't realize there was a difference between Login and user. Guess when you mount the MDF and LDF files, the user is created, and my CREATE LOGIN script would create the Login, now sp_change_users_login would link them both.

Thanks!

Phillip





Re: Handing over SQL Server DB to client company

PhillipM

By the way, does the hashed password in CREATE LOGIN map to the orginal password that I created for the login (Item), or do I have to do a change password too

Phillip





Re: Handing over SQL Server DB to client company

Madhu K Nair

PhillipM wrote:

Great! Didn't realize there was a difference between Login and user. Guess when you mount the MDF and LDF files, the user is created, and my CREATE LOGIN script would create the Login, now sp_change_users_login would link them both.

Thanks!

Phillip

There are two level security in sql server. One Server Level (Login) and the other DB (User). When u mount db it will have all the users when it was dettached. so you don't have to create again. Only to map it to the login using SP_Change_Users_Login

Madhu






Re: Handing over SQL Server DB to client company

PhillipM

I installed SQL server on a laptop in an effort to simulate handing over the files to a client. I would was able to mount the database files (MDF, LDF), create the login Item (CREATE LOGIN...) and map the Item login with user Item (

EXEC sp_change_users_login 'Update_One', 'Item', 'Item' ) .

I then started my application; however, I got an error: The SELECT permission was denied on ojbect '<a table name>', database 'ItemDB', schema 'dbo'. After seeing this error, I thought it may be linked to the owner of the database (SA, since MDF/LDF was mounted under SA account). I tried changing the owner of my database to Item (EXEC sp_changedbowner 'Item'), but got the following error message: Msg 15110, Level 16, State 1, Line 1 The proposed new database owner is already a user or aliased in the database.

Any tips

Phillip





Re: Handing over SQL Server DB to client company

Madhu K Nair

Make the USER Item as DB_OWNER (if it requires) else give the permision or add in proper role

run the following statement

EXEC sp_addrolemember 'db_owner', 'test'

Madhu






Re: Handing over SQL Server DB to client company

PhillipM

I just got it to work with the below method:

Use ItemDB;

GO

EXEC sp_dropuser 'Item'

go

EXEC sp_changedbowner 'Item','False'

I believe the above would now associate login Item dbo of ItemDB, and there was no need for user Item in ItemDB in the first place Guess it was an "orphan" user. I was able to start the application and connect to the database.

Let me know your thoughts.

Thanks,

Phillip





Re: Handing over SQL Server DB to client company

Madhu K Nair

From BOL

sp_changedbowner [ @loginame = ] 'login'
          [ , [ @map= ] remap_alias_flag ]

Arguments

[ @loginame = ] 'login'

Is the login ID of the new owner of the current database. login is sysname, with no default. login must be an already existing SQL Server login or Microsoft Windows user. login cannot become the owner of the current database if it already has access to the database through an existing alias or user security account within the database. To avoid this, drop the alias or user within the current database first.

It was not an orphan user.. BOL says login cannot become the owner of the current database if it already has access to the database through an existing alias or user security account within the database.

Both the method will do.. if you would have add the user to DB_OWNER role it could have solve the problem..

Madhu