3

I've developed a set of SSIS packages to load a data warehouse. In order to manage any issue that may arise during package execution, each package starts by creating a snapshot of the data warehouse.

The package authenticates on my server using a login that does not belongs to the dbcreator server role. Obviuosly with this configuration the package fails due to the lack of permission to create/drop a database.

The dbcreator server role allows a user to create/drop a database on the whole instance. My question is: Is there a way to grant a database user the permission to create a snapshot of the given database (and only that) and eventually drop the snapshot (and only the snapshot associated to the given database)?

What are the best practices to handle this requirement?

Improve this question
0

1 Answer 1

Reset to default
1

Its not possible to do straight away as mentioned on MS documentation:

Permissions Any user who can create a database can create a database snapshot; however, to create a snapshot of a mirror database, you must be a member of the sysadmin fixed server role.

What I understand is - if you could have alternative in place for DB creator role, this purpose can be achieved and this has been explained very well by one of MSSQL SME Mr. Solomon Rutzky

You can achieve this by using module signing which has been explained by Mr. Tibor Nagy on this link --> https://www.mssqltips.com/sqlservertip/3336/how-to-use-module-signing-for-sql-server-security/

You can also refer the question and answer thread for better understanding of module signing at the same forum and how can this be used:

Granting a user a permission to restore a database

Execute Permissions for a Store Procedure that creates databases

https://sqlquantumleap.com/2018/02/15/safely-and-easily-use-high-level-permissions-without-granting-them-to-anyone-server-level/

More about Module signing at Microsoft:

https://learn.microsoft.com/en-us/previous-versions/sql/sql-server-2008/ms345102(v=sql.100)

Hope this leads you into right direction.

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.