Grant Permission to database object

• mhac_0301

  SSC Veteran

  Points: 283

  More actions

  January 13, 2010 at 3:20 am

  #215417

  Hi,

  I am using sql server 2005.

  I have created a new database. and in that database, i have created a table.

  That database needs to be access by 2 users and so i added them up.

  But when I tried executing a simple select query, I got an error saying:

  Database access error -229 caused by user test. Error message: [Microsoft][ODBC SQL Server Driver][SQL Server]The SELECT permission was denied on the object 'Customers', database 'Smarts', schema 'dbo'..

  What should I do?

  I have tried adding each user permissions, and by adding the table it needs to be accessed in the Securables portion.

  Please help.

  Thanks in advance.!

• GRE (Gethyn Ellis)

  SSCrazy Eights

  Points: 9602

  More actions

  January 13, 2010 at 4:36 am

  #1102967

  The script below shows how to grant select permissions to a specific table.

  I create a login called test.

  --Creates a login

  USE [master]

  GO

  CREATE LOGIN [Test] WITH PASSWORD='P@ssw0rd'

  GO

  I then create a user in the smarts database mapped to my test login

  --Create Users in the database

  USE [Smarts]

  GO

  CREATE USER [Test] FOR LOGIN [Test]

  GO

  I can then either grant select to that specific user on that specific table:

  --Grants Select permissions to the table

  use [Smarts]

  GO

  GRANT SELECT ON [dbo].[Customers] TO [Test]

  GO

  Or you could add the user to the DB_READER database role...This will give the user read access to all database tables!

  --Alteratively you could add each user to the db_datareader

  USE [Smarts]

  GO

  EXEC sp_addrolemember N'db_datareader', N'Test'

  GO

  If you want something in between you can create a user defined role:

  --CREATE A User defined role

  USE [Smarts]

  GO

  CREATE ROLE [UR_SELECT_ROLE]

  GO

  Then add your users to the role who need the same set of permissions:

  --AND the test user to the role

  USE [Smarts]

  GO

  EXEC sp_addrolemember N'UR_SELECT_ROLE', N'Test'

  GO

  The grant select permissions to that role:

  --GRANT SELECT permissions on the table to the new role.

  --Add the users to this role that need the same permissions set

  use [Smarts]

  GO

  GRANT SELECT ON [dbo].[LAPTest] TO [UR_SELECT_ROLE]

  GO

  This can all be accomplished through the Management Studio GUI too

  Gethyn Elliswww.gethynellis.com