February 4, 2009 at 9:59 am
Can someone please explain to me how exactly the permissions work within SQL Server?
I'm asking because I had a user who had a separate account which gave her ownership of a particular database, but when she went to update a row, update permission was denied. I investigated the problem and found out that she was part of a group that had been given db_denydatawriter access. I thought her db_owner access would up the db_denydatawriter access, but it didn't.
I am also asking, because I've encountered a couple of users without logins. Am I correct in assuming that if a user doesn't have a login to the instance, he/she cannot access any of the databases? I want to double check before I go ahead and delete my prod orphaned users. I don't want to break anything.
Thanks for your help in advance.
February 4, 2009 at 10:33 am
Hi Erin.
db_denydatareader and db_denydatawriter will always overide any other permissions (db_datareader, db_datawriter, dbo).
Regardless of what other permissions are set, the 2 deny permissions will always win over.
I believe that an orphaned user cannot access the instance since in order to login to the instance there needs to be a login in place - this happens before any connect permissions are evaluated for individual databases.
February 5, 2009 at 1:45 pm
erin.meglich (2/4/2009)
Can someone please explain to me how exactly the permissions work within SQL Server?I'm asking because I had a user who had a separate account which gave her ownership of a particular database, but when she went to update a row, update permission was denied. I investigated the problem and found out that she was part of a group that had been given db_denydatawriter access. I thought her db_owner access would up the db_denydatawriter access, but it didn't.
I am also asking, because I've encountered a couple of users without logins. Am I correct in assuming that if a user doesn't have a login to the instance, he/she cannot access any of the databases? I want to double check before I go ahead and delete my prod orphaned users. I don't want to break anything.
Thanks for your help in advance.
read this in bol
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/77158a9a-d638-4818-90a1-cb2eb57df514.htm
and
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/99b43a72-0722-4a7b-a493-bdee1c74c7b9.htm
Regards,
[font="Verdana"]Sqlfrenzy[/font]
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply