How to ignore security settings in SQL Server Database Project (SSDT)

In SSDT project, you could create everything you want and keep them identical with target database. However, security might be an exception, few reason you might want to manage security out side of SSDT project:

  • You need to create a login in order to map a database user, and grant user whatever premission.
  • You may need to update permission for logins/users which are not yet in SSDT project.
  • SSDT project may be managed via a source control, like git and build&deploy via CI/CD tools, but different environments have different security principals. This could be a challenge to manage all security in SSDT project.

In these scenarios, you could create nothing about login/user objects in SSDT, but instead manage full security out side via some other methodology. To do so, you could make corresponding build profile settings:

SSDT Project -> Publish -> Advanced -> Drop (6 options to selected).SSDT_Build_Profile

In the build profile, there are corresponding options:

<DropObjectsNotInSource>True</DropObjectsNotInSource>
<DoNotDropLogins>True</DoNotDropLogins>
<DoNotDropUsers>True</DoNotDropUsers>
<DoNotDropServerRoles>True</DoNotDropServerRoles>
<DoNotDropServerRoleMembership>True</DoNotDropServerRoleMembership>
<DoNotDropRoleMembership>True</DoNotDropRoleMembership>
<DoNotDropPermissions>True</DoNotDropPermissions>
<DoNotDropDatabaseRoles>True</DoNotDropDatabaseRoles>

Let me know if you have any comments.

Advertisements

About Alex Feng

SQL Server DBA in Alibaba Cloud Computing
This entry was posted in SSDT, Uncategorized and tagged , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s