Tuesday, March 27, 2012

Grant Read only access to ALL databases

Let me start off by apologizing for asking such a "noob" question.
I have a SQL2000 server (on my AD domain) with a script that creates a
new database everyday, naming them by the MMDDYY format. For example,
the database for March 25, 2006 would be named "032506". And so on.
I would like to have a group of users that have "READ" only access to
these databases.
I've created a group on Active Director called "DBReader".
I've added the group to the SQL Server Enterprise Manager -> Security
-> Logins
This is were I'm stumped.
When I right click on the group I've added to the SQL Server Enterprise
Manager, I get three tabs, General, Server Roles, and Database Access.
I don't want to use the Server Roles tab because all the roles give too
much access (I only want to give READ access).
The Database Access tab allows me to assign READ access to "existing"
databases, what about new databases that gets created tomorrow and the
next day?
How can I assign "READ" access using group permissions to ALL existing
and NEW databases thats created in the future?
I'm hoping there's a simple solution and don't have to resort to
scheduling a script to add permissions to new databases everyday.
Thx for reading!
"me" <mnkykng@.gmail.com> wrote in message
news:1143504145.539215.309930@.g10g2000cwb.googlegr oups.com...
> Let me start off by apologizing for asking such a "noob" question.
> I have a SQL2000 server (on my AD domain) with a script that creates a
> new database everyday, naming them by the MMDDYY format. For example,
> the database for March 25, 2006 would be named "032506". And so on.
> I would like to have a group of users that have "READ" only access to
> these databases.
> I've created a group on Active Director called "DBReader".
> I've added the group to the SQL Server Enterprise Manager -> Security
> -> Logins
> This is were I'm stumped.
> When I right click on the group I've added to the SQL Server Enterprise
> Manager, I get three tabs, General, Server Roles, and Database Access.
> I don't want to use the Server Roles tab because all the roles give too
> much access (I only want to give READ access).
> The Database Access tab allows me to assign READ access to "existing"
> databases, what about new databases that gets created tomorrow and the
> next day?
> How can I assign "READ" access using group permissions to ALL existing
> and NEW databases thats created in the future?
> I'm hoping there's a simple solution and don't have to resort to
> scheduling a script to add permissions to new databases everyday.
>
New databases are created as copies of the Model database. Make the change
there.
David
|||Thx for replying!
So just to clarify.
If I use the SQL Server Enterprise Manager and add my AD group to
Database -> Model -> Users.
Double click the AD group I just added.
And check the box labeled "db_datareader" under the Database role
membership, any new databases that are created will get read access
permissions assigned automatically to my AD group?
sql

No comments:

Post a Comment