Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Is there a way of copying the securables definitions from 1 db to another? Expand / Collapse
Author
Message
Posted Thursday, September 5, 2013 8:49 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Friday, June 20, 2014 8:23 AM
Points: 738, Visits: 1,305
We're finally moving to SQL 2012 from SQL 2005. Is there a way of automating the moving or copying of securables from the old database for some of the users, to the new database for the same users? If so, how is that done?

Kindest Regards,

Rod
Connect with me on LinkedIn.
Post #1491826
Posted Thursday, September 5, 2013 9:12 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 10:25 AM
Points: 301, Visits: 595
No. What procedures are you taking to move over your databases from 05 to 12? Backup and restore I hope!

MCSA SQL Server 2012
Post #1491849
Posted Friday, September 6, 2013 6:43 AM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Friday, September 12, 2014 6:31 AM
Points: 617, Visits: 866
Rod at work (9/5/2013)
We're finally moving to SQL 2012 from SQL 2005. Is there a way of automating the moving or copying of securables from the old database for some of the users, to the new database for the same users? If so, how is that done?


As "I.T.D." said, backup old DBs, then restore them to new server instance. If the server logins that relate to the DB Users exist on the destination when the restore takes place, the securables will align with those logins. Otherwise they will be orphaned. When the appropriate logins are added after the restore, they can be quickly fixed by use of
sp_Change_Users_Login 'Auto_Fix', 'UserName'


Regards, Mike
Post #1492197
Posted Friday, September 6, 2013 8:07 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 10:25 AM
Points: 301, Visits: 595
Rod, did you understand what Mike is saying? Please PM me if you need more explaination.

MCSA SQL Server 2012
Post #1492257
Posted Friday, September 6, 2013 10:19 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Friday, June 20, 2014 8:23 AM
Points: 738, Visits: 1,305
Hi Mike and It's The Database,

Your reply, Mike, made me realize that I left out some important details. Our current SQL 2005 database is on a server on-premise. The new SQL 2012 database will be on a VM, on a server hosted by our parent company. So the hardware aspect of our operation is moving off site. I'm pretty sure that those users and accounts will at least change names, on the new machine/VM.


Kindest Regards,

Rod
Connect with me on LinkedIn.
Post #1492327
Posted Friday, September 6, 2013 10:24 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 10:25 AM
Points: 301, Visits: 595
Rod,

you can one of two things here.

a) create the current users under security and once you restore they will sync up.

b) new site new names, no worries, restore the database, delete the old users from the database then add the new user names in security.

pretty simple


MCSA SQL Server 2012
Post #1492334
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse