March 21, 2017 at 9:54 am
We have a classic conflict we've probably all seen:
A vender wants the service account to have sa rights on a MSSQL instance. We're fine with putting the service account in the dbowner group on their own databases, but there are other databases supporting other applications on the instance, so we don't want to give them sa rights. We're also fine with temporarily granting dbcreator or securityadmin during installs and upgrades because install routines often create databases and set up security on them, but for normal operations we want the service accounts to have the minimum amount of rights required.
I've found that the bulkadmin role is required if the service account performs bulk imports. What other server-level roles are sometimes legitimately required by vendor service accounts that already have dbowner on their databases?
March 21, 2017 at 9:58 am
It really depends on why exactly they think SA is required.
One option if they absolutely have to have SA is to just give them their own instance and not argue with them about it
March 21, 2017 at 10:05 am
The DB owner can do anything in their own database. That includes dropping it, so I would actually ask, does the application need the ability to do this?
As for other roles, that all depends on what the account actually needs to do. For example, if the account will have no need to BULK INSERT, it doesn't need to be granted the bulkadmin role. The permissions might not even need to be server level, for example, does it need the ability to use sp_send_dbmail? If so, the ability to do that isn't a server role, instead the login would need access to the msdb database, and granted the DatabaseMailUserRole Role (which is at database level).
The important thing is knowing what the account needs to do. If you know this, and can share that information, it would help.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
March 21, 2017 at 10:22 am
Sue
March 21, 2017 at 11:47 am
ZZartin - Tuesday, March 21, 2017 9:58 AMIt really depends on why exactly they think SA is required.One option if they absolutely have to have SA is to just give them their own instance and not argue with them about it
I'm sorry, but in most cases, for me, it just doesn't. SA is normally requested, or occasionally hysterically demanded either because they don't actually know what they want, or because they're just dangerously arrogant. If someone doesn't know what they're doing, don't give them sa. If someone has that mindset, don't give them sa.
If there is a genuine reason, it should only br granted when that reason applies and then reverted to a more appropriate level.
In some areas like medicine or finance, granting such rights could constitute a breech of the law or regulatory requirements
I'm a DBA.
I'm not paid to solve problems. I'm paid to prevent them.
March 21, 2017 at 2:46 pm
One big issue with SA isn't just that they can access other databases and do things with them, they can also access/alter the 'externals'. Things like changing MAXDOP, granting xp_cmdshell, granting permissions to anyone they want to the server and databases, and many other things. We have vendors who need SA so they can upgrade their proprietary databases...we do that on a 'on demand' request only. We provide it, monitor the changes, and then remove it. We also don't have any other databases on that server that the vendor would not need to access.
-SQLBill
March 21, 2017 at 5:53 pm
I agree.... no one but seriously trusted internal DBAs should have "SA" (sysadmin) privs. No applications, not even so called "system monitoring" software needs it. We're 1/6th of the way through the 21st century and software vendors need to finally figure that out or get out of the business. If they have an application that you just cannot live without, do yourself a favor and put it on a totally separate physical box on a separate domain where the server logins have virtually no privs. If that makes it so the application can't do its job, then find another vendor. I usually won't even allow an application to have privs higher than read/write and controlled exec privs on the databases.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 21, 2017 at 6:59 pm
Like many application this particular application has a database connection config screen with a "Test Connection" button. With the service account in the DB Owner group for it's database I tried several combinations of Public + dbcreator/bulkadmin/securityadmin but all failed until I put it in sysadmin. I'd like to find out what is really needed and just give it that
March 21, 2017 at 9:09 pm
dan-572483 - Tuesday, March 21, 2017 6:59 PMLike many application this particular application has a database connection config screen with a "Test Connection" button. With the service account in the DB Owner group for it's database I tried several combinations of Public + dbcreator/bulkadmin/securityadmin but all failed until I put it in sysadmin. I'd like to find out what is really needed and just give it that
It may be that the "Test Connection" button is establishing a connection to test the login/password, then checking if it's a member of the syadmin server role. If not, display a failure message. Like others have said, it may well be that the vendors doesn't want to figure out what the application requires, so they mandate sysadmin privs so everything is covered. Also like others have said, if this is something you can live without, do it. If not, give it it's own isolated instance and leave keep it isolated from the rest of your domain.
March 21, 2017 at 9:52 pm
dan-572483 - Tuesday, March 21, 2017 6:59 PMLike many application this particular application has a database connection config screen with a "Test Connection" button. With the service account in the DB Owner group for it's database I tried several combinations of Public + dbcreator/bulkadmin/securityadmin but all failed until I put it in sysadmin. I'd like to find out what is really needed and just give it that
Hmmmm.... I wonder what would happen if you gave it sysadmin just long enough to pass that test and then rolled it back to something more reasonable.
What does this app actually do?
--Jeff Moden
Change is inevitable... Change for the better is not.
March 22, 2017 at 4:21 am
Oh, one other thing to bear in mind. Check their support contract.
In spite of their demand for God Mode, I bet you what you like if they hose your* (not their, you're the one paying, it's your data and YOU, not they are the one with a duty of care to anyone whose data you hold in there) system, YOU are the one responsible for pulling them out of the **** as their contract will stipulate that they don't support SQL Server.
If they are the kind of - frankly to my mind - disreputable and unprofessional organization that's not going to move on this, and not going to behave in an acceptable manner it goes on it's own box, completely separated and firewalled as far as possible from the rest of your systems.
You also need to raise at the highest level within your organization the risks you will be running by allowing them to behave in this manner, and most importantly, the extent to which you are exposed and the risks to the company and those to whom you owe a duty of care as a result.
This is unequivocally and absolutely YOUR responsibility as a DBA to highlight to your decision makers upfront, and agree who holds the can when they screw up upfront, and to what degree it's even possible to protect them, and how. Making excuses about it after the event is, I have to say, an abdication of professional responsibility on YOUR part. The repercussions and risks of this approach need to be highlighted by you, to those who are taking the risk and they need to fully appreciate the risks they are buying into. These need to be discussed in a thoroughly professional, dispassionate, and responsible manner with the nature of the risk - fiduciary, legal, financial and reputational - laid out and fully understood and agreed. When it all hits the fan because of their cowboy approach at some point in the future, be clear YOU own it. NOT them
* As a company, with you as a representative and responsible agent, it doesn't 'belong' to you either
I'm a DBA.
I'm not paid to solve problems. I'm paid to prevent them.
March 22, 2017 at 12:50 pm
dan-572483 - Tuesday, March 21, 2017 6:59 PMLike many application this particular application has a database connection config screen with a "Test Connection" button. With the service account in the DB Owner group for it's database I tried several combinations of Public + dbcreator/bulkadmin/securityadmin but all failed until I put it in sysadmin. I'd like to find out what is really needed and just give it that
Let it fail. What we do when someone says "I need x permission" is to ask, what are you trying to do and what error(s) are you getting. Then based on that we grant appropriate permissions. If they can't tell us what they are trying to do and what error they are getting, we tell them they have appropriate permissions for what we understand they need to do, so there must be some other issue.
-SQLBill
March 28, 2017 at 7:55 am
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
March 28, 2017 at 8:01 am
If they insist on having a login to the 'SA' account, then you can play the role of a malicious genie by renaming the original SA account and then creating a new one having only those minimal permissions you deem are necessary. There are some server level permissions like VIEW SERVER STATE, VIEW DEFINITION, and ALTER TRACE that can enable the functionality required for performance monitoring and other power user tasks without granting them full control of the server in the same way that membership in SYSADMIN would.
use MASTER
go
alter login sa DISABLE;
go
alter login sa with name = [sa_bak];
go
create login sa
with PASSWORD = 'F5G8T9V0K1'
, DEFAULT_DATABASE = master;
go
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply