SQL authentication account with read-write access to only 1 table

• mtz676

  SSCrazy Eights

  Points: 9261

  More actions

  April 7, 2015 at 10:47 am

  #316410

  How can I create a SQL authentication account with read-write access to only 1 table in a SQL database.

• Lowell

  SSC Guru

  Points: 323546

  More actions

  April 7, 2015 at 11:31 am

  #1789274

  people only have access what you grant them, but you have to be careful with granting roles like db_datareader or giving people sysadmin rights.

  here's a full example:

  Create LOGIN [ClarkKent] WITH PASSWORD='NotTheRealPassword'

  USE [DCUniverse]

  Create USER [ClarkKent] FOR LOGIN [ClarkKent]

  CREATE ROLE [OnlyOneTable]

  --included as examples

  --EXEC sp_addrolemember N'db_ddladmin', N'OnlyOneTable'

  --EXEC sp_addrolemember N'db_datareader', N'OnlyOneTable'

  --EXEC sp_addrolemember N'db_datawriter', N'OnlyOneTable'

  --can the users EXECUTE procedures? comment out if false

  --GRANT EXECUTE TO [OnlyOneTable]

  --allow the users to see one specific table

  Grant SELECT ON [dbo].[TheDailyPlanet] To [OnlyOneTable]

  --finally add our user to the role:

  EXEC sp_addrolemember N'OnlyOneTable', N'ClarkKent'

  --test:

  EXECUTE AS USER='ClarkKent'

  --who am i?

  select suser_name()

  --do stuff

  SELECT * FROM [TheDailyPlanet]

  --he can't DELETE!

  DELETE FROM [TheDailyPlanet]

  --change back into superman

  REVERT;

  --clean up after ourselves

  /*

  DROP ROLE [OnlyOneTable]

  DROP USER [ClarkKent]

  DROP LOGIN [ClarkKent]

  */

  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!