Access db

Question

Access db

sqlguru

SSC-Addicted

Points: 471
More actions
May 31, 2023 at 10:51 pm

#4201833

Access db wanting to move sql server, how much control does business have over table management?
June 2, 2023 at 9:47 am

This was removed by the editor as SPAM

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004323 More actions · Answer 1

I'm not sure that I understand your question but, at face value, I have to say that "It Depends" on the privs you give "the business", whatever that is defined as.

Personally, I would say only give the privs that are absolutely necessary and only give one other person the "sysadmin" role or "control server" privs. I'd also control such privs using Active Directory groups so that you don't have to mess around with maintaining individual privs in SQL Server. It would all be done at the "Domain" level in Active Directory.

--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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

sqlguru SSC-Addicted Points: 471 More actions · Answer 2

Planning to host this Access db migrate to SQL Server production. Having their own database would you give full access to that particular db in production? Since it's production do you see any issues giving full access to that database in production?

jonathan.crawford SSCertifiable Points: 6612 More actions · Answer 3

I can think of lots of ways that I would accidentally break production, yeah. The only way I'd see no "issues" would be if there was an ironclad written agreement signed by the requestor, my boss and reporting chain up to the CEO stating that I would not have to support or answer for any issues with this database in perpetuity, that the owner would do everything from updates to backups to patching.

-------------------------------------------------------------------------------------------------------------------------------------
Please follow Best Practices For Posting On Forums to receive quicker and higher quality responses

sqlguru SSC-Addicted Points: 471 More actions · Answer 4

But this will be hosted on production server where there are all other databases get backed up and patching etc. I agree that we should not support a database when someone has dbo owner access. But in this case the business is using Access managing their own Access db. So looking for same functionality but with a SQL Server. Please advise? Thanks in Advance!

pietlinden SSC Guru Points: 63503 More actions · Answer 5

"But in this case the business is using Access managing their own Access db. So looking for same functionality but with a SQL Server. Please advise? Thanks in Advance!"

Maybe I should stay out of this, but what do you mean by "managing their own Access db"? Are you trying to manage who has access to what objects in your database and/or what they can do? Access has been getting gutted over the years. So the notion of "Access security" absolutely makes me laugh now. That's been removed. Replication. Removed. Personally, I'd push all the non-static tables to SQL Server and keep the value list sources (well, the simple ones) in Access.

Do you mean "creating their own queries", or do you mean "managing data security" or ?

Phil Parkin SSC Guru Points: 246960 More actions · Answer 6

sqlguru wrote:

But this will be hosted on production server where there are all other databases get backed up and patching etc. I agree that we should not support a database when someone has dbo owner access. But in this case the business is using Access managing their own Access db. So looking for same functionality but with a SQL Server. Please advise? Thanks in Advance!

A business unit has asked you to host a database for them, on a server administered by you, but wants full ownership rights, is that correct? I'd be saying 'no' at this point. Of course, you want to be helpful, so you tell them that you can assist, subject to them following various rules and processes around access, security, version control and deployments (exact details to be determined). Your server is your controlled environment and you should not be giving away control lightly.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin