Sql-server – Permissions to Create DB’s and be able to perform any function only on those DB’s

permissionssql-server-2008-r2

I am looking for a way to allow users to create databases which only they and SA accounts will have access to (Perform any function; creating/deleteing tables, deleting the DB, etc).

For example, User1 creates DB1 and User2 creates DB2. User1 must have full access to DB1 but no access to DB2 and User2 will have full access to DB2 but no access to DB1.

Thanks

Best Answer

I think you need to clear up the distinction between logins (security entities at the server level) and users (security entities at the database level, which may be associated with a server-level login).

What you want to do here is grant CREATE ANY DATABASE to the server-level login. For example:

CREATE LOGIN foo WITH PASSWORD = N'bar', CHECK_POLICY = OFF;
GO
GRANT CREATE ANY DATABASE TO foo;
GO

Now, log in as foo in another window, and note that they can do this:

CREATE DATABASE foo_1;
GO
USE foo_1;
GO
CREATE TABLE dbo.bar_1(foo_what INT);
GO

But as soon as they try to use a database they did not create:

USE AdventureWorks2014;
GO

They are blocked:

Msg 916, Level 14, State 1
The server principal "foo" is not able to access the database "AdventureWorks2014" under the current security context.

Related Question