Granting select to all databases current and future?

Question

Granting select to all databases current and future?

lbrady

SSC Eights!

Points: 883
More actions
September 20, 2013 at 1:40 pm

#280011

Hello, I am having a major brain malfunction!
I have a user who needs select permissions to all databases on a server. No problem. The caveat is they they are requesting access to all databases current and future.
Can I set up read access at a server level? If so, how?
SS2008SP2.
TIA
-Laura

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

David Webb-CDS SSCoach Points: 17398 More actions · Answer 1

Haven't tried this, but you could set them up with the db_datareader role in the Model database. When Model is copied as the basis for any new database, the role definition would come along.

And then again, I might be wrong ...
David Webb

Erland Sommarskog SSC-Insane Points: 24038 More actions · Answer 2

You could write a server trigger for CREATE DATABASE that adds this user to the database. I like this better than adding a user to model, because a server trigger is easier to find and notice. But I can't say that I like any of them.

But a database may also appear on the server because it was restored from a different server. Model is not going to help. Don't recall on the top of my head if there is server trigger for RESTORE DATABASE.

[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 3

lbrady (9/20/2013)
I have a user who needs select permissions to all databases on a server.

From a data security aspect, I'd really want to know why that's necessary. I'd also get the head of IT to sign off on that request in writing.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

lbrady SSC Eights! Points: 883 More actions · Answer 4

David Webb-CDS (9/20/2013)
Haven't tried this, but you could set them up with the db_datareader role in the Model database. When Model is copied as the basis for any new database, the role definition would come along.

Thank you! This is exactly what I need. Not sure why I couldn't think of it myself.

lbrady SSC Eights! Points: 883 More actions · Answer 5

Jeff Moden (9/20/2013)
lbrady (9/20/2013)
I have a user who needs select permissions to all databases on a server.
From a data security aspect, I'd really want to know why that's necessary. I'd also get the head of IT to sign off on that request in writing.

Yes, Jeff. A valid concern. However, this department owns the server. I only support them in a very limited capacity (backups and permissions - the use of the term 'security' is purposefully omitted.) From a company standpoint, it is all development and therefore no PII.