SQLTeam.com | Weblogs | Forums

Schema and Permission

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.

Thanks

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

1 Like

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.

sorry meant to say roles

1 Like

OK, let me look into it and get back with you.

I still am unsuccessful with this.

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.

I think this is what you guys are referring to:

OK, lets see if I can do this.

1 Like

I think you may need to read up on ownership chaining. eg

http://www.sommarskog.se/grantperm.html#crossdbcert

1 Like

Ifor, I believe you are correct and I believe that is the missing part "Ownership Chaining". Thanks for paying attention. I will read it now.