SQL permissions for roles

Question

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

User · Answer

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  amp  5   then no   More Information  http   msdn microsoft com en-us library bb669084 28v vs 110 29 aspx

User · Answer

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

User · Answer

Unless the role was made dbo  db owner or db datawriter  it won t have permission to edit any data   If you want to grant full edit permissions to a single table  do this   GRANT ALL ON table1 TO doctor   Users in that role will have no permissions whatsoever to other tables  not even read

[sql] SQL permissions for roles

Examples related to sql

Examples related to sql-server