Archive for the ‘Security’ Category

For Meta data tables users can see records for each object they have permission for.

So if I have 10 tables and I deny access to one of them for you, you will only see nine items if you run Select * from sys.Objects

http://msdn.microsoft.com/en-us/library/ms187113.aspx

You should be able to deny access through a different route:

DENY VIEW DEFINITION TO UserName;
This will deny view definition to all objects
Advertisements

USE Master;

GRANT  EXECUTE ON xp_readerrorlog TO [DomainName\AD USER or Group];

They should now be able to run this:

exec xp_readerrorlog;

If you don’t want to give SysAdmin to developers on a test or live server you can apply these permissions:

Use Master;

GRANT VIEW SERVER STATE TO [Domain\ADGroup or User];

GRANT SHOWPLAN TO [Domain\ADGroup or User];

GRANT ALTER TRACE TO [Domain\ADGroup or User];

as per this article:

http://msdn.microsoft.com/en-us/library/ms187611.aspx

Nearly all of this post comes from the blog listeed in the reference below. I have duplicated most of it here as I found it hard to find and I know I am going to come accross this error again.

As well as the notes below the user will also need to be added to the DCOM users group on the server.

When a user tries to connect to SSIS remotely they get an access is denied error. They can connect locally with no problems. Administrators on the remote machine can connect remotely and locally.

For Windows 2003 Server or Windows XP

1. If the user running under non-admin account it needs to be added to Distributed COM Users group
2. Go to Start – Run and type %windir%\system32\Com\comexp.msc to launch Component Services
3. Expend Component Services\Computers\My Computer\DCOM Config
4. Right click on MsDtsServer node and choose properties
5. In MsDtsServer Properties dialog go to Security page
6. Configure your settings as described below step 7
7. Restart SSIS Service

In the Security page we are interested in “Launch and Activation Permissions” section. Click Edit button to see “Launch Permissions” dialog.

“Launch Permissions” dialog allows you to configure SSIS server access per user/group. In the bottom of the dialog you can select:

• Local / Remote Launch permissions if you allow a user/group to start service locally or remotely
• Local / Remote Activation permissions if you allow to a user/group to connect to SSIS server locally or remotely.

Remote Access:
By default low privileged users can only connect to SSIS Server on the local machine when the service already started. It is shown by the fact that only Local Activation checked for Machine\Users group. To grant the user permission connect to the running server remotely you need to check remote activation.

Reference:
http://deepakrangarajan.blogspot.com/2008/03/connecting-to-integration-service-on.html