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


Grant permission to access only 2 tables in a database


Grant permission to access only 2 tables in a database

Author
Message
xXShanXx
xXShanXx
SSC-Enthusiastic
SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)

Group: General Forum Members
Points: 122 Visits: 601
Hi

I am a newbie, I need to give an access to a selected i.e to a particular 2 tables in the database with
select and delete permissions only on SQL server 2008 R2 and also restrict the user to have access only to the table and not the stored procedures or views.

Is this possible ?

If so please advise how to do so.

Thanks a lot in advance.

Shan
Lowell
Lowell
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14957 Visits: 38958
SQL is very restrictive rights wise...users only get what you give them access to. You want to avoid things like just granting a user db_owner status or any other roles than you specifically create to avoid permissions overlap. that is typically where the problem lies, roles are cumulative , so when a user is in mulitple roles, and one of those roles
gives rights to more objects than the other roles.

here's a specific exmaple to use as a model:

--create a role to wrap up our permissions
CREATE ROLE TWOTABLEACCESS
GRANT SELECT,INSERT,UPDATE,DELETE ON dbo.ALL_BLOCKGROUPS TO TWOTABLEACCESS;
GRANT SELECT,INSERT,UPDATE,DELETE ON dbo.GEOSTATE TO TWOTABLEACCESS;

--now test the role
CREATE USER ROLETESTER WITHOUT LOGIN;

EXEC sp_addrolemember 'TWOTABLEACCESS','ROLETESTER'

EXECUTE AS USER = 'ROLETESTER'

--what tables can i see? better be just these two!
select * from sys.tables
SELECT * FROM dbo.GEOSTATE

REVERT; --change back to my normal, super admin login

--drop my test user:
DROP USER ROLETESTER
--add the real users to my role
EXEC sp_addrolemember 'TWOTABLEACCESS','bob'
EXEC sp_addrolemember 'TWOTABLEACCESS','myDomain\DataEntryGroup'

--now see if that user has too much access

EXECUTE AS USER = 'bob'

--what tables can i see? better be just these two!
select * from sys.tables
SELECT * FROM dbo.GEOSTATE

REVERT; --change back to my normal, super admin login






Lowell

--
help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Adi Cohn-120898
Adi Cohn-120898
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2224 Visits: 6079
You can give permissions on specific objects, so what you can do is just give the user permissions to do select and delete on those tables. If you don’t give permission on other objects and don’t add those users to roles with other permissions, your user will only be able to run the select and delete statement on those tables. You can use the GUI to grant the permissions or use GRANT statement (you can read about both ways in
Books On Line)

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Orlando Colamatteo
Orlando Colamatteo
SSCrazy Eights
SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)

Group: General Forum Members
Points: 8239 Visits: 14368
A good rule of thumb is to only grant user access to objects via Database Roles. It makes security management and auditing simpler in the long run and is not difficult to implement. I use a Database Role even when I only have one user that needs access to one specific table. In your situation I would:

1. Create a new Database Role named appropriately.

USE DatabaseName;
GO
CREATE ROLE AppropriateRoleName ;
GO



2. Grant SELECT on the two tables to the new role.

USE DatabaseName;
GO
GRANT SELECT ON dbo.Table1 TO AppropriateRoleName ;
GRANT SELECT ON dbo.Table2 TO AppropriateRoleName ;



3. Add the users to the role:

USE DatabaseName;
GO
EXEC sys.sp_addrolemember
@rolename = N'AppropriateRoleName',
@membername = N'DatabaseUserName' ;



__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
lkkanthanlk
lkkanthanlk
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: 21
Thanks this was helpfull
bmanning 59285
bmanning 59285
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 65
Hi

I am a newbie, I need to give an access to all tables and stored procedures in the database with
select and view permissions only on SQL server 2008 R2. I want to also restrict the user to have access only to the tables and the stored procedures or views.


If so please advise how to do so.

Thanks a lot in advance.

Brian
Orlando Colamatteo
Orlando Colamatteo
SSCrazy Eights
SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)

Group: General Forum Members
Points: 8239 Visits: 14368
bmanning 59285 (6/27/2012)
Hi

I am a newbie, I need to give an access to all tables and stored procedures in the database with
select and view permissions only on SQL server 2008 R2. I want to also restrict the user to have access only to the tables and the stored procedures or views.


If so please advise how to do so.

Thanks a lot in advance.

Brian

The first question I have is why do you want to give view and read permissions to all tables and view permissions to all procs? If we're talking about a production database that would violate the idea that one should have permissions to do their job, no more, no less. In some cases that does indeed mean giving perms to all objects as you mentioned (e.g. a prod support role) but usually that is not the case.


PS I see you are a new member on the site. Welcome to SSC. For future reference it's polite to start a new thread for new questions, instead of hijacking someone else's thread. Feel free to start a new thread and post the link here.

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
sufiyan.sarguroh
sufiyan.sarguroh
SSC Rookie
SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)

Group: General Forum Members
Points: 34 Visits: 148
Lowell (7/22/2011)
SQL is very restrictive rights wise...users only get what you give them access to. You want to avoid things like just granting a user db_owner status or any other roles than you specifically create to avoid permissions overlap. that is typically where the problem lies, roles are cumulative , so when a user is in mulitple roles, and one of those roles
gives rights to more objects than the other roles.

here's a specific exmaple to use as a model:

--create a role to wrap up our permissions
CREATE ROLE TWOTABLEACCESS
GRANT SELECT,INSERT,UPDATE,DELETE ON dbo.ALL_BLOCKGROUPS TO TWOTABLEACCESS;
GRANT SELECT,INSERT,UPDATE,DELETE ON dbo.GEOSTATE TO TWOTABLEACCESS;

--now test the role
CREATE USER ROLETESTER WITHOUT LOGIN;

EXEC sp_addrolemember 'TWOTABLEACCESS','ROLETESTER'

EXECUTE AS USER = 'ROLETESTER'

--what tables can i see? better be just these two!
select * from sys.tables
SELECT * FROM dbo.GEOSTATE

REVERT; --change back to my normal, super admin login

--drop my test user:
DROP USER ROLETESTER
--add the real users to my role
EXEC sp_addrolemember 'TWOTABLEACCESS','bob'
EXEC sp_addrolemember 'TWOTABLEACCESS','myDomain\DataEntryGroup'

--now see if that user has too much access

EXECUTE AS USER = 'bob'

--what tables can i see? better be just these two!
select * from sys.tables
SELECT * FROM dbo.GEOSTATE

REVERT; --change back to my normal, super admin login






This does give access to only two tables , but does this prevent him from viewing the SPs and views ? Does it have to be done explicitly .

Also,

we can do the same via GUI , cant we ? By using the Securables option ?
Lowell
Lowell
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14957 Visits: 38958
sufiyan.sarguroh (7/20/2012)


This does give access to only two tables , but does this prevent him from viewing the SPs and views ? Does it have to be done explicitly .

Also,

we can do the same via GUI , cant we ? By using the Securables option ?

You are correct, you can use the gui to do the same thing as the code examples.

If you dont grant access, a user cannot use procedures or views and also cannot see the text of the procsnor views either.

You get that ability from roles like ddl_admin, db_owner.
you can grant just the ability to see object definitions with GRANT VIEW DEFINITION whi ch is good for people making reports.

Lowell

--
help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

sufiyan.sarguroh
sufiyan.sarguroh
SSC Rookie
SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)

Group: General Forum Members
Points: 34 Visits: 148
Thank You Smile
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