A few months ago, I have created a schema to secure a handful of views. I granted a SQL Authentication account to this schema. The objective is so this SQL account only has access to these views and nothing else in the database. This setup has been successful for many months.
Now, I was asked to move that schema to another database which I did. Unfortunately, it is saying the SQL account does not have permission to the tables in the original database. Keep in mind, I cannot grant this account "DB_Reader" for the security reason that it will be able to read all tables.
Am I missing something on granting in the Schema or the SQL account? It seems like it is at the SQL account level.
If you dropped and recreated, then it would cause this issue. One of the things you could do is put the views into their own schema and then grant select on the schema to the user or group. Then if they are dropped and recreated, the user will still have select privileges on them
as @mike01 recommends group permissions is much better! otherwise you would be tweaking stuff all day long and in fact could expose yourself to worst issues
then in that group you could add users from AD etc. Once those users leave company, then no problem
Hi Mike, I believe that was how I setup the views to the schema. I am not sure what you mean by the drop.
Yosiasz, the account needs to be a SQL Authentication account for the reason that we supply it to a vendor. I was not aware that a SQL Authentication account can be in a group? I thought only AD Group exists.
SQL Authentication account does not have access to the tables but has access to the schema (with the views). That worked as long as the tables, views and schemas are in the same database. As soon as the views try to access tables from another database, this is where it
Msg 229, Level 14, State 5, Line 5
The SELECT permission was denied on the object
I can give db_dataReader role but then that defeat the purpose.
I then have tried to a add role in but a bit lost.