September 9, 2009 at 9:44 am
Is is possible to add security to an SSIS packages so only certain people can add new packages, edit packages and run packages.
cheers
September 9, 2009 at 1:06 pm
You can add security to the specific dtsx file by going to the package properties and finding packagepassword and protectionlevel. The only thing is you have to give that password out to people. You could always just put them in a folder and control the folder security.
September 9, 2009 at 1:45 pm
I would suggest looking into the db_dtsadmin, db_dtsltduser, and db_dtsoperator roles in the MSDB database.
Also from BOL
SQL Server 2005 Books Online (November 2008)
Integration Services Roles
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/extran9/html/9702e90c-fada-4978-a473-1b1423017d80.htm
Updated: 17 July 2006
SQL Server 2005 Integration Services (SSIS) includes the three fixed database-level roles—db_dtsadmin, db_dtsltduser, and db_dtsoperator—for controlling access to packages. Roles can be implemented only on packages that are saved to the msdb database in SQL Server. You assign roles to a package using SQL Server Management Studio. The role assignments are saved to the msdb database.
The following table describes the read and write actions of Windows and fixed database-level roles in Integration Services.
Role Read action Write action
db_dtsadmin
or
sysadmin
Enumerate own packages.
Enumerate all packages.
View own packages.
View all packages.
Execute own packages.
Execute all packages.
Export own packages.
Export all packages.
Execute all packages in SQL Server Agent.
Import packages.
Delete own packages.
Delete all packages.
Change own package roles.
Change all package roles.
db_dtsltduser
Enumerate own packages.
Enumerate all packages.
View own packages.
Execute own packages.
Export own packages.
Import packages.
Delete own packages.
Change own package roles.
db_dtsoperator
Enumerate all packages.
View all packages.
Execute all packages.
Export all packages.
Execute all packages in SQL Server Agent.
None
Windows administrators
View execution details of all running packages.
Stop all currently running packages.
The sysdtspackages90 table in msdb contains the packages that are saved to SQL Server. For more information, see sysdtspackages90 (Transact-SQL).
The sysdtspackages90 table includes columns that contain information about the roles that are assigned to packages.
The readerrole column specifies the role that has read access to the package.
The writerrole column specifies the role that has write access to the package.
The ownersid column contains the unique security identifier of the user who created the package. This column defines the owner of the package.
By default, the permissions of the db_dtsadmin, and dtsoperator fixed database-level roles and the unique security identifier of the user who created the package apply to the reader role for packages, and the permissions of the db_dtsadmin role and the unique security identifier of the user who created the package apply to the writer role. A user must be a member of the db_dtsadmin, db_dtsltduser, or db_dtsoperator role to have read access to the package. A user must be a member of the db_dtsadmin role to have write access.
The fixed database-level roles work in conjunction with user-defined roles. The user-defined roles are the roles that you create in SQL Server Management Studio and then use to assign permissions to packages. To access a package, a user must be a member of the user-defined role and the pertinent Integration Services fixed database-level role. For example, if users are members of the AuditUsers user-defined role that is assigned to a package, they must also be members of db_dtsadmin, db_dtsltduser, or db_dtsoperator role to have read access to the package.
If you do not assign user-defined roles to packages, access to packages is determined by the fixed database-level roles.
If you want to use user-defined roles, you must add them to the msdb database before you can assign them to packages. You can create new database roles in SQL Server Management Studio.
Step 1: Open Object Explorer and Connect to Integration Services
Before you can assign roles to packages by using SQL Server Management Studio, you must open Object Explorer in SQL Server Management Studio and connect to Integration Services.
The Integration Services service must be started before you can connect to Integration Services.
To open Object Explorer and connect to Integration Services
Open SQL Server Management Studio.
Click Object Explorer on the View menu.
On the Object Explorer toolbar, click Connect, and then click Integration Services.
In the Connect to Server dialog box, provide a server name. You can use a period (.), (local), or localhost to indicate the local server.
Click Connect.
Step 2: Assign Reader and Writer Roles to Packages
You can assign a reader and a writer role to each package.
Assign a reader and writer role to a package
In Object Explorer, locate the Integration Services connection.
Expand the Stored Packages folder, and then expand the subfolder that contains the package to which you want to assign roles.
Right-click the package to which you want to assign roles.
In the Packages Roles dialog box, select a reader role in the Reader Role list and a writer role in the Writer Role list.
Click OK.
Create a User-Defined Role
SQL Server (the MSSQLSERVER service) must be started before you can connect to the Database Engine and access the msdb database.
To create a user-defined role
Open SQL Server Management Studio.
Click Object Explorer on the View menu.
On the Object Explorer toolbar, click Connect, and then click Database Engine.
In the Connect to Server dialog box, provide a server name and select an authentication mode. You can use a period (.), (local), or localhost to indicate the local server.
Click Connect.
Expand Databases, System Databases, msdb, Security, and Roles.
In the Roles node, right-click Database Roles, and click New Database Role.
On the General page, provide a name and optionally, specify an owner and owned schemas and add role members.
Optionally, click Permissions and configure object permissions.
Optionally, click Extended Properties and configure any extended properties.
Click OK.
CEWII
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply