2

I want to allow for a certain domain user to be able to create databases on a remote SQL instance.

What steps are necessary and is it possible to perform those steps programmatically in a C# application ?

Should I first create a login if doesn't exist and then give it the dbcreator server role?

Related question: Safe and secure implementation of logon trigger in SQL Server 2014 Express edition?

2

Yes you would need a Login for that domain user to exist first. If all they need is the ability to manage databases (create, alter, drop, restore them) then the server role dbcreator should be sufficient.

If you anticipate multiple domain users needing access to this, then you should instead create a Login for a correlating Active Directory Group, so you don't have to continually manage setting up multiple Logins. You will then be able to map that AD Group to the server role dbcreator. (You can even use the built in "Domain Users" group if you wanted everyone on the domain to have access to creating databases.)

As far as how to accomplish this in C#, there's a multitude of ways, and that's perhaps a better question for StackOverflow because it's dependent on how you're accessing and querying your server instance. For example, with ADO.NET you could simply write a SQL query to check if a Login exists for the current user, if not create it, and assign the role. Or you can even create a procedure that takes in the current user of the application, and handles that logic for you (which will be more consumable depending on what database access framework you're using). But you'll likely have the easiest time managing this by creating an appropriate AD Group and mapping it to a Login with the dbowner role once.

9
  • 2
    dbcreator also gives permission to alter and drop any database – Bob Klimes Feb 3 at 19:59
  • Indeed is possible to have multiple domain users with the capability of creating databases on remote server. Not all the members from domain users shall be able to create databases, some of them will be only dbcreator and some of them will have the possibility only to have access on certain database and update . So it is possible to first create an active directory empty and assign the role dbcreator on that AD and after just start by adding users. The problem is that now I don't know for sure who shall be in that group, i shall give access only after a project is setup . – Elena2020 Feb 3 at 20:07
  • @Elena2020 Sounds good, so it sounds like an AD Group is the way to go, and while you're still developing you just need to add yourself or whatever test users you're testing with, to that AD account. – J.D. Feb 3 at 20:23
  • Any hints about how to create an ad and assign role? It is possible to handle this in my c# app – Elena2020 Feb 3 at 20:34
  • Or another ideea would be if possible for me to create the databases with my sysadmin or serveradmin account and then grant them db_owner role. – Elena2020 Feb 3 at 20:37
1

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):

GO
CREATE 
OR ALTER
PROCEDURE dbo.[ExampleProc]
(
    @DatabaseName sysname
)
AS
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];';

EXEC(@SQL);
GO

(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:

  1. 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];
    
  2. 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';
    
  3. Sign the module / stored procedure (this links the stored procedure to the permissions that do not yet exist):
    ADD SIGNATURE
       TO [dbo].[ExampleProc]
       BY CERTIFICATE [Permission$CreateDatabase]
       WITH PASSWORD = 'UseBetterPassword!';
    
  4. 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];
    
  5. Apply the permissions needed for the operation to the principal created from the certificate (the login in this case):
    ALTER SERVER ROLE [sysadmin] ADD MEMBER [Permission$CreateDatabase];
    
    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 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:

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.