[sql] SQL permissions for roles

I'm a bit confused about permissions in SQL

I have created a medical database.

As a script I'm creating a doctor role and I want to say they can perform updates on tables 1,2 and 3.

The database though actually contains 5 tables.

does it mean that they wont be able to update 4 and 5 ?

or will i have to explicitly say deny update on tables 4 and 5 for the doctor role ?

SQL-Server follows the principle of "Least Privilege" -- you must (explicitly) grant permissions.

'does it mean that they wont be able to update 4 and 5 ?'

If your users in the doctor role are only in the doctor role, then yes.

However, if those users are also in other roles (namely, other roles that do have access to 4 & 5), then no.

More Information: http://msdn.microsoft.com/en-us/library/bb669084%28v=vs.110%29.aspx

USE DataBaseName; GO --------- CREATE ROLE --------- CREATE ROLE Doctors ; GO  ---- Assign Role To users -------  CREATE USER [Username] FOR LOGIN [Domain\Username] EXEC sp_addrolemember N'Doctors', N'Username'  ----- GRANT Permission to Users Assinged with this Role----- GRANT ALL ON Table1, Table2, Table3 TO Doctors; GO