Table Level Permissions

Question

Table Level Permissions

Karl Kieninger

SSC Eights!

Points: 853
More actions
July 3, 2012 at 10:06 pm

#271972

I' have a legacy db with 200+ tables that I need to be read-only except one logging table. I've considered ways to manipulate the file groups to achieve this, but for various reasons would prefer to manage it with permissions. I'm not very familiar with how users roles interact with permissions table level. If it matter the legacy app that will continue to access logs in via an sa account which is the db_owner. If needed I can switch its login to a new account.
Suggestions would be appreciated.
I acknowledge that this may exist in another forum topic, but I did not find after a reasonable amount of searching.
Thank you,
KArl

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Smarties SSC Eights! Points: 888 More actions · Answer 1

Moving the log table to a new filegroup and set the others to read only is probably the easiest option. The other way is to create a view for each table and assign permission to the views, considering you have over 200 tables, it will be a lot of work.

Karl Kieninger SSC Eights! Points: 853 More actions · Answer 2

Smarties (7/4/2012)
Moving the log table to a new filegroup and set the others to read only is probably the easiest option. The other way is to create a view for each table and assign permission to the views, considering you have over 200 tables, it will be a lot of work.

Moving the Log table and setting the original file group to read only is a great idea, but the original file group is the primary filegroup, you cannot make the primary filegroup read only, and I have found no method to designate a different filegroup as primary.

Thanks,

Karl

Smarties SSC Eights! Points: 888 More actions · Answer 3

I am pretty sure you can create a new filegroup and set it as the default filegroup. This will then allow you to set the original filegroup to read only.

After creating a new filegroup, you can set this as the default by code similar to this:

ALTER DATABASE [youdb] MODIFY FILEGROUP [yourfilegroup] DEFAULT

And then you can move the logging table to the new filegroup by recreacting the clustered index speciftying the new filegroup name in the CREATE INDEX statement.

Karl Kieninger SSC Eights! Points: 853 More actions · Answer 4

Smarties (7/4/2012)
I am pretty sure you can create a new filegroup and set it as the default filegroup. This will then allow you to set the original filegroup to read only.

DEFAULT <> PRIMARY. I can change the DEFAULT via TSQL or SSMS, but I have not found a way to change the PRIMARY.

If anyone knows of one that would be great.

Thanks,

Karl

Smarties SSC Eights! Points: 888 More actions · Answer 5

Hi Karl,

My apologies, you are correct, PRIMARY <> DEFAULT.

Can you try this?

use [MyDB]

GO

DENY INSERT ON [dbo].[MyTable] TO [MyTestLogin]

GO

DENY DELETE ON [dbo].[ MyTable] TO [MyTestLogin]

GO

DENY UPDATE ON [dbo].[ MyTable] TO [MyTestLogin]

GO

The code above will prevent your login from insert, update and deleting data from a table. An easier way will be to add a new database role, apply the above permissions to the role and then add your login to the database role.

Hope that helps.

Karl Kieninger SSC Eights! Points: 853 More actions · Answer 6

That works.

I created a new user and used sp_msforeachtable to deny on all table and then granted update to the couple tables that need it.

Thanks.

I did try making the default file group files read only via the the OS, but I'm not sure if that should have worked. My results were mixed.

Thanks for the help!

Karl