August 7, 2019 at 9:02 pm
I have a simple SQL Server 2017 database in which I am unable to add one of the database users to the db_owner role. I have confirmed the user is actually setup in the database. When I try to add it to the db_owner role from my own machine, using SSMS or t-sql, I get a timeout error. Just for example, to show it is just simple code, this is the code I am using:
USE [MyDB]
GO
ALTER ROLE [db_owner] ADD MEMBER [myuser]
GO
When I connect to the server and try, both ways, I get the same problem.
I cannot find anything in the SQL Server error log or the Windows logs. I have tried a trace over all activity and nothing shows up. It doesn't throw any kind of error, it just doesn't work.
I have tried multiple other database users and they all get added immediately. The problem is only with this one user.
I'm not really in a situation where I can drop the user and re-create it, and try again, because this is a production system and this user has its own schema with a lot of objects.
We are scheduled for a maintenance window in a little over a week where this machine is going to be rebooted. I am going to try it again at that time, right after the reboot, but I am hoping someone might know something about it that will allow me to both get this done as well as understand what is happening.
I have been working with SQL Server for 20+ years and I have never seen anything like this. I can't find anything related to this issue.
I'm hoping someone else has run across this before and might have some insight into something I am missing.
Any help is greatly appreciated!
----------------------------------------------------------
01010011010100010100110000100000010100110110010101110010011101100110010101110010
001000000101001001101111011000110110101101110011
August 7, 2019 at 11:37 pm
You're using SSMS and error says it's timing out. What is the timeout setting in your SSMS?
In SSMS menu go to Tools, Options, Query Execution, then General. The 3rd item down is the Execution time-out. Setting it to 0 to makes it unlimited.
You say "this user has its own schema with a lot of objects" maybe that's the cause. The system has to check for control and ownership conflicts. Probably with a query that's timing out.
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
August 8, 2019 at 5:22 pm
This should be a very simple metadata operation. I have to admit, I have never seen anything like this, either. Is this a blocking issue? That would not surface in logs, or a trace.
August 8, 2019 at 5:30 pm
Sorry I am just now getting back to responding.
I have played with the timeouts and let it run a long time. I decided to try it again this morning and just let it keep running. About 15 minutes after I executed the query, someone contacted me and told me the system had become very slow. As soon as I killed my query, everything was running fine again. So yes, it could be some kind of blocking or some kind of checks going on that, with so much activity by this user, is just taking an enormous amount of time.
At this point, I am just going to wait until we have our maintenance window and, immediately after a restart, I am going to try this again before processes can get fired up.
----------------------------------------------------------
01010011010100010100110000100000010100110110010101110010011101100110010101110010
001000000101001001101111011000110110101101110011
August 8, 2019 at 9:21 pm
This is strange.
Couple things. Can you add a new user? If so, can they go into the db_owner role?
After that, can you look at sys.database_principals in the db and compare your problem user with the new one? Also, look at sys.database_role_members. Curious if you see any anomalies.
August 19, 2019 at 8:51 pm
OK, I was finally able to make the change and make this user part of the db_owner role. It took this long because I had to wait for our scheduled maintenance window. This window gave me the opportunity to restart my SQL Server services. That then allowed me to make the change as soon as the server came back up and before this user's activity started back up.
I don't know exactly what is\was happening under the covers but the user I was trying to add to the role had a LOT of activity going on. Apparently, something was going on with all of that activity when the change was being made. My guess is, if I had been able to let it run long enough, it would have eventually made the change but, as I mentioned in an earlier post, this also caused performance issues.
So, I didn't want to leave this topic just hanging so I wanted to thank everyone for their input and let you know what I did, even if I don't have all of the details about what exactly caused it to happen.
----------------------------------------------------------
01010011010100010100110000100000010100110110010101110010011101100110010101110010
001000000101001001101111011000110110101101110011
August 20, 2019 at 1:36 pm
Thanks for the note. If you learn more, we'd appreciate an update.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply