I'm reading about security and i'm making some tests (after read).
I don't understand one behavior of the public database role.
By default the GUEST account is SQL Server 2005 is disabled (for user databases), including the AdventureWorks.
I have been reading that all users belong to public database role WHEN they are created.
I have created a user and it 's correct (nothing changed from 2000 to 2005 in this terms).
The new user now belongs to the public role.
Then, knowing that the Guest account is disabled by default in the AdventureWorks, i have created a new SQL Server login named TEST. This login (test) was not mapped to the AdventureWorks database, so, there is no database user account associated with the login test on database adventureworks.
I have tried to connect to the adventureworks (with the new user TEST), but i can not, even (this new user) being a member of the public database role that exists on the adventureworks.
I think that this happens because there is no user account associated with the login test and therefor
the account tries to connect trought the GUEST account witch is disabled by default.
1) Am i correct?
then i have mapped a user account to the login test, i have given the user acount test the database role of db_reader, so, now i can connect to this database and make some select statements.
Now, what i'm traing to achieve is give permissions of bd_owner to the public database role (in the adventureworks database) .
My goal is test in my enviroment this:
if the user account test, as db_reader permissions and belongs to the public database role , if i grant to the public database role the db_owner permissions, then, the user account test will have the permissions of db_owner, because he belongs to the public database role too.
How can i give the public role in adventureworks database, the db_owner permissions, so that the user test, can gain them?
Thank you Masters.
P.s - i don't know if i can do this (add permissions to a fixed database role like the public).
Can i give to a fixed server role the permissions of db_owner?