SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 SQL Server Administration (2008)
 sql user security based on a column in tables
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sreenu9f
Yak Posting Veteran

72 Posts

Posted - 05/15/2013 :  10:30:15  Show Profile  Reply with Quote

I have a database with several hundreds of tables and there is a column which is specific for a location. i would like to provide user access to a person/group based on the location but want to do it on all the tables.
please suggest if there is a script or any other way of accomplishing this task.

thanks,

russell
Pyro-ma-ni-yak

USA
5072 Posts

Posted - 05/15/2013 :  19:07:45  Show Profile  Visit russell's Homepage  Reply with Quote
You can use schemas and/or roles for this, but you can't make SQL Server look at the location column and set permission on a row by row basis.

If the location column has different values in different rows, you may want to create views WHERE location = 'xyz' and then assign to specific schema.
Go to Top of Page

Bustaz Kool
Flowing Fount of Yak Knowledge

USA
1673 Posts

Posted - 05/15/2013 :  19:13:47  Show Profile  Reply with Quote
Are you trying to use the data in the column to determine if the User can see the other columns? If so, you could make a set of Views that filtered the data.
CREATE MyTable (
  MyKey int not null,
  CustomerName varchar(50),
  Region varchar(5) -- North, South,East, West
  )
go
CREATE VIEW MyTableN
as
select 
  CustomerName
where
  Region = 'North'
go
CREATE VIEW MyTableS
as
select 
  CustomerName
where
  Region = 'South'
go
CREATE VIEW MyTableE
as
select 
  CustomerName
where
  Region = 'East'
go
CREATE VIEW MyTableW
as
select 
  CustomerName
where
  Region = 'West'
Now issue grants to users based on which region they are allowed to see.

=================================================
I am not one of those who in expressing opinions confine themselves to facts. (Mark Twain)
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000