You plan to deploy a new application. The application will perform the following operations:
• Create a new database
• Back up the new database
You need to configure a login to support the deployment of the new application. The solution must ensure that the application uses the most restrictive permissions possible.
What should you do?
A. Add the login to the dbcreator server role.
B. Add the login to the diskadmin and serveradmin server roles.
Once the database is created, add a user to the db_backupoperator database role.
C. Add the login to the diskadmin and securityadmin server roles.
Once the database is created, add a user to the db_backupoperator database role.
D. Add the login to the sysadmin server role.
Correct Answer: A
Explanation/Reference:
Members of the dbcreator fixed server role can create databases, and can alter and restore their own databases.
USE master
GO
CREATE LOGIN Login1 WITH PASSWORD=N’1234567′
EXEC sp_addsrvrolemember @loginame = N’Login1′, @rolename = N’dbcreator’
EXECUTE AS Login=’Login1′
SELECT CURRENT_USER -- return: guest
GO
CREATE DATABASE TestDB ON PRIMARY
( NAME = N’TestDB’, FILENAME = N’C:TestDB.mdf’ , SIZE = 14336KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
LOG ON
( NAME = N’TestDB_log’, FILENAME = N’C:TestDB_log.ldf’ , SIZE = 43264KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO
--Command(s) completed successfully.
As soon as a new database is created, Login1 is the owner of the database and it is mapped to dbo user which has db_owner role in that database. db_owner and db_backupoperator fixed database roles have permissions to BACKUP DATABASE and BACKUP LOG.
USE TestDB
GO
SELECT CURRENT_USER -- return: dbo
BACKUP DATABASE TestDB
TO DISK = N’c:TestDB.bak’ WITH DESCRIPTION = N’Test’, NOFORMAT, INIT, NAME = N’TestDB-Full Database Backup’, SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
--BACKUP DATABASE successfully processed xxx pages in xxx seconds.
