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.

 All Forums
 SQL Server 2008 Forums
 SQL Server Administration (2008)
 User Access administration question

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.
Go to Top of Page

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.
Go to Top of Page

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 Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page
   

- Advertisement -