It is impossible to do this using permissions only .
The only way is to create a stored procedure as described here
If you want to avoid stored procedures, a workaround is:
GRANT ALL PRIVILEGES ONtestuser_%. * TO 'testuser'@'%'; (as suggested here); however, this has the problem that the users must then be very careful in naming their databases.
For example if user aaa creates database bbb_xyz, it can then be accessed exclusively by user bbb but not by user aaa.
In SQL Server Management Studio, expand the 'Security' node under the server instance in question.
Add the Windows Group in question to the Logins node.
Under the Server Roles section, leave the restricted groups, such as DEVS, QA, etc, as Public, and assign them access to the desired databases via the User Mapping node.
For the DBA group, assign them the sysadmin server role.
This will prevent databases being dropped / replaced by everyone except members of the DBA group.
Look at SQL Server Books Online for further details: http://msdn.microsoft.com/en-us/library/aa337552.aspx
In order to allow users to create databases, you could add them to the dbcreator role. Unfortunately, this role also allows members to DROP DATABASE, etc, as documented at http://msdn.microsoft.com/en-us/library/ms188659.aspx
A server-level DDL trigger could be created to prevent users performing DROP DATABASE such as:
USE master;
GO
CREATE TRIGGER DropDB ON ALL SERVER
FOR DROP_DATABASE
AS
BEGIN
IF COALESCE(IS_MEMBER('dba'),0) = 0 ROLLBACK;
END
GO
ENABLE TRIGGER DropDB ON ALL SERVER;
However, this does not prevent the user from executing RESTORE DATABASE commands.
Additional Info
Your requirements can be resolved by creating a stored procedure that allows users to create databases, whilst preventing them from performing the other actions allowed by membership in the dbcreator server role.
Details about how to create the stored procedure in a secure manner have been provided by @PaulWhite in his detailed answer here.
Best Answer
You can create a user that has certain non-db_owner fixed database roles assigned, and then you can create a user-defined role which contains additional permissions. (See https://msdn.microsoft.com/en-GB/library/ms189121.aspx)
So in effect, you can create a user that has all the rights of db_owner except say, managing users and dropping database. But I don't think there's a way to directly deny such privileges, you have to instead add everything but them.
Your database creation SP can configure new databases in this way, and you’d have to do it for each existing database.