Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Grant Permission to database object Expand / Collapse
Author
Message
Posted Wednesday, January 13, 2010 3:20 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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.!
Post #846775
Posted Wednesday, January 13, 2010 4:36 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, October 17, 2014 7:12 AM
Points: 1,031, Visits: 2,796
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
Post #846791
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse