January 30, 2009 at 8:44 am
I am trying to understand implications of read only Database.
My assumption is w/ a read only data base, one should be able to do select queries easily. But I think i am unable to do that as well. When I parse the query, I get following message:
Msg 916, Level 14, State 1, Line 1
The server principal "user" is not able to access the database "DataBase" under the current security context.
thank you
January 30, 2009 at 8:52 am
Hmmmm......just tried after seeing this question. I can execute select statements but when I put the same select statement in a proc and try to execute it, it denies result with permission issue.
I think it might be since read_only property is more like data_reader and execute would be more appropriate with ddl_admin.
SQL DBA.
January 30, 2009 at 8:58 am
You have a permissions issue is all. You need to set up User to have datareader rights, and to have Execute rights on the select procs.
I've gotten good use out of read-only databases, where I store common data that nobody needs to update. Like a Numbers table, a base calendar table, and so on. Set it to read-only, and SQL won't bother with locks on it, which can be very nice for performance in a high-concurrency situation.
When I have to reload tables in it, like a table of Zip Codes, I set it to read-write, ETL the data in, and then re-set it to read-only, during off-hours. That's a monthly process, and is highly automated.
But it does require setting up permissions so that anyone with any access at all to the server can read from that database and execute the procs and functions in it. (Which are all just select statements with no updates/inserts/deletes.)
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 30, 2009 at 9:09 am
Ok... so, I assume once I have datareader rights .. I shd be able to do select from..
Correct?
February 2, 2009 at 6:38 am
You should be able to just give the user execute privileges to the procedure(s).
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply