May 7, 2008 at 2:54 pm
Hey guys,
This might be a quick question for someone, but i have search for an answer to this with no help...
I need to give a couple NT groups read only access to many databases on a server. Heres the twist... There is no static defined set of databases. They are dynamically created depending on what programs are running. So i can just go give db_datareader to abc database because the database names are dynamic as well.
I was hoping for a way to modify a Server Role and have something server specific, so when any database is created on that server, it doesn't matter because the NT group is already part of the Role...
Can this be done???
Thanks
May 7, 2008 at 3:02 pm
No, server roles do not impact database permissions.
Are you creating databases on the fly? Why not add a standard NT group to the db_datareader when you create the db?
May 7, 2008 at 3:09 pm
how about add the NT roles to db_datareader in model database then this set up will be in every db created in the instance....
---------------------------------------------------------------------
May 8, 2008 at 7:40 am
Hey Steve,
Databases are created on the fly... Its not as simple as a DBA creating the database and adding the group to db_datareader.
May 8, 2008 at 7:48 am
george sibbald (5/7/2008)
how about add the NT roles to db_datareader in model database then this set up will be in every db created in the instance....
so have you tried this?
---------------------------------------------------------------------
May 8, 2008 at 8:00 am
Hey George,
Thanks!! That did it.
May 8, 2008 at 8:04 am
That worked for a database that is newly created. Is there anything that I could do for databases that are restored from a backup, to super seed the backup permissions?
May 8, 2008 at 8:13 am
not directly because the restore will overwrite whatever is already there if database already exists and a restore does not use model database.
Not sure how restore is done (via EM, manually in QA, part of app?) but you would need to add the create user code into the process to restore the database. So perhaps supply a stored proc for everyone to use that restores database then adds user to dataabse. Or update the app to do that.
---------------------------------------------------------------------
May 8, 2008 at 8:36 am
I think what i am going to have to do is create a job that runs every 20 min or so iterating through the db's and checks to see if the permission is there... If not add the permission... Kinda backyard, but i can't change the legacy code process as it will open a huge can of worms...
If anyone else has any ideas, I'm welcome to hear them...
Thanks for your help!
May 8, 2008 at 9:08 am
The job was going to be my next idea. It should be quick and not much load, so I might run it every 5 min or so.
Viewing 10 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply