SQL Server Performance Forum – Threads Archive
Role Based Security
HiI have a role on my sql database where I added all the users to it.When I create a new SP or table I am going to the properties of the role and checking the particular table or SP.
Do I need to do this each time or is there a way tht automatically give permissions to the role when it is created. Thanks!
"He laughs best who laughs last"
I would think that you always have to explicity have to GRANT permissions to objects for all security principals unless they are members of the pre-defined roles like db_owner or sysadmin. Nathan H.Omukwenyi
Hey Nathan
Good to see you after a while, hope everything is fine at your end. Reddy,
You can set those permissions for that object using GRANT statement, without intervention of EM. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS†with no rights for the sake of knowledge sharing.
Yeah I understand tht we can use GRANT from QA but can tht be executed itself once every new object is created? Thanks!
"He laughs best who laughs last"
Hi Reddy,
I think it should not like that obviously you can do some tweak (check existance of objects from sysobjects and then issuing GRANT commands )with it do the same you wants to achieve but it should be manualy given because its not the same privilages each time.
Regards Hemantgiri S. Goswami
[email protected]
"Humans don’t have Caliber to PASS TIME , Time it self Pass or Fail Humans" – by Hemantgiri Goswami
Security should never be applied with a default of permit.
If you found a way to automate this, you’re effectively saying the users will have access to every object created which defeats the point. I’d suggest creating objects via scripts as best practise (because it allows for future reference, storing with your change records, etc.) Simply pasting a template GRANT statement at the end would be a working solution.
For a good assessment use SP_HELPROTECT for complete information on privileges for all the users in the database. Then as suggested use the scripts wherever necessary. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS†with no rights for the sake of knowledge sharing.
]]>