Here's a little SQL script that will do it for you. Please read thru the comments as you'll have to change the database names to suit your needs.
*** DISCLAIMER: While I've used this extensively and know that it works just fine, please don't use it if you're unfamiliar with T-SQL. Oh, and ALWAYS backup BEFORE you mess with your databases. ***
-- Initial Database
USE [KenticoCMS]
GO
-- Alter existing tables
exec sp_rename 'CMS_Role', 'CMS_Role_old'
exec sp_rename 'CMS_User', 'CMS_User_old'
exec sp_rename 'CMS_UserRole', 'CMS_UserRole_old'
ALTER TABLE CMS_RolePermission
DROP CONSTRAINT [FK_CMS_RolePermission_CMS_Role]
GO
-- Create new shared database.
CREATE DATABASE [KenticoUsers]
GO
USE [KenticoUsers]
GO
-- Create tables
CREATE TABLE [CMS_User] (
[UserID] [int] IDENTITY (1, 1) NOT NULL ,
[UserName] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[FirstName] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MiddleName] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LastName] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FullName] [nvarchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Email] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UserPassword] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[PreferredUICultureCode] [nvarchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PreferredContentCultureCode] [nvarchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[StartingAliasPath] [nvarchar] (450) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
CONSTRAINT [PK_CMS_User] PRIMARY KEY CLUSTERED
(
[UserID]
) WITH FILLFACTOR = 90 ON [PRIMARY] ,
CONSTRAINT [IX_CMS_User] UNIQUE NONCLUSTERED
(
[UserName]
) WITH FILLFACTOR = 90 ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [CMS_Role] (
[RoleID] [int] IDENTITY (1, 1) NOT NULL ,
[RoleDisplayName] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[RoleName] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
CONSTRAINT [PK_CMS_Role] PRIMARY KEY CLUSTERED
(
[RoleID]
) WITH FILLFACTOR = 90 ON [PRIMARY] ,
CONSTRAINT [IX_CMS_Role] UNIQUE NONCLUSTERED
(
[RoleName]
) WITH FILLFACTOR = 90 ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [CMS_UserRole] (
[UserID] [int] NOT NULL ,
[RoleID] [int] NOT NULL ,
CONSTRAINT [PK_CMS_UserRole] PRIMARY KEY CLUSTERED
(
[UserID],
[RoleID]
) WITH FILLFACTOR = 90 ON [PRIMARY] ,
CONSTRAINT [FK_CMS_UserRole_CMS_Role] FOREIGN KEY
(
[RoleID]
) REFERENCES [CMS_Role] (
[RoleID]
),
CONSTRAINT [FK_CMS_UserRole_CMS_User] FOREIGN KEY
(
[UserID]
) REFERENCES [CMS_User] (
[UserID]
)
) ON [PRIMARY]
GO
-- Add admin user
INSERT INTO [CMS_User]
([UserName], [FirstName], [LastName], [FullName], [Email], [UserPassword])
VALUES('admin', 'Default', 'Admin', 'Default Admin', 'admin@altn.com', '')
GO
-- Add roles
INSERT INTO [CMS_Role]
([RoleDisplayName], [RoleName])
VALUES('CMS Editors', 'CMS.Editors')
INSERT INTO [CMS_Role]
([RoleDisplayName], [RoleName])
VALUES('CMS Administrators', 'CMS.Administrators')
INSERT INTO [CMS_Role]
([RoleDisplayName], [RoleName])
VALUES('CMS Developers', 'CMS.Developers')
INSERT INTO [CMS_Role]
([RoleDisplayName], [RoleName])
VALUES('CMS File Editors', 'CMS.FileEditors')
-- Get inserted record ID.
DECLARE @ID int
SET @ID = (SELECT [UserID] FROM [CMS_User] WHERE [UserName] = 'admin')
-- Assign roles to user
DECLARE @RoleID int
DECLARE Role_Cursor CURSOR FOR
SELECT [RoleID] FROM [CMS_Role]
OPEN Role_Cursor
FETCH NEXT FROM Role_Cursor
INTO @RoleID
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO [CMS_UserRole]
([UserID], [RoleID])
VALUES(@ID, @RoleID)
FETCH NEXT FROM Role_Cursor
INTO @RoleID
END
CLOSE Role_Cursor
DEALLOCATE Role_Cursor
GO
-- This is the target DB as well.
USE [KenticoCMS]
GO
-- Create views
CREATE VIEW [CMS_Role]
AS
(SELECT * FROM [KenticoUsers].dbo.[CMS_Role])
GO
CREATE VIEW [CMS_User]
AS
(SELECT * FROM [KenticoUsers].dbo.[CMS_User])
GO
CREATE VIEW [CMS_UserRole]
AS
(SELECT * FROM [KenticoUsers].dbo.[CMS_UserRole])
GO
-- These are updated because new entries have different ids than default ones.
UPDATE CMS_RolePermission
SET RoleID = 1 WHERE RoleID = 8
UPDATE CMS_RolePermission
SET RoleID = 2 WHERE RoleID = 9
UPDATE CMS_RolePermission
SET RoleID = 3 WHERE RoleID = 10
UPDATE CMS_RolePermission
SET RoleID = 4 WHERE RoleID = 11