Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
sreenu9f
Yak Posting Veteran
73 Posts |
 Posted - 2014-06-26 : 11:17:28
|
| Hi, I need help with regard to the user access. currently there is one DB with 100's of tables and each table has a PK which distinguishes each region ( 4 regions) and 4 views were created for each table and those region specific views were provided to users of each region but the big challenge is for the SQL report writers who want to create stored procs we had to give the full access. Is there any way to restrict the access when they develop SP's to use just the views for their region hence they don't have the ability to look at other regions data.please suggest.Thanks, |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2014-06-26 : 13:46:02
|
| Grant them access only to the views that correspond to their region. Ideally set it up so the users have only public access to the database and then grant them permissions for the specific views that they should be allowed to access. |
 |
|
|
sreenu9f
Yak Posting Veteran
73 Posts |
Posted - 2014-06-26 : 14:11:33
|
| Thanks James; That's already taken care of but the issue is they need to create and run stored procedures and letting them do this function i have to provide full access for the tables and DB. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2014-06-26 : 14:56:28
|
| It seems to me this is an environment issue. Creating stored procedures should be done in a test environment and then a DBA deploys them to PROD during a maintenance window. Then those report writers only have access to the stored procedures that contain the queries for the views they have access to. Alternatively, you could create a new database where they have dbo access. Due to cross-database permissions, they'll only be able to utilize the views they have access to.Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
|
|
|
|
User Access administration question