SQL Integration Services Access

Once upon a time, a long time ago, in SQL 2008, all users had access to connect to Integration Services in Management Studio. Naturally, SQL 2012 does it differently, and you have to explicitly grant access via Component Services

1. Run Dcomcnfg.exe. This lets you edit the registry.
   
2. In the Component Services dialog, expand
   Component Services --> Computers --> My Computer --> DCOM Config .
   
3. Right-click Microsoft SQL Server Integration Services 11.0, and then click Properties.
4. On the Security tab, click Edit in the "Launch and Activation Permissions" area.
   
   
   
5. Add users and assign appropriate permissions, and then click OK. If you don't like your users remoting onto the server, chose 'remote launch' and 'remote activation'
   
   
   
   
6. Repeat steps 4 - 5 for the "Access Permissions" area. Again, grant remote access if you don't allow users to log directly onto the server.
   
   
   
   
   
   
7. Restart the Integration Services Service in SQL Configuration. Restart Management Studio if you had it open, and were trying to connect from that.
   

T-SQL to give domain accounts sysadmin access

For those moments where you can only get access via osql.exe....

CREATE LOGIN [Domain\UserName] FROM WINDOWS;
GO


EXEC master..sp_addsrvrolemember [Domain\UserName],'sysadmin'

GO

Why yes. I did just lock myself out of a server accidentally this morning. Where Management Studio wasn't installed. Go figure.

Code Snippet

To execute sp_helprotect on all databases on a server, saving the results into a temporary table, and then displaying the results, including the database name. Makes use of sp_msforeachdb, an undocumented stored procedure which is very nifty for cycling through each database on a server.

It took me a while to work out how to do what I wanted to do...

This code is provided as-is, with no warranties. Test it before using it in earnest: and if you want to share the joy, please credit me. I wouldn't have managed it without the code here.

CREATE TABLE #temptable

(

[Owner] VARCHAR(50),

[Object] VARCHAR(100),

grantee VARCHAR(50),

grantor VARCHAR(50),

protecttype VARCHAR(50),

[Action] VARCHAR(50),

[Column] VARCHAR(50),

dbname VARCHAR(50)

)



DECLARE @STATEMENT VARCHAR(500)



SET @STATEMENT = 'Use [' + '?' + ']
INSERT INTO #temptable
([Owner],
[Object],
Grantee,
Grantor,
ProtectType,
[Action],
[Column]
)
EXEC (''sp_helprotect'')
UPDATE #temptable SET dbname = db_name()
WHERE dbname IS NULL'



EXEC MASTER.dbo.Sp_msforeachdb @command1=@STATEMENT



GO



SELECT *

FROM #temptable

ORDER BY dbname



GO



DROP TABLE #temptable
GO