Hey there.
I must write a stored procedure to update a table and want to make sure the ID used in both Acceptance and Production will have the necessary access. The ID will inherit it's access to the resource though a ROLE.
Wondering if there are queries out there that can traverse for example, the roles/id's that have access to it... Thx!
Hi Bill,
I'm a little confused after reading your question. Could you please elaberate your question a little more?
the ID used in bothAcceptance andProduction will have the necessary access.
What do you mean by "Acceptance" and "Production" ? Are they tables or something? Also, what do you mean by "the ID used", based on my understanding, we don't need an "id" when executing a stored procedure. Thanks for your understanding
|||Thanks for responding.
Bottom line is that I need a query that provides me the permission (roles/IDs) that have access to an object (table/views/stored procedures).
To be more specific to your questions...
You can log into SQL SERVER with an ID or with TRUSTED AUTHENTICATION. In my work environment, our support staff generally log into SQL SERVER using TRUSTED AUTHENTICATION... However, we use a number of ID's during the automated phase of interacting with SQL SERVER.
In this, we have many automated processes that run throughout the day and generally speaking, these are executables which modify our data based on a pre-defined set of business rules and they log into the database using a controlled set of IDs. So, at a regularly appointed time an exe kicks off and uses an embedded ID/PW (not really embedded) to open a connection with a database and then performs the necessary work. This is standard business practice.
Development, Acceptance (test), Production (release) are standard business environments that are virtually identical to each other that help creators of a system build, test and then finally release their system for use by the business.
Virtually the same...
Picture yourself as a deity in charge of a universe occupied by 'real' souls (production). Not really 'in change' since there's this thing called 'free will' but pretty down close... You can make whatever change you want but you really want to make sure the change will work and since you're a 'good' god, you want the changes to have a positive impact. You know that making the slightest change to your universe could have magnificent changes, good or bad. You decide that changes here should be made with care and you therefore limit who can do what here (ie, only you have access to make changes... your angels are out of luck).
So you decide to simulate your production universe by creating a sort of playground universe, a sandbox of sorts where you can build new things, test new ideas... You call this new place your development universe. No souls here... just you and the angels throwing out ideas and building new things and of course testing what is built based on how you 'think' it will be used. Your angels get to work building the change and once you believe you've got it right, you decide to mirror the changes to your production universe... but... you recall that sometimes what you've built breaks in your production enviroment (you wrote that holy bood well but for whatever reason the souls misinterpreted it and killed a bunch of people for oil - go figure)...
So you decide that your testing isn't enough and again mirror your production environment into a testing (acceptance) environment and recruit a bunch of souls (business folk) to test the changes since they have a better sense of how things work in the 'real' world. In this universe, there's sort of a mix of how things are governed... You still have the ultimate authority regards what happens here and can change things as needed but, you resisit the temptation since, in the real universe, on-the-fly changes are strongly discouraged. So, you allow on-the-fly changes here ONLY to grease the wheel as needed and insist that any of the changes should made here should also be made in the development universe... In essence, you figure out how to get it right in acceptance then make the changes in development and finally repromote everything back to acceptance so that the final test and sign-off by the testing 'souls' is generally on a change that requires NO on-the-fly fixes.
So, the souls sign-off on the change and you finally push the change to your real universe (production). All is well right? Well, you hope so... but their still killing for oil... woman are still being treated as second class citizens... kids are still being mistreated... geez...
It is standard business practice to have a Development, Acceptance and production environment. Each has it's own database, it's own programs, it's own security model and as you move from development to production, the security is tighter and tighter so that, what might have worked in development, won't work in Acceptance and certanly not in production... What worked in Acceptance SHOULD have worked in Production but it didn't. Knowing permission of an object in all three environments 'up front' is very helpful to make sure there are fewer surprises once your change is in production.
Sure, you SHOULD already know what the rules are but frankly, people change (along with their view of how security should work) and you just aren't always in the best position to know what's up.
I've been burned before... What worked in Acceptance didn't work in Production and it should have... Why didn't it? Because security folk were lax in Acceptance: they made a change 1st in Production rather than making the change 1st in Acceptance. It broke my system and I of course took the political hit and I intend that never to happen again.
