Tech Novice
asked on
Create user which has r/w rights on database,except few tables which has read rights
Hi,
I have few tables in the database which needs to be marked as read only , rest all should be read write for the a login user ,how can i create such login user which has login access of the few tables as r/w, some tables as Read only. This user can should also posses sys admin rights also.
I have few tables in the database which needs to be marked as read only , rest all should be read write for the a login user ,how can i create such login user which has login access of the few tables as r/w, some tables as Read only. This user can should also posses sys admin rights also.
>>>This user can should also posses sys admin rights also.
Generally, you can't prevent SYS ADMINs from updating data. However, there is one other way to prevent a SYS ADMIN from updating tables. You could create INSTEAD OF - INSERT, UPDATE and DELETE TRIGGERS on those "READ ONLY" tables which for all or certain user's updates would do nothing.
Let me know if you need help with that.
Best Regards,
GDG
Generally, you can't prevent SYS ADMINs from updating data. However, there is one other way to prevent a SYS ADMIN from updating tables. You could create INSTEAD OF - INSERT, UPDATE and DELETE TRIGGERS on those "READ ONLY" tables which for all or certain user's updates would do nothing.
Let me know if you need help with that.
Best Regards,
GDG
ASKER
Can you please provide details for creating file group (but you can create a separate Filegroup to hold all these tables) .
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Please be noted that once you mark the filegroup as Readonly, you can't modify the contents inside the table
If this is the case then i worry that the file group would be of any help,as admin can insert data in these tables at regular interval,is there aany other approach apart from this?
If this is the case then i worry that the file group would be of any help,as admin can insert data in these tables at regular interval,is there aany other approach apart from this?
Yes, there is.
Please see my earlier post. (Second post in this thread.)
That is the only way you will be able to limit certain users (even if they are SYSADMIN users) while still allowing other users to modiify the table(s).
Just know that the admin users can modify and or disable the triggers, so you must trust the account to a certain level.
Before adding the triggers, I would find out if the user account or accounts MUST be SYSADMINs. If they don't, or if it is something you can grant and take away as needed, That would be my first choice.
Let me know if you need help with that.
Please see my earlier post. (Second post in this thread.)
"You could create INSTEAD OF - INSERT, UPDATE and DELETE TRIGGERS on those "READ ONLY" tables which for all or certain user's updates would do nothing. "
That is the only way you will be able to limit certain users (even if they are SYSADMIN users) while still allowing other users to modiify the table(s).
Just know that the admin users can modify and or disable the triggers, so you must trust the account to a certain level.
Before adding the triggers, I would find out if the user account or accounts MUST be SYSADMINs. If they don't, or if it is something you can grant and take away as needed, That would be my first choice.
Let me know if you need help with that.
>> If this is the case then i worry that the file group would be of any help,as admin can insert data in these tables at regular interval,is there aany other approach apart from this?
If admin would load only during specific intervals, then you can temporarily remove Read only option from File Group, insert your data and change it back to Read Only.
Otherwise you can go with Triggers as Godwin mentioned above, but any sysdmin or database owner can disable the Triggers and do whatever they want..
If admin would load only during specific intervals, then you can temporarily remove Read only option from File Group, insert your data and change it back to Read Only.
Otherwise you can go with Triggers as Godwin mentioned above, but any sysdmin or database owner can disable the Triggers and do whatever they want..
ASKER
Thanks
You can't mark a table as Read only, but you can create a separate Filegroup to hold all these tables, create the tables on this Filegroup and load all these tables and then Mark that Filegroup as Read only.
>> This user can should also posses sys admin rights also.
If an user has sys admin rights, then by default they will have Full rights on all objects unless they are placed in Read only File groups.
So, Read only Filegroups would be the only way to meet up your requirement.