January 13, 2010 at 3:20 am
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.!
January 13, 2010 at 4:36 am
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
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply