I have three schemas Production, HRecourse and Sales,tables in every week create and drop , I have six Users P_User1, P_User2, HR_User1, HR_User2 and S_User1 and S_User2. I have to grant then how i can Select permission to each table in their respective Schemas. how i tackle this scenario with SQL Scripts.
Sql-server – Select permission to each table
Securitysql server
Related Question
- Sql-server – “The SELECT permission was denied” only for some of the tables
- Sql-server – SQL Server user cannot select from a table it just created
- SQL Server – Deny Users Permission to Create Tables in Master
- SQL Server – Grant SELECT Permission on View Across Schemas
- Sql-server – Create table permission for a user in specific schema
- SQL Server 2016 Security – User Security Inheritance Explained
- Azure SQL – Select Permission Denied on Object
- SQL Server – Is It Safe to Grant Permissions to All Users?
Best Answer
nstead of dropping and re-creating, why not just keep the same tables and truncate them and re-populate them? Then you don't have to mess with permissions each week.
However, if you want to keep doing this the hard way, this script might help.
SQL Server 2008+:
If you're stuck on 2005, you'll need to make a couple of changes (and start thinking about your next move):