sql user security based on a column in tables

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)

sql user security based on a column in tables

Author

Topic

sreenu9f
Yak Posting Veteran

73 Posts

Posted - 2013-05-15 : 10:30:15

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

5072 Posts

Posted - 2013-05-15 : 19:07:45

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.

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2013-05-15 : 19:13:47

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.[CODE]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'[/CODE]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)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources