Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Access question


Access question

Author
Message
sunny.tjk
sunny.tjk
Old Hand
Old Hand (327 reputation)Old Hand (327 reputation)Old Hand (327 reputation)Old Hand (327 reputation)Old Hand (327 reputation)Old Hand (327 reputation)Old Hand (327 reputation)Old Hand (327 reputation)

Group: General Forum Members
Points: 327 Visits: 1344
The default access of all new employees of our company is db_datareader but how do we deny access to particular column(for ex. SSN) of tables by default?
sunny.tjk
sunny.tjk
Old Hand
Old Hand (327 reputation)Old Hand (327 reputation)Old Hand (327 reputation)Old Hand (327 reputation)Old Hand (327 reputation)Old Hand (327 reputation)Old Hand (327 reputation)Old Hand (327 reputation)

Group: General Forum Members
Points: 327 Visits: 1344
I can think of DENY SELECT but not sure how to implement it. I'd like to accomplish DENY SELECT to everyone except to the members of the approved group.
Orlando Colamatteo
Orlando Colamatteo
SSCrazy Eights
SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)

Group: General Forum Members
Points: 8237 Visits: 14368
sunny.tjk (10/31/2012)
I can think of DENY SELECT but not sure how to implement it. I'd like to accomplish DENY SELECT to everyone except to the members of the approved group.


Here is one option:

1. Create a new Database Role named db_CompanyNameDefaultReader where CompanyName is your particular company name.
2. Add db_CompanyNameDefaultReader to db_datareader using sys.sp_addrolemember.
3. Add all relevant members of db_datareader to the new role db_CompanyNameDefaultReader again using sys.sp_addrolemember.
4. Remove all people now in db_CompanyNameDefaultReader from db_datareader using sys.sp_droprolemember.
5. Deny select on the columns in question from db_CompanyNameDefaultReader using the following DDL:

DENY SELECT ON OBJECT::[dbo].[TheTableName]([TheColumnName]) TO [db_CompanyNameDefaultReader];



__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
sunny.tjk
sunny.tjk
Old Hand
Old Hand (327 reputation)Old Hand (327 reputation)Old Hand (327 reputation)Old Hand (327 reputation)Old Hand (327 reputation)Old Hand (327 reputation)Old Hand (327 reputation)Old Hand (327 reputation)

Group: General Forum Members
Points: 327 Visits: 1344
opc.three (10/31/2012)
sunny.tjk (10/31/2012)
I can think of DENY SELECT but not sure how to implement it. I'd like to accomplish DENY SELECT to everyone except to the members of the approved group.


Here is one option:

1. Create a new Database Role named db_CompanyNameDefaultReader where CompanyName is your particular company name.
2. Add db_CompanyNameDefaultReader to db_datareader using sys.sp_addrolemember.
3. Add all relevant members of db_datareader to the new role db_CompanyNameDefaultReader again using sys.sp_addrolemember.
4. Remove all people now in db_CompanyNameDefaultReader from db_datareader using sys.sp_droprolemember.
5. Deny select on the columns in question from db_CompanyNameDefaultReader using the following DDL:

DENY SELECT ON OBJECT::[dbo].[TheTableName]([TheColumnName]) TO [db_CompanyNameDefaultReader];




Great idea opc.Three!!

But will it still work if a new user is added to db_datareader role?
Orlando Colamatteo
Orlando Colamatteo
SSCrazy Eights
SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)

Group: General Forum Members
Points: 8237 Visits: 14368
Thanks. In the approach I detailed, if a new user is added to the db_datareader Fixed Database Role they will have permission to select the restricted column from your table unless they are also a member of the db_CompanyNameDataReader User-defined Database Role in which case the DENY permission for db_CompanyNameDataReader User-defined Database Role will take precedence over the GRANT afforded to the db_datareader Fixed Database Role. In short, it is a best practice to try and make Database Users directly a member of one Role if possible to keep things less confusing to manage.

As another general rule I would also suggest you avoid adding a Database User directly to any of the Fixed Database Roles like db_datareader. I prefer to only allow users to be part of User-defined Database Roles. In the rare case where a Database User or set of Users is justified in having db_datareader permissions I will add the User-defined Database Role they belong to as a member of the db_datareader Fixed Database Role.

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
sunny.tjk
sunny.tjk
Old Hand
Old Hand (327 reputation)Old Hand (327 reputation)Old Hand (327 reputation)Old Hand (327 reputation)Old Hand (327 reputation)Old Hand (327 reputation)Old Hand (327 reputation)Old Hand (327 reputation)

Group: General Forum Members
Points: 327 Visits: 1344
opc.three (10/31/2012)
sunny.tjk (10/31/2012)
I can think of DENY SELECT but not sure how to implement it. I'd like to accomplish DENY SELECT to everyone except to the members of the approved group.


Here is one option:

1. Create a new Database Role named db_CompanyNameDefaultReader where CompanyName is your particular company name.
2. Add db_CompanyNameDefaultReader to db_datareader using sys.sp_addrolemember.
3. Add all relevant members of db_datareader to the new role db_CompanyNameDefaultReader again using sys.sp_addrolemember.
4. Remove all people now in db_CompanyNameDefaultReader from db_datareader using sys.sp_droprolemember.
5. Deny select on the columns in question from db_CompanyNameDefaultReader using the following DDL:

DENY SELECT ON OBJECT::[dbo].[TheTableName]([TheColumnName]) TO [db_CompanyNameDefaultReader];




I tried to implement this on my local but I received an error saying "Cannot use the special principal 'db_datareader'." when I executed the following code:

EXEC sp_addrolemember 'SSN_GRANTED', 'db_datareader'
Orlando Colamatteo
Orlando Colamatteo
SSCrazy Eights
SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)

Group: General Forum Members
Points: 8237 Visits: 14368
sunny.tjk (11/1/2012)
opc.three (10/31/2012)
sunny.tjk (10/31/2012)
I can think of DENY SELECT but not sure how to implement it. I'd like to accomplish DENY SELECT to everyone except to the members of the approved group.


Here is one option:

1. Create a new Database Role named db_CompanyNameDefaultReader where CompanyName is your particular company name.
2. Add db_CompanyNameDefaultReader to db_datareader using sys.sp_addrolemember.
3. Add all relevant members of db_datareader to the new role db_CompanyNameDefaultReader again using sys.sp_addrolemember.
4. Remove all people now in db_CompanyNameDefaultReader from db_datareader using sys.sp_droprolemember.
5. Deny select on the columns in question from db_CompanyNameDefaultReader using the following DDL:

DENY SELECT ON OBJECT::[dbo].[TheTableName]([TheColumnName]) TO [db_CompanyNameDefaultReader];




I tried to implement this on my local but I received an error saying "Cannot use the special principal 'db_datareader'." when I executed the following code:

EXEC sp_addrolemember 'SSN_GRANTED', 'db_datareader'

You do not want to add db_datareader to SSN_GRANTED, you want the opposite.

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
jerry-621596
jerry-621596
Old Hand
Old Hand (373 reputation)Old Hand (373 reputation)Old Hand (373 reputation)Old Hand (373 reputation)Old Hand (373 reputation)Old Hand (373 reputation)Old Hand (373 reputation)Old Hand (373 reputation)

Group: General Forum Members
Points: 373 Visits: 644
How about creating a view with all columns in the table except those you want to hide. Then give read permission ONLY to those views.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search