What exactly do you mean by "remote"? Meaning on the network, just not hosted on their computer?
In general, you don't need to assign any elevated permissions to any person or application user. You only need to create a stored procedure that does the very specific steps required for this operation, grant the required permission to the stored procedure, and then grant this one domain user
EXECUTE on this "Create Database" stored procedure. In this way, the domain user won't have any actual elevated permissions. If the stored procedure does nothing more than create a database, then that's all that domain user will be able to do, and only by executing that stored procedure.
For example, assume you have a stored procedure like the following that enforces a naming convention of two underscores before and after the DB name (this example also assumes that the objects are in the
[master] database, which I typically try to avoid, but it does simplify things a little):
SET NOCOUNT ON;
DECLARE @AlteredName sysname = QUOTENAME(N'__' + @DatabaseName + N'__');
DECLARE @SQL NVARCHAR(MAX) = N'CREATE DATABASE ' + @AlteredName + N'
ALTER AUTHORIZATION ON DATABASE::' + @AlteredName + N' TO [sa];';
(Note: The database is owned by the login that created it, hence we change the ownership so that same login can't drop the database)
All you would need to do is the following:
- Allow the domain user to execute this stored procedure (at this point the domain user will get an error due to not having permission to create databases):
GRANT EXECUTE ON dbo.[ExampleProc] TO [Domain\AccountWhoCreatesTheDBs];
- Create a Certificate (this will be used to link the code / stored proc with the permissions):
CREATE CERTIFICATE [Permission$CreateDatabase]
ENCRYPTION BY PASSWORD = 'UseBetterPassword!'
WITH SUBJECT = 'CREATE DATABASE permission',
EXPIRY_DATE = '2099-12-31';
- Sign the module / stored procedure (this links the stored procedure to the permissions that do not yet exist):
BY CERTIFICATE [Permission$CreateDatabase]
WITH PASSWORD = 'UseBetterPassword!';
- Create a login from the certificate (this will contain the necessary permissions, and is linked to the stored procedure since the login is created from the same certificate that was just used to sign the stored procedure):
CREATE LOGIN [Permission$CreateDatabase]
FROM CERTIFICATE [Permission$CreateDatabase];
- Apply the permissions needed for the operation to the principal created from the certificate (the login in this case):
This permission, granted to the certificate-based login, will be given to the stored procedure, not to the domain user! This level of permission is needed in order to change DB ownership to
ALTER SERVER ROLE [sysadmin] ADD MEMBER [Permission$CreateDatabase];
sa, otherwise adding the certificate-based login to
dbcreator would have been fine.
Once that is done, the domain user:
- will not be able to execute this:
CREATE DATABASE [__TEST__];
- will be able to execute this (successfully):
EXEC dbo.ExampleProc N'TEST';
- will not be able to execute this (due to changing the ownership of the DB):
DROP DATABASE [__TEST__];
Keep in mind that when signing a stored procedure (or any module), the signature is based on the code of that module, so if that code ever changes, the signature (and the linked permissions) are lost. This requires re-signing the module to get the permission(s) back, but that's a good thing because it ensures that the code can't be changed without alerting you (or some DBA) that something changed.
For more details / examples, please see my blog posts on this topic: