April 14, 2015 at 9:13 am
I am looking to understand and adopt a method by which we can have a schema created on the system database's that would allow non system DBA's to either use or manage objects on a "SUPPORT" schema.
We have 3 levels of access:
1. System DBA (speaks for itself - using LAN group "SYSDBA")
2. Application DBA (manages user database's - using LAN group "APPDBA")
3. Application Users (read and execute permissions only - using LAN group "APPUSR")
In a number of our BAU reports, tracking, ETL's etc we have the need to create and use objects that look at information within the system database's. But wanted to ensure that the objects and access we have are isolated to the appropriate levels above. To this end I'm looking at having a "SUPPORT" schema created on the MASTER, MSDB and ReportServer database's (although I know the RS is not a true system database).
On this "SUPPORT" schema I would like the following;
1. Application DBA's to be able to create and modify any object when using SUPPORT schema, as well as have select and execute permissions on any objects on "SUPPORT" schema (I believe that being "Schema Owner" would achieve this?)
2. Application support to be able to select, execute and view definitions on objects using the SUPPORT schema.
Can I get a step by step breakdown of how to accomplish this the best way.
April 27, 2015 at 9:25 am
Instead of creating objects in system databases, why don't you create a new database specifically for the objects you create? would save messing with the system databases.
you will still be able to reference objects in the system databases.
April 27, 2015 at 9:32 am
I'm with Cunningham for a few reasons. I don't like adding things to system databases, especially when they could cause issues with MS upgrades/patches. I wouldn't expect new schemas, but you never know.
Second, I want to be able to easily recover this db if I need to and this info, as well as xfer this to other systems if the need arose. Having a separate db makes sense.
You can still use roles and apply permissions as needed. I'd create the roles and apply permissions as needed inside of another db. You can still create separate schemas there that the roles have rights to work within.
April 27, 2015 at 9:42 am
On each of our servers, we have created a 'DBAUtilities" database.
This contains everything for maintenance, monitoring, capturing metrics and so forth.
This is a far better place to keep these things.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply