Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Grant Permission to database object


Grant Permission to database object

Author
Message
mhac_0301
mhac_0301
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
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.!
GRE (Gethyn Ellis)
GRE (Gethyn Ellis)
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1066 Visits: 2841
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 Ellisgethynellis.com
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search