Sql-server – How to enable a SQL Server 2012 login to create, alter and drop the created databases

permissionsSecuritysql serversql-server-2012

I'm trying to assign a proper GRANT permission to CI login, that will be able to:

Create test DB
Alter it
Drop this test DB (and only it)

But it appears that SQL Server 2012 GRANT CREATE ANY DATABASE only allows the creation, not the altering and dropping of the database (as the newly database does not hold the creator as dbo).

As just giving a sysadmin role is too large of a security risk, any idea what other GRANTS are required?

Best Answer

First you have to

Create Login with create database permission. Note that the login cannot drop any databases.

USE [master]
GO

CREATE LOGIN [Kin] WITH PASSWORD=N'somePassw0rd', DEFAULT_DATABASE=[master],
DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
use [master]
GO
-- Grant create any database, but not drop or alter any database
 GRANT CREATE ANY DATABASE TO [Kin]
GO

Map the login to the database that you want to have permission to alter or drop.

-- for drop database, you just need db_owner
USE [test_drop]
GO
CREATE USER [Kin] FOR LOGIN [Kin] 
 GO

Add the user to the db_owner role.

  EXEC sp_addrolemember 'db_owner', 'Kin'

Now test it for create / alter and drop :

EXECUTE AS LOGIN = 'Kin'

create database test_drop


EXECUTE AS LOGIN = 'Kin'
use test_drop

create table test (fname char(1))


EXECUTE AS LOGIN = 'Kin'
select * from test

EXECUTE AS LOGIN = 'Kin'
alter database test_drop 
set offline

EXECUTE AS LOGIN = 'Kin'
alter database test_drop 
set online

EXECUTE AS LOGIN = 'Kin'
drop database [test_drop]

Try to drop some other db :

EXECUTE AS LOGIN = 'Kin'
alter database [test_kin] 
set online

Below is the error :

Msg 5011, Level 14, State 9, Line 2 User does not have permission to alter database 'test_kin', the database does not exist, or the database is not in a state that allows access checks. Msg 5069, Level 16, State 1, Line 2 ALTER DATABASE statement failed.

Related Solutions

Sql-server – How to create a read-only server role on SQL Server 2012

Posting this as an answer just because it is too long for a comment, and because it will be relevant to other users pretty soon.

SQL Server 2014 adds some new server-level permissions that will assist with exactly this type of scenario - they were designed with auditing in mind, but this type of requirement seems to fit that bill as well. You could simply add the following two permissions to a server-level login:

CONNECT ANY DATABASE

SELECT ALL USER SECURABLES

The former, like it sounds, allows the login to connect to any database. The nice thing about this is that it will allow this even for databases that are created in the future (provided you don't set explicit deny, which is how you can protect certain user databases from logins that have this permission). The latter allows the login to perform read operations on any database they have access to - so they can SELECT from tables, views, UDFs etc. but they will not be able to perform any UPDATE operations (I haven't tested if this permission understands when a stored procedure performs DML). These work great in combination if you want to give a login wide-open read access to the entire server, or to be more fine-grained you can grant traditional CONNECT privileges to certain databases, and the SELECT ALL USER SECURABLES right will only function for those databases where the login has explicit access to.

The 2014 security changes are documented here - well, partially; they forgot about the database-level permission ALTER ANY DATABASE EVENT SESSION - though that's not relevant here.

SQL Server Permissions – How to Allow Developers to View Query Plan Without Running Trace

DENY trumps GRANT, so yeah, you can’t let people view plans if you’ve explicitly denied them the ability to alter trace. But just because you haven’t denied a right doesn’t mean it has been granted.

This example shows that your database users can run queries and collect the execution plans within a database where they have been granted SHOWPLAN (and without being added to the db_owner role), as long as the server-level login has not been explicitly denied the ability to ALTER TRACE.

It also shows that, unless you explicitly grant any trace-related permissions, they won't be able to read trace data, or even be able to tell there are traces running at all, unless they have inherited those rights in some other way (Windows group, server role, etc). So you don't need to explicitly deny ALTER TRACE in order to protect trace data from these users - just be aware that it is possible for them to can inherit it without an explicit grant.

USE master;
GO
CREATE LOGIN blob WITH PASSWORD = 'x', CHECK_POLICY = OFF;
GO
CREATE DATABASE floob;
GO
USE floob;
GO
CREATE USER blob FROM LOGIN blob;
GO
GRANT SHOWPLAN TO blob;
GO
CREATE TABLE dbo.splunge(plonk INT);
GO
GRANT SELECT ON dbo.splunge TO blob;
GO
EXECUTE AS USER = 'blob';
GO
SET SHOWPLAN_XML ON;
GO
-- plan is shown:
SELECT plonk FROM dbo.splunge;
GO
SET SHOWPLAN_XML OFF;
GO
-- error:
EXEC sp_trace_getdata @traceid = 1;
GO
-- empty result set:
SELECT * FROM sys.traces;
GO
REVERT;
GO
USE master;
GO 
DROP DATABASE floob;
DROP LOGIN blob;

Best Answer

Related Solutions

Sql-server – How to create a read-only server role on SQL Server 2012

SQL Server Permissions – How to Allow Developers to View Query Plan Without Running Trace

Related Question