Security access within schemas

• Imke Cronje

  SSCarpal Tunnel

  Points: 4826

  More actions

  October 26, 2009 at 4:03 am

  #216572

  Hi Guys,

  I am sitting with a situation. How do you limit a user on a database to only select/update/insert etc from a few tables within on the SQL instance. I have 2 schemas on my SQL instance with tables belonging to them and would like to have db_owner rights only on certain tables. I have db_owner rights on both logins but would not want the logins to see other see tables on select from it. Can anyone perhaps help.

  Regards

  IC

• Adi Cohn-120898

  SSC-Dedicated

  Points: 33944

  More actions

  October 26, 2009 at 9:46 am

  #1070784

  Db_woner is the name of a role that anyone it can do anything inside the database. If you want your users to have limited permissions (as they should), then first of all you need to take them out from db_owner role. You can assign permissions on table level and on schema level. You can do it with GRANT statement. BOL has lots of articles about GRANT statement on object or schema level. I recommend that you’ll have a look at it.

  Adi

  --------------------------------------------------------------
  To know how to ask questions and increase the chances of getting asnwers:
  http://www.sqlservercentral.com/articles/Best+Practices/61537/

  For better answers on performance questions, click on the following...
  http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

• Lowell

  SSC Guru

  Points: 323518

  More actions

  October 26, 2009 at 10:14 am

  #1070807

  here's an example I hope helps you visualize it:

  you want to try to give users only the rights they need.

  use [SandBox]

  --grant select/insert/update, but Not Delete to a specific table

  CREATE ROLE [VeryLimitedRights]

  GRANT INSERT ON [dbo].[WHATEVER] TO [VeryLimitedRights]

  GRANT SELECT ON [dbo].[WHATEVER] TO [VeryLimitedRights]

  GRANT UPDATE ON [dbo].[WHATEVER] TO [VeryLimitedRights]

  --grant readonly/select only to a different table

  GRANT SELECT ON [dbo].[SomeTable] TO [VeryLimitedRights]

  GO

  --give readonly/select for every table to this group--do they really need this?

  ALTER AUTHORIZATION ON SCHEMA::[db_datareader] TO [VeryLimitedRights]

  --add two specific users to this role; this assumes they are NOT in other roles that give them too much power!

  EXEC sp_addrolemember N'VeryLimitedRights', N'bob'

  EXEC sp_addrolemember N'VeryLimitedRights', N'jeff'

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!