I have a rather specific MSSQL database question. I have a user that i want to give them the ability to make schema changes to just one database of the 15 that I have. What are the Server Roles and or database roles that I need to give them to allow this?
The user has been created, and I shared them with the database in question. From there i am not sure what combinations of Server Roles and Database role i need so they cannot have schema changing roles to all databases only for the one database i want.
Solved! Go to Solution.
Thank you so much @George_Thompson and @MarceloMarques !! Exactly what I needed. I will change the permissions.
Follow up question: I would like to add 'sde' to the processadmin fixed server role and grant the VIEW DEFINITION privilege on the database to drop geodatabase connections. (as per Esri documentation). I am getting push back from the DBA and they want to try lower permissions first to see if we can accomplish dropping geodatabase connections in ArcGIS Pro without that high of level of role and privileges. Do you have any advice for me on that?
Thank you!
I am not aware of another way to drop the connections without the processadmin fixed server role.
@AndreaB_ - lower permission will not work, for the sde user to be able to drop geodatabase connections the sde user needs to be granted processadmin privilege.
--Allows the sde user to kill connections to the geodatabase
EXEC master..sp_addsrvrolemember @loginame = N'sde', @rolename = N'processadmin'
GO
Privileges for geodatabases in SQL Server—ArcGIS Pro | Documentation
@George_Thompson @MarceloMarques Thank you for the confirmation!