SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Access question


Access question

Author
Message
sunny.tjk
sunny.tjk
SSC-Addicted
SSC-Addicted (481 reputation)SSC-Addicted (481 reputation)SSC-Addicted (481 reputation)SSC-Addicted (481 reputation)SSC-Addicted (481 reputation)SSC-Addicted (481 reputation)SSC-Addicted (481 reputation)SSC-Addicted (481 reputation)

Group: General Forum Members
Points: 481 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
SSC-Addicted
SSC-Addicted (481 reputation)SSC-Addicted (481 reputation)SSC-Addicted (481 reputation)SSC-Addicted (481 reputation)SSC-Addicted (481 reputation)SSC-Addicted (481 reputation)SSC-Addicted (481 reputation)SSC-Addicted (481 reputation)

Group: General Forum Members
Points: 481 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
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14889 Visits: 14396
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
SSC-Addicted
SSC-Addicted (481 reputation)SSC-Addicted (481 reputation)SSC-Addicted (481 reputation)SSC-Addicted (481 reputation)SSC-Addicted (481 reputation)SSC-Addicted (481 reputation)SSC-Addicted (481 reputation)SSC-Addicted (481 reputation)

Group: General Forum Members
Points: 481 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
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14889 Visits: 14396
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
SSC-Addicted
SSC-Addicted (481 reputation)SSC-Addicted (481 reputation)SSC-Addicted (481 reputation)SSC-Addicted (481 reputation)SSC-Addicted (481 reputation)SSC-Addicted (481 reputation)SSC-Addicted (481 reputation)SSC-Addicted (481 reputation)

Group: General Forum Members
Points: 481 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
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14889 Visits: 14396
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
Mr or Mrs. 500
Mr or Mrs. 500 (593 reputation)Mr or Mrs. 500 (593 reputation)Mr or Mrs. 500 (593 reputation)Mr or Mrs. 500 (593 reputation)Mr or Mrs. 500 (593 reputation)Mr or Mrs. 500 (593 reputation)Mr or Mrs. 500 (593 reputation)Mr or Mrs. 500 (593 reputation)

Group: General Forum Members
Points: 593 Visits: 645
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