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?

Improve this question

2 Answers 2

Reset to default
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.

Improve this answer
9
  • 2
    dbcreator also gives permission to alter and drop any database Commented Feb 3, 2021 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 . Commented Feb 3, 2021 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. Commented Feb 3, 2021 at 20:23
  • Any hints about how to create an ad and assign role? It is possible to handle this in my c# app Commented Feb 3, 2021 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. Commented Feb 3, 2021 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:

Improve this answer

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.