patterncsharpMinor
SSIS 2012: What rights are needed to connect to SSDB via DMO
Viewed 0 times
neededconnect2012whataressisrightsviassdbdmo
Problem
I'm trying to connect to the SSDB catalog to execute a package using the .NET object model e.g.
If I run this I get an error
If I grant the user sysadmin rights it works, so it's definitely a permission issue, but I can't use that as the solution.
Under the old Integration Services model you would make the user a member of db_ssisadmin, db_ssisdtuser or db_ssisoperator - tried that and it doesn't help :-(
using Microsoft.SqlServer.Management.IntegrationServices;
var sqlConn = new SqlConnection(connectionString);
var ssis = new IntegrationServices(sqlConn);
var project = ssis.Catalogs["SSISDB"].Folders[package.Path].Projects[package.Project];
var pinfo = project.Packages[package.Name + ".dtsx"];
var id = pinfo.Execute(false, env, packageParams)If I run this I get an error
The server principal "XXXX" is not able to access the database \"SSISDB\" under the current security contextIf I grant the user sysadmin rights it works, so it's definitely a permission issue, but I can't use that as the solution.
Under the old Integration Services model you would make the user a member of db_ssisadmin, db_ssisdtuser or db_ssisoperator - tried that and it doesn't help :-(
Solution
The answer is contained in Link
1.To allow a login to be able to read/execute only one project, but not able to access other objects (projects or environments) in a
folder where the project is in: a.Map it to a member of the SSISDB
database role -- public. (This leverages SQL security mechanism.)
b.Grant it Read to the folder, and grant it Read/Execute to the
project. (This uses SSIS Catalog security mechanism.)
2.To allow a login (user or group) to be able to read/execute all projects in a folder: a.Map it to a member of the SSISDB database role
-- public. b.Grant it Read/Execute/Read Objects to the folder.
3.To allow a login to be able to do anything on SSISDB: a.make it a member of the SSISDB database role -- ssis_admin.
1.To allow a login to be able to read/execute only one project, but not able to access other objects (projects or environments) in a
folder where the project is in: a.Map it to a member of the SSISDB
database role -- public. (This leverages SQL security mechanism.)
b.Grant it Read to the folder, and grant it Read/Execute to the
project. (This uses SSIS Catalog security mechanism.)
2.To allow a login (user or group) to be able to read/execute all projects in a folder: a.Map it to a member of the SSISDB database role
-- public. b.Grant it Read/Execute/Read Objects to the folder.
3.To allow a login to be able to do anything on SSISDB: a.make it a member of the SSISDB database role -- ssis_admin.
Context
StackExchange Database Administrators Q#93232, answer score: 2
Revisions (0)
No revisions yet.