April 13, 2024 at 10:26 am
To manage ssis project development’s & deployment from visual studio is SSISadmin role is good or sysadmin permissions are required?
How can we provide permissions if we can’t create the login for the same account that has visual studio license?
April 14, 2024 at 11:10 am
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
April 15, 2024 at 3:37 pm
My advice - check the docs first. ALWAYS ALWAYS ALWAYS check the official docs as things MAY change between versions. Link to 2022 - https://learn.microsoft.com/en-us/sql/integration-services/security/integration-services-roles-ssis-service?view=sql-server-ver16
As for the sysadmin, I would NOT give that out to anyone except the DBA. Nobody else SHOULD need sysadmin. Some 3rd party tools require it to create a database, but after the database is created, you can usually pull that permission off and the app continues to work fine.
Next, I NEVER deploy from visual studio. I've had issues where the deployment broke things OR the deployment pushed out ALL SSIS packages, not just the one I was changing. The second issue is worse than the first as if I break my own changes, that is 1 package down. If I broke changes done by others in my team who had released their stuff but my git branch was stale, their work just got blown out. Others have had it work without issue, so your experience may vary. But I always deploy SSIS from SSMS that matches the version of SQL I am pushing to so I make sure, 100%, that I am ONLY pushing out my changes and that I can verify that my changes worked without needing to double check if anything else got updated. SSIS 2008 R2 uses SSMS 2008 R2. SSIS 2012 uses SSMS 2012. And so on. The reason being, the wrong version means it will almost always fail to deploy.
As for permissions, you can configure how you connect to SSIS from within the visual studio project. You can do windows authentication or SQL authentication. Windows will use the currently logged in user (NOT the VS user); SQL authentication will use the hard-coded username/password (I do not recommend doing this unless it is the only option). Your VS user could be a public account (gmail.com for example) and your logged in user is using AD for authentication and it'll happily use AD and ignore the public account.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply