August 12, 2011 at 2:19 pm
Hi Can some one provide me a query for Steps 2,4,5,6?and Moreover if any suggestions for the below steps i really appreciate that .
Thanks in advance.
Step1>Take the back of a database from Dev environment
Step2>Extract the security of database from test environment
Step3>Restore the database from Dev environment to test with replace.
Step4>Delete all the User Ids from restore database.
Step5>Apply security that was extracted in Test Environment.
Step6>Fix the Orphan users.
August 12, 2011 at 2:37 pm
Here's a thread from the other day with some of what you're looking for. Have you searched around much ?
http://qa.sqlservercentral.com/Forums/Topic1156166-146-1.aspx
August 12, 2011 at 3:47 pm
sp_help_revlogin << search MSDN KB for this for logins.
Getting all orphans back up... try this (I use something like it I modded from a Pinal Dave Blog, in a different way - modded a bit in case you can use):
(edit because I realized this had a bug in it and wanted to correct, sorry to re-awaken but if people are searching this I might as well make it right - also, could run into collation conflict for certain databases where I didn't set any explicit collation preferences in this version of the script)
master..sp_msforeachdb
'if db_id(''?'') > 4
begin
declare @table table
(pkey int identity(1,1),
username varchar(50),
schemaname varchar(50))
declare @name varchar(50),
@max-2 int,
@current int
set @current = 1
insert @table
SELECT us.name, isnull(sc.name,''dbo'')
FROM [?].dbo.sysusers as us
left join sys.schemas as sc on sc.name = us.name
join sys.syslogins on us.name = loginname
WHERE issqluser = 1
AND (us.sid IS NOT NULL
AND us.sid <> 0x0)
AND SUSER_SNAME(us.sid) IS NULL
ORDER BY us.name
select @max-2 = @@rowcount
while @current <= @max-2
begin
select @name = [username] from @table where pkey = @current
--EXEC sp_change_users_login ''update_one'',@name,@name /*disabled for testing needs but this commented out command*/
select ''update_one'',@name,@name,''[?]''
set @current = @current + 1
end
end';
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply