|
|
|
Forum Newbie
      
Group: General Forum Members
Last Login: Friday, November 25, 2011 6:20 AM
Points: 1,
Visits: 39
|
|
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.!
|
|
|
|
|
Ten Centuries
      
Group: General Forum Members
Last Login: Tuesday, March 26, 2013 9:55 AM
Points: 1,024,
Visits: 2,768
|
|
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 Ellis
gethynellis.com
|
|
|
|