Less Than Dot Blog & ERROR "not able to access the database "model" "

Question

I have a couple quick questions about all the SQL on the Less Than Dot Blog (I'll bet anything Naomi chimes in here with a response within a few minutes). 

http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/how-to-get-information-about-all-databas#3

I started a new job and I just got setup with SQL Server 2008 R2.  I tried to run all SQL under all topics of the 'Table of Contents:'  Almost every query failed with this message:

Msg 916, Level 14, State 1, Line 5
The server principal "ryan_shuell" is not able to access the database "model" under the current security context.

Basically, I'm trying to get a better appreciation for the structure of the DB here.  Is there some way to query all these DBs, and skip over that error message?  Or, do I need to call the help desk (those guys definitely are not experts with SQL Server)?

All of this works on my home machine, but that's just loaded with AdventureWorks, and Northwind, and a couple of very simple DBs.  I know the SQL works, it just won't work on my work DB.

Thanks everyone!!

---

Ryan Shuell

Answer 1

Hmm, strange error message, but it clearly indicates that the account you're running with has very little rights. Anyway, in the queries you're using can you try excluding model database (if you're creating query using sys.databases try excluding name = 'model' for now).

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 2

Related Connect bug report:

http://connect.microsoft.com/SQLServer/feedback/details/198005/not-able-to-access-the-database-model-under-the-current-security-context-microsoft-sql-server-error-916

Thread:

http://social.msdn.microsoft.com/forums/en-US/sqlsecurity/thread/b63000dc-08d6-4a0f-b6f1-9e6a0d10c9ed/

>The server principal "ryan_shuell" is not able to access

WORKAROUND: drop the login and add it again following proper procedure.

---

Kalman Toth Database & OLAP Architect sqlusa.com
Paperback / Kindle: Pass SQL Exam 70-461 & Job Interview: Programming SQL Server 2012

Answer 3

My thanks go out to both of you!  I tried both suggestions; unfortunately, this did not resolve the error.  I'll have to figure something else out.

Just a couple more questions for Naomi.  How did you come up with all that SQL at your blog?  Do you type it out letter by letter, or does SQL Server auto-generate most of the script for you, and you tweak it a bit to make it dynamic and scalable?  In Excel you can easily record Macros, and then tweak the resulting VBA to do all kinds of things.  Of course, you can never record a loop, and the ranges will not be dynamic, but usually you can record a lot to get started, and then just modify the code a little to customize it to do what you need to do.  Is this similar to that?  I'd definitely appreciate any guidance that you could offer.  Finally, what does 'less than dot' mean???

---

Ryan Shuell

Answer 4

I explain in that very blog in the beginning. One night I was thinking about one particular problem and realized that I can simply generate dynamic SQL using sys.databases. Then I just wrote the script for that particular problem. When new question popped up in MSDN where I can use that technique, I used it and put in that blog. Later I put a table of content. BTW, doing so I limited my abilities to edit that blog - I am now unable to edit (there are some internal difficulties in LTD). But I think I put as much different scripts into it as possible and using that idea it's easy to come up with something similar if needed.

Interesting question about 'Less than dot' - I never actually asked for the history of the name and I joined that site few years after it started. You may want to become a member of the site (if you're not already) and post this question in one of the forums (post link here, please). I am sure Ted or Denis or anyone else will be happy to tell.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 5

Got it!  You should be a SQL Server MVP.  I don't know why you're not, actually.  Maybe it will happen soon!!!

---

Ryan Shuell