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 4 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply