SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

SQL Clone Server Service Permissions

SQL Clone is amazing, and it can really save time and disk space for many organizations. I’ve got a series posted here on various little things I’ve learned about the product. There are also a number of articles on the Redgate Community Hub.

I was working on helping a customer install the SQL Clone server recently and one of the things that the client wanted to know was what are the minimum permissions needed for the SQL Clone Server.

When you install the SQL Clone server, the configuration dialog asks you for a Windows account and password. This is noted in the documentation as the account that configures and starts the server.

sqlcloneserver

This means that during the configuration, this account will:

  • Create a local service on the Clone Server OS
  • Connect to the SQL Server specified
  • Create a new database (or use the one that exists)
  • Map itself to dbo in the new database

If the SQL Server can be a remote SQL Server from the SQL Clone server, a domain account is needed. If this is a local SQL Server, then you can use a local account. The account does need to have local administrator privileges.

With that in mind, here’s what I did as a minimum permission set:

  • Create a new domain account, SQLCloneServer (I want to be able to use a remote SQL Server. I left this as just a member of Domain Users.
  • Add this account as a local administrator on the SQL Clone server host.
  • Add this AD user as a login to the SQL Server that will host the configuration databse
  • Give the SQL user the dbcreator role (you can remove this later and leave them with permissions inside the db)

That’s it.

Scripting

It’s always better to script. Here’s the AD part in PowerShell:

New-ADUser -Name “SQL Clone Server” -GivenName “SQL” -Surname “Clone Server” -SamAccountName “SQLCloneServer” -UserPrincipalName SQLCloneServer@mydomain.com

Here’s the local SQL Clone, web server permissions part, using local commands. This could be in PoSh, but it’s not as clean (to me).

net localgroup Administrators "MyDomain\SQLCloneServer" /add

Here’s the SQL Part

USE [master]
GO
CREATE LOGIN [MYDOMAIN\MySQLCloneUser] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
GO
ALTER SERVER ROLE [dbcreator] ADD MEMBER [MYDOMAIN\MySQLCloneUser]
GO

Filed under: Blog Tagged: Redgate, SQL Clone, syndicated

The Voice of the DBA

Steve Jones is the editor of SQLServerCentral.com and visits a wide variety of data related topics in his daily editorial. Steve has spent years working as a DBA and general purpose Windows administrator, primarily working with SQL Server since it was ported from Sybase in 1990. You can follow Steve on Twitter at twitter.com/way0utwest

Comments

Leave a comment on the original post [voiceofthedba.com, opens in a new window]

Loading comments...