April 21, 2017 at 11:54 am
Our company delivers software that is supported through a back-end SQL server. The amount of data can be large. We usually request that our DB live on SQL Express (Small Shop) or SQL Standard (Large Shop). We maintain the SQL Server DB DDL/DML using an upgrade installer that launches scripts. This keeps the database up to date with the software it supports.
Currently we have the installer run as SysAdmin which is quite open and potentially a security issue if other databases are stood up next to ours. We need this permission mainly for these these three commands:
- Enable CLR on the Server
- We create a User on the server so our application can easily configure to the DB
- Database Compatibility on upgrades. We maintain the compatibility so we can set a standard of programming and datatypes available.
Question:
I do not like that we need to use SysAdmin. I know that is dangerous for our company. I do think these commands are necessary for our software to work. Compatibility, and CLR are both SysAdmin level permission. Do any software vendors deploy this way? Examples potentially could help my case. I would like to know some rules to live by when adding databases to other peoples servers. Also what I can expect from a DBA if I ask for CLR to be enabled.
April 21, 2017 at 12:26 pm
Setting compatibility mode is a database level permission (alter database) if not mistaken.
create user is securityadmin or alter any login (no need for sysadmin)
CLR is nearly sysadmin
In any case none of the above should normally be granted to anyone, specially not to a third party application.
From my point of view if a install needs those permissions just for a particular set of commands then those should be on a separate script with explicit instructions to have a DBA execute them before the main install is done.
As for CLR enabled.
Not all CLRs are bad - some may be some not.
Many DBA's will be happy to allow SAFE assemblies to be installed.
Installing any other type of assembly may get refused or may need to go through another set of configurations.
As a DBA I would also expect that your application does NOT require DB_OWNER role - Again if the install requires it that should be as a separate script/install
If doing installs on a small client which does not have a DBA then I would assume that your own company is most likely in charge of that server and this could be maintained by yourselves.
This is me - but each DBA/Company has their own rules
April 21, 2017 at 2:06 pm
The Application is Sproc/Read/Write Permissions. It is a separate install that does the DB maintenance as DB_Owner. That is safe. I was thinking something similar as to what you are saying. Basically two installs. The two installs could be daisy chained together for simple small shops. They would be separated for larger shops so a DBA could execute the scripts (instead of install) if necessary before the DB Owner updates the DB.
I was looking at the compatibility and you can not "change" compatibility as DB_Owner. That needs higher level permissions.
April 21, 2017 at 3:27 pm
JKSQL - Friday, April 21, 2017 2:06 PMThe Application is Sproc/Read/Write Permissions. It is a separate install that does the DB maintenance as DB_Owner. That is safe. I was thinking something similar as to what you are saying. Basically two installs. The two installs could be daisy chained together for simple small shops. They would be separated for larger shops so a DBA could execute the scripts (instead of install) if necessary before the DB Owner updates the DB.
I was looking at the compatibility and you can not "change" compatibility as DB_Owner. That needs higher level permissions.
Hum... what version of SQL Server?
Just tried it with 2014, created a standard user, and tried the following permissions
grant db_owner to user
then login as that user and issue
alter database test set compatibility_level = 110 (did try 100, 110, 120)
and it works as expected
remove db_owner from user
grant alter to testuser
then login as that user and issue
alter database test set compatibility_level = 110
and it also works as expected
April 21, 2017 at 3:48 pm
I did not use TSQL. I used the gui to do my test. The compatibility level is grayed out. I bet you are right that the gui does not work but TSQL will. I guess that means the only issue I have is CLR.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply