SQLTeam.com | Weblogs | Forums

SQL Express Database migration


#1

Hello,
We
have quite simple SQL database sitting on Azure, we are developing it
on the fly so no Dev/QA/Prod versions, is on SQL Express 2012

Sometimes
we need to take a copy of the database and attach to a local server for
further revision/development then replace the one on Azure.

Initially
we used <detach - attach> and worked fine till we introduced
users with limited accesses and custom db properties such as
db_executor.

Now, after attaching, can not use the limited user to connect via odbc.
Reading forums I understood that user profiles need to be rebuilt, got some SQL but does not seams to work.

Any idea what I'm doing wrong? or any advise on what should be the correct steps to accomplish this?

Please note that this is on SQL Express 2012.

Thank you,
Paul


#2

How are you rebuilding the user profiles?


#3

Need to search on the net... not sure now where found that discussion on this.

It was a 2 line command to remove and add back some rights but did not worked and not saved.


#4

It sounds like there might be a disconnect between the Login, at the SQL Server level, and the User, at the database level. Here is a template of a SQL script that we use to sync things back up after restoring a database:[code]-----------------------------------------------------------------------------
--- Generate and Execute SQL Statements

--- Description: Run from the current database, this script looks for Logins
--- and Users with the same name but different SID's. It generates, and
--- optionally runs, the code to bring the two entities into sync.

--- NOTE: Since it only runs on exceptions, this script can be run multiple times.


set nocount on;

declare
@PKey int = 1,
@MaxPKey int,

@sql			nvarchar(max),

@error_number	int,
@error_line		int,
@error_message	nvarchar(2048),

@Debug		smallint = 1,			--<<< SET TIHS VALUE !!!
	-- 0 = Ignore
	-- 1 = Print major events
	-- 2 = Select major and minor events

@Execute	bit = 1,				--<<< SET TIHS VALUE !!!
	-- 0 = Do NOT Execute
	-- 1 = Execute SQL Generated

@UnionAll	bit = 0					--<< SET THIS VALUE !!!
	-- Used when the script generated are a series of SELECTs and you want one resultset

declare
@Template nvarchar(1000) = N'
use [<>]; --<>
ALTER USER [<>]
WITH
<<DEFAULT_SCHEMA>>
LOGIN = [<>];';

declare @tbl table (
pkey int not null identity(1, 1),
sql nvarchar(max) not null
)


--- Populate table

-- Application specific logic: Start

insert into @tbl (
sql
)
select --* ,
replace(
replace(
replace(
replace(
@Template,
'<>',
dp.Name
),
'<>',
db_name()
),
'<>',
@@servername
),
'<<DEFAULT_SCHEMA>>',
case
when dp.type in ('G', 'C', 'K') then ''
else 'DEFAULT_SCHEMA = dbo,'
end
) sqlStmt
--select *
from
sys.database_principals dp
inner join
sys.server_principals sp
on dp.name = sp.name
where
1=1
and dp.type not in ('A', 'R') -- A = Application Role, R = Database Role
and dp.sid <> sp.sid
--and dp.default_schema_name <> 'dbo'
--and s.name like 'HMS\e00%'
and dp.name not in (
'guest',
'public',
''
)
order by
dp.name;

-- Application specific logic: End

set @MaxPKey = @@RowCount

--/**/select * from @tbl


--- Loop through table entries: Calculate table row count

begin try
while (@PKey <= @MaxPKey)
begin
---------------------------------------
--- Get single table entry
---------------------------------------

	select
		@sql	= sql
	from
		@tbl
	where
		PKey = @PKey

	---------------------------------------
	--- Save SQL statement
	---------------------------------------

	if @Debug & 1 > 0
		print @sql

	if @Debug & 2 > 0
		select @sql

	if @Execute = 1
		exec sp_ExecuteSql @sql

	---------------------------------------
	--- End-of-loop processing
	---------------------------------------

	set @PKey += 1;
end

print '--Complete!'

end try
begin catch
select
@error_number = error_number(),
@error_line = error_line(),
@error_message = error_message()

select
	@error_number ErrorNumber,
	@error_line ErrorLine, 
	@error_message ErrorMessage

raiserror('ERROR: %s (%li) at line %lu', 16, 0, @error_message, @error_number, @error_line) with log;

end catch[/code]You may need to modify this a bit if your login name doesn't map to the user name.
The other issue might be that the Login doesn't exist for the User in the database that got restored. You would need to create the Login first, then run this script.

NOTE: If you set the @Execute = 0, the code gets generated but not run. You can then review and modify it before turning it loose.

HTH


#5

@stephen_hendricks

Stephen, the SQL you supplied did exactly what I needed.
Thank you very much!