?
Solved

how to secure SSISConfigurations table with ssis configuration details.    URGENT HELP REQUIRED!!!!!!!!!!!!!!

Posted on 2011-03-10
2
Medium Priority
?
346 Views
Last Modified: 2012-06-27


Hi,

I want to assign  permissions to select, update and write (means all) SSISConfigurations table which contains ssis connection strings and connection passwords to only a user abc and sqlAgent login which would use it while running ssis package which is deployed to sql server.

Can you please tell me the process to secure this table so no one except the authorized user can open it or view it.

URGENT HELP REQUIRED!!!!!!!!!!!!!!!!

Thanks.
0
Comment
Question by:ezkhan
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 21

Accepted Solution

by:
Jason Yousef, MS earned 1000 total points
ID: 35101895
Hello,
You can restrict users using ROLES, or you can on views and schemas.
so you can create views of the table and restrict access to them.

or create new schema for each user and create his table under it.

take a look at this website.
http://www.techrepublic.com/article/understanding-roles-in-sql-server-security/1061781

http://vyaskn.tripod.com/sql_server_security_best_practices.htm

http://beginner-sql-tutorial.com/sql-grant-revoke-privileges-roles.htm

http://www.sqlservercentral.com/Forums/Topic490100-359-1.aspx

Hope that helps
Jason

0
 
LVL 40

Assisted Solution

by:lcohan
lcohan earned 1000 total points
ID: 35109614
You need to EXPLICITELY revoke ALL rights against that databse table for ALL users/roles in the sql database then grant select, update on SSISConfigurations to abc.
Exception to the rule would be any user in the sysadmin server role  on the SQL server.

Here's one script you can run against the DB - just put the object name at the end instead of 'clients'
to help you gett all permissions on that object then script to REVOKE all you want to restrict:

select
 sysusers.name as username, sysusers.gid,
 sysobjects.name as objectname, sysobjects.id,
 CASE WHEN sysprotects_1.action is null THEN CASE WHEN sys.sysobjects.xtype = 'P' THEN 'N/A' ELSE 'No' END ELSE 'Yes' END as 'SELECT',
 CASE WHEN sysprotects_2.action is null THEN CASE WHEN sys.sysobjects.xtype = 'P' THEN 'N/A' ELSE 'No' END ELSE 'Yes' END as 'INSERT',
 CASE WHEN sysprotects_3.action is null THEN CASE WHEN sys.sysobjects.xtype = 'P' THEN 'N/A' ELSE 'No' END ELSE 'Yes' END as 'UPDATE',
 CASE WHEN sysprotects_4.action is null THEN CASE WHEN sys.sysobjects.xtype = 'P' THEN 'N/A' ELSE 'No' END ELSE 'Yes' END as 'DELETE',
 CASE WHEN sysprotects_5.action is null THEN CASE WHEN sys.sysobjects.xtype = 'U' THEN 'N/A' ELSE 'No' END ELSE 'Yes' END as 'EXECUTE'
from
 sys.sysusers
 full join sys.sysobjects on ( sysobjects.xtype in ( 'P', 'U' ) and sysobjects.Name NOT LIKE 'dt%' )
 left join sys.sysprotects as sysprotects_1
  on sysprotects_1.uid = sysusers.uid and sysprotects_1.id = sysobjects.id and sysprotects_1.action = 193 and sysprotects_1.protecttype in ( 204, 205 )
 left join sys.sysprotects as sysprotects_2
  on sysprotects_2.uid = sysusers.uid and sysprotects_2.id = sysobjects.id and sysprotects_2.action = 195 and sysprotects_2.protecttype in ( 204, 205 )
 left join sys.sysprotects as sysprotects_3
  on sysprotects_3.uid = sysusers.uid and sysprotects_3.id = sysobjects.id and sysprotects_3.action = 197 and sysprotects_3.protecttype in ( 204, 205 )
 left join sys.sysprotects as sysprotects_4
  on sysprotects_4.uid = sysusers.uid and sysprotects_4.id = sysobjects.id and sysprotects_4.action = 196 and sysprotects_4.protecttype in ( 204, 205 )
 left join sys.sysprotects as sysprotects_5
  on sysprotects_5.uid = sysusers.uid and sysprotects_5.id = sysobjects.id and sysprotects_5.action = 224 and sysprotects_5.protecttype in ( 204, 205 )
where -- sys.sysusers.name = 'beny'            -- by USER
            sysobjects.name = 'clients'      -- by OBJECT
order by
 sysusers.name, sysobjects.name

0

Featured Post

Percona Live Europe 2017 | Sep 25 - 27, 2017

The Percona Live Open Source Database Conference Europe 2017 is the premier event for the diverse and active European open source database community, as well as businesses that develop and use open source database software.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Backups and Disaster RecoveryIn this post, we’ll look at strategies for backups and disaster recovery.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question