20 Apr

Master Data Services: Security

Because, if the model doesn’t confuse you, the security model surely will.

Model Administrators

These have update permissions on a specific model, or models.   They only have access to areas they are assigned to.  There can be multiple model administrators.

System Administrator

They have update permissions to all models and they can add, update or delete them and there is only a single System Administrator account.

You cannot change the system administrator account using MDS.  You must use SSMS and T-SQL.   The following link explains the process: http://msdn.microsoft.com/en-us/library/ff487048.aspx.


Users and Groups

In order to get access to MDS a user must have either a Windows domain account or an account on the server where MDS is installed.

To give a user access to MDS you either ad their account  to the list of users in MDS or add them to a group that is in the list of groups in MDS.

To give a user, or group,  the ability to work in the explorer they must be given access to the explorer and model objects.

Functional Area Permissions

There are 5 functional areas:

  • Explorer
  • Version Management
  • Integration Management
  • System Administration
  • User and Group Permissions

Within the Explorer, permissions assigned to models and hierarchical objects, determine access.  In the other areas a user must be a model administrator to view and work on a model.


Object Permissions

  • Model: Apply to all entities, and collections within the model.  Permissions assigned to a model can be overridden by object permissions.
  • Entity: Apply to the entity’s attributes, collections and explicit hierarchies.
  • Leaf: Apply to all attribute values for leaf members.
  • Consolidated: Apply to all attribute values for consolidated members of an entity.
  • Collections: Apply to all collections for an entity.

Permissions can be broken down into 3 groups:

  • Read-only: Can’t add, remove or update.
  • Update: Can add, remove and update.
  • Deny:  Can do nothing.

There is so variance amongst the different objects but I’m going to take my chances on those.

It is considered a best practice to grant the update permission to the model object and then explicitly permissions beneath it.  If you do not apply these permissions then the objects inherit permissions.


Navigational Access

This was, to me, a confusing concept.  In essence, what they are doing here is saying, if you get access at a given level, you also get access to some things further up the tree.  These work as follows:

  • Entities: You can read or update the code and name for all members, and read the model name.
  • Attributes: You can read or update the code and name for all members in the entity, and read the model name.
  • Collections: You can read or update the name, code, description and ownerID.  You can also read the model name.


Hierarchy Permissions

Microsoft say’s they’re optional.  I’m going with that.  Here’s a link.


Overlapping User And Group Permissions

  • Deny overrides all other permissions.
  • Update overrides Read-only.

Overlapping Model And Member Permissions

  • Deny overrides all other permissions.
  • Read-only overrides Update.

Here’s to hoping that’s most, if not everything, and that I didn’t blow anything too terribly.

12 Apr

SSISDB Security

SSISDB is that folder over on the left, in SSMS, under “SSIS Integration Catalogs”.  Security here isn’t all that different from other places in SQL Server, in the sense that it has roles, and permissions, but, it is different.  To access permissions you navigate into the SSISDB folder, right-click on a folder or object, select Properties and then the Permissions tab on the left.

The permissions tab allows you to Grant or Deny permissions but you can also Revoke permissions with a stored procedure (gonna hope that doesn’t show up).  There are up to 9 permissions depending on where you are in the hierarchy.

NameWhat I hope it does.
ReadCan read the object's properties but not for other objects contained in the object.
ModifyCan modify the object's properties but not for other objects contained in the object.
Manage PermissionsCan assign permissions to objects.
Create ObjectsCan create objects in the folder. (Folder only)
Modify ObjectsCan modify objects in the folder. (Folder only)
Execute ObjectsCan execute objects in the folder. (Folder only)
Read ObjectsCan read objects in the folder. (Folder only)
Manage Object PermissionsCan manage permissions on all objects in the folder.
(Folder only)
ExecuteExecute all packaged in the project.
The source for this table is the Microsoft 70-463 Training Kit.

One thing to note is that packages inherit permissions from their containing project and are not assigned to them directly.

Any SSISDB user can deploy projects.  And any objects created by a user (including environments and operations, but mainly projects) can read, modify or execute (projects only) that object.

Don't push the Red Button!

Don’t push the Red Button!

There is a single role for the SSISDB catalog: ssis_admin.  It’s your typical admin role in that it can do anything to any project in the catalog.  The default user for this role is the user who created the catalog.

Finally, users must be granted access to the SSISDB catalog before they can use it.  Upon creation no users will have access to it.

My primary source was the Microsoft 70-463 Training Kit.  I have to be fair and admit that I’m developing more respect for that book.  Parts of it are actually quite good and this is a topic that I couldn’t find in the msdn.  I’m going to have to do another review on that book if this keeps up.

27 Mar

SSIS: Package and Project Security Properties.

These are more, or less, the same thing, in that, they work the same way with the same settings.  The following settings apply at both the package and the project level.

Project: Right-click Project > Properties > Common

Package: Right-click Designer > Properties


  • DontSaveSensitive: Sensitive information is not saved.  If a different user opens the package / project they must enter this information.
  • EncryptAllWithPassword: Encrypts the entire package or project with a password.  The package / project can not be accessed or run without providing the password.
  • EncryptAllWithUserKey: Encrypts the package with a key based on the user’s profile.  Only this user can use or run the project / package.
  • EncryptSensitiveWithPassword: Encrypts only sensitive data with a password.  This package or project can be accessed in the designer without a password but sensitive information must be reentered.  The package cannot be run without the password.
  • EncryptSensitiveWithUserKey: This is the default setting.  Encrypt sensitive information with a key based on the user’s profile.  The package or project can be accessed in the design but sensitive information must be reentered.  The package cannot be run without the password.

The only real different between the package and project security properties is that the UI is slightly different.  Consistency is the hobgoblin of small minds in the SSIS interface.

You can read more here.