November 6, 2008 at 4:02 am
I have a database called "live" and I want to create a database called "liveview". I want to create only views in "liveview" which link to tables in "live". The users will connect to the database as user viewer. The "live" tables are owned by user scheme. So in database "liveview" I have granted create view permission to viewer and executed the command below.
create view [scheme].[stockm] as select * from live.scheme.stockm with (nolock)
This is to allow user viewer to access the live table scheme.stockm by connecting to the database "liveview" without the ability to update or create locks etc. What i don't want to do is allow user viewer the ability to connect directly to database "live"
When I try to execute the command above as user viewer connected to liveview I get the message.
Server: Msg 2760, Level 16, State 1, Procedure stockm, Line 1
Specified owner name 'scheme' either does not exist or you do not have permission to use it.
November 6, 2008 at 4:11 am
Check the schema on the database you have created and Logins that are used to access Live database
November 6, 2008 at 5:15 am
How do I give viewer access to schema scheme. I tried GRANT SELECT ON SCHEMA::scheme to viewer but go the message
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'ON'.
November 6, 2008 at 6:01 am
I am going to question your design a bit here - don't take it personally.
The first thing that I see as an issue is object ownership and permissions. If a user does not have access to the database with the data in it, the object owner of the view and the table need to be the same and you have to enable cross-database owership chaining. This presents a security risk because a user that does have the ability to create a view can actually gain access to objects in other databases that they have no permissions in. So, this may become a security mess pretty quickly and you really need to consider the implications of what you are doing very carefully.
The second thing I want to question is the use of the NOLOCK query hint. It is nearly always misused so I always question it when I see it. Keep in mind that using this query hint really makes the data you return an estimate. This query hint will not only give you dirty pages that may never be committed, but it will also (possibly) return the same record more than once or miss records. As your query scans indexes and heaps to return data, it ignores data moving around so if you have activity that is moving data within your indexes it can duplicate or miss something. So, if you have a database with heavy transactions running and you want to get accurate results without blocking and waiting, SNAPSHOT isolation is a much better solution.
November 6, 2008 at 6:17 am
All I'm trying to do is give access to live data in read-only mode on a small selection of tables. I know that if I don't use NOLOCK then the back end ERP package will crash. So the question is .... How do you allow a user to select data read only from another database and not allow that user the ability to connect to that database?
November 6, 2008 at 6:27 am
I would be very concerned about an ERP solution that crashes when it encounters a share lock on a table. I don't think I could program something to do that intentionally.
Your question is a bit open-ended. Why can't the users have "access" to the data? Are you just trying to ensure they cannot make updates? How up-to-date does the data need to be? How does the ERP solution authenticate? Giving the users read-only permissions on the database would ensure they cannot make changes.
In cases of users wanting to query a database where I want to ensure no conflict with the production OLTP system, I tend to look to a replication solution so I can move the entire workload to another server. Have you considered log-shipping to a secondary server? This could provide you with a read-only copy of the database on a second server with a small amount of latency (15 minutes). If that is too much latency, transactional replication may be well-suited for your situation - then you would just need to give the users read-only access to the secondary server. This would also allow you to be selective in the data being replicated to their reporting server and even do some pre-calculating for commonly run queries.
November 6, 2008 at 8:42 am
Lets just say I have good reasons for wanting to do it my way is there a way of preventing a user connecting to a database but also still being able to select data from views defined externally to that database?
November 6, 2008 at 8:52 am
The only things I can think of are using cross-database ownership chaining or defining a linked server (which can be back to the original server) and hard-coding another login into the linked server. The linked server approach would incurr a great deal of unnecessary overhead.
November 6, 2008 at 10:07 am
I agree with Michael, up to a point, about database ownership chaining. The user in liveview must be a user in live, but it doesn't need any permissions in live except CONNECT. That means the user wouldn't be able to query any tables or see any objects in live except via the views in liveview. Kevin didn't say the liveview user would create views, only that it would select from the views so there wouldn't be a danger of the user creating other views.
Greg
November 6, 2008 at 10:54 am
The problem I have is that the viewer can connect to the live database and select from any tables. I have had to execute deny select on payroltab to viewer in order to stop them reading specific tables. They can therefor also connect to the live database and select from tables without the "with (nolock)" hint.
It's a shame you can't stop a login from connecting to a database but allow it to access tables. I have defined the viewer login as a datareader and denydatawriter but I'm not sure this will stop the login from creating locks when they select from the table. I will also have to deny access to all the tables I don't want them to see.
Thanks for all your help any way guys.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply