October 2, 2015 at 11:00 am
Hi all,
I'm having a hard time with security, and I'm trying to do some simple tests to see how SQL server security behaves. I'm using windows 7 and SQL Express 2012.
On my SQL server I have 2 databases, let's call them DB1 and DB2.
I created a windows group called Operators, and created a windows user "user1" which I added to the Operators group.
With no extra permissions, I log into windows and SSMS with user1. As expected, I can't access DB1 and DB2.
I then log into SSMS with SA and give the login [machine\Operators] db_owner rights on DB2.
I close my SSMs connection, and connect using user1... still no access to DB1 and DB2
I restarted SQL service... Still no access to DB1 and DB2
I restarted Windows, and now it is behaving as it should I have no access to DB1 and I have access to DB2...
Can Anyone explain this behavior? I can't believe that every time you make a change to the group you would need to restart Windows!!!
Am I missing something?
Any comment is welcome.
thank you
JG
October 2, 2015 at 12:28 pm
I think this may have to do with windows caching the AD information and what it had cached did not include the membership of user1. I had a similar issue before where an AD account had its name changed but the SQL Server did not recognize the name change until the instance was rebooted (http://www.sqlservercentral.com/Forums/Topic1175282-391-1.aspx). Sorry i cant give you an exact answer.
Under normal circumstances, you shouldn't have to reboot fro SQL server to recognize a AD group or account change.
October 2, 2015 at 1:32 pm
Fair Enough...
I thought there would maybe be a command like in MySQL's "Flush-privileges" to refresh the changes done to a user.
Thank you for your response.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply