Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Granting select to all databases current and future? Expand / Collapse
Author
Message
Posted Friday, September 20, 2013 1:40 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: 2 days ago @ 7:48 PM
Points: 37, Visits: 123
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
Post #1497025
Posted Friday, September 20, 2013 2:03 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 1:49 PM
Points: 897, Visits: 7,101
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
Post #1497031
Posted Friday, September 20, 2013 3:55 PM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 2:28 PM
Points: 786, Visits: 691
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.


Erland Sommarskog, SQL Server MVP, www.sommarskog.se
Post #1497066
Posted Friday, September 20, 2013 10:56 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 8:58 PM
Points: 36,794, Visits: 31,253
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1497107
Posted Saturday, September 21, 2013 6:10 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: 2 days ago @ 7:48 PM
Points: 37, Visits: 123
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.
Post #1497137
Posted Saturday, September 21, 2013 6:14 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: 2 days ago @ 7:48 PM
Points: 37, Visits: 123
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.

Post #1497138
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse