August 24, 2026 at 10:04 pm
In addition to what Frederico notes, what about separations/terminations? Easy to leave a security hole as the DBAs aren't notified as people move/leave. I would always have windows auth/Kerberos if possible. Windows accounts in Windows groups. Local SQL Roles, and the windows groups in SQL Roles.
Also not an issue for me. There are around ten people in the department, and we're all in one area. I know all of them personally and interact with them daily. One of them leaving would not go unnoticed, and would be accompanied by discussions of how to transfer their workload, farewell evenings at a local pub, and a fair amount of personal drama. It's not a normal corporate environment, but its a neat setup for me - I can do in large measure whatever I think is a good idea, and nobody hassles me about anything, as long as the database mostly runs when they need it, which is almost all the time. They're tolerant about things occasionally going sideways, because hitting me up means walking down the hall a few feet and talking to me, rather than submitting trouble tickets to some remote office that may or may not respond. I do things for them in real time, often in a matter of minutes, so they take my occasional mistakes in stride, as the price they pay for having instant response in important situations.
August 25, 2026 at 4:06 pm
It's not that someone leaving is unnoticed. It's that we forget to revoke all their access.
When someone leaves, I'm usually thinking about other things (workload I pick up or what will they do or I wish them well).
It's easy to forget to revoke access. Heck, Windows people do it all the time. HR gets a resignation or fires someone. No one else does anything.
August 25, 2026 at 7:48 pm
It's not that someone leaving is unnoticed. It's that we forget to revoke all their access. When someone leaves, I'm usually thinking about other things (workload I pick up or what will they do or I wish them well). It's easy to forget to revoke access. Heck, Windows people do it all the time. HR gets a resignation or fires someone. No one else does anything.
Yeah, I do understand how that could happen, but I don't think it will be an issue here. First of all, the DB is only accessible through the institution's internal network. There is no outside access, of any sort. They would have to get access to a computer connected to the internal network. Second, when someone leaves, the personnel department lets IT know, and they completely remove his account. Therefore, he is no longer a member of the departmental domain group, because his account has ceased to exist, and membership in that domain group is necessary to be able to connect to the server at all. What I want with my scheme of logins is once they are in the server, be able to control what the app shows them. I enable or disable specific controls, depending on what domain groups the user is in, and THAT is what I want to start doing myself, rather than having to contact IT every time some responsibilities within the department shift, which doesn't happen all that much anyway. Usually, people come in at the level they will be working while they are here, and when they leave, ALL access is revoked, so my forgetting to update something would have no impact. All it would mean that a user who no longer even exists has (had) access to a certain set of data and functions.
August 25, 2026 at 11:12 pm
If you have Windows logins as logins, that's fine.
I'd create app role groups and add the logins in here, and grant the permissions needed for each functional role. The execute as thing shouldn't come into play here.
Usually people have dbo or some schema own everything, so that permissions aren't checked. If I grant permissions to execute procA, which calls viewB that accesses tableC and tableD, I only grant permissions to procA.
If you have ownership changes in objects, you create the proc with execute as. The execute clause is described here: https://learn.microsoft.com/en-us/sql/t-sql/statements/create-procedure-transact-sql?view=sql-server-ver17#arguments
If this doesn't work, a repro would be better than trying to describe this. Create a few objects, grant permissions to a role and then show what code the user in the role runs
Viewing 4 posts - 16 through 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply