October 19, 2018 at 10:53 am
Hi there everyone,
I am reviewing SQL Managed Instances and so far seemed to be a good fit for the databases where i work.
However getting a few niggles. The database we have developed here does use some of the system dmv tables. Uses a domain service account to connect and use the database. However ive found that not all the DMV's can be accessed using the domain account. The account has been added to the db_owner group on the database. As its a domain constrained db user i cannot explicitly add permissions. Previously on our on prem servers we would be granting view server state and view all definitiions permissions to this user. Also as a DBA i would query some DMVs as part of my role. It seems that some of these DMVs can only be accessed via the SQL admin account. Given the number of people who need to access these its not my plan to let everyone log in to instance with that account. Just asking for trouble.
I feel like i am missing something obvious but cannot put my finger on how to grant the permission to those DMVs without letting everyone use the admin account. I have searched various sites and search engines. Raised tickets with Microsoft Support and not really moving forward much. If anyone has any pointers for me that would be great.
Also what are other members thoughts on the managed instance platform. Got to ask if you have uncovered any gotchas.
Many thanks.
October 24, 2018 at 1:45 pm
craig.gall - Friday, October 19, 2018 10:53 AMHi there everyone,I am reviewing SQL Managed Instances and so far seemed to be a good fit for the databases where i work.
However getting a few niggles. The database we have developed here does use some of the system dmv tables. Uses a domain service account to connect and use the database. However ive found that not all the DMV's can be accessed using the domain account. The account has been added to the db_owner group on the database. As its a domain constrained db user i cannot explicitly add permissions. Previously on our on prem servers we would be granting view server state and view all definitiions permissions to this user. Also as a DBA i would query some DMVs as part of my role. It seems that some of these DMVs can only be accessed via the SQL admin account. Given the number of people who need to access these its not my plan to let everyone log in to instance with that account. Just asking for trouble.
I feel like i am missing something obvious but cannot put my finger on how to grant the permission to those DMVs without letting everyone use the admin account. I have searched various sites and search engines. Raised tickets with Microsoft Support and not really moving forward much. If anyone has any pointers for me that would be great.
Also what are other members thoughts on the managed instance platform. Got to ask if you have uncovered any gotchas.
Many thanks.
If you didn't get anywhere with a support ticket with Microsoft, maybe you need to look at alternative ways to accomplish whatever it is you were doing originally.
Or it could be that getting your own VM is a better option than a managed instance.
Sue
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply