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 12»»

Grant permission to access only 2 tables in a database Expand / Collapse
Author
Message
Posted Friday, July 22, 2011 7:26 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Yesterday @ 9:37 AM
Points: 82, Visits: 333
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
Post #1146626
Posted Friday, July 22, 2011 7:40 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 4:29 PM
Points: 12,741, Visits: 31,053
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1146631
Posted Friday, July 22, 2011 7:43 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 5:39 AM
Points: 2,104, Visits: 5,378
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/
Post #1146635
Posted Friday, July 22, 2011 11:41 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:25 AM
Points: 7,070, Visits: 12,523
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

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy
Post #1146822
Posted Thursday, October 13, 2011 8:43 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, October 13, 2011 8:43 AM
Points: 1, Visits: 21
Thanks this was helpfull
Post #1189908
Posted Wednesday, June 27, 2012 11:05 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, December 19, 2013 10:17 AM
Points: 3, Visits: 62
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
Post #1322052
Posted Wednesday, June 27, 2012 11:19 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:25 AM
Points: 7,070, Visits: 12,523
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

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy
Post #1322064
Posted Friday, July 20, 2012 3:24 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, October 05, 2012 8:14 AM
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 ?
Post #1332795
Posted Friday, July 20, 2012 4:30 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 4:29 PM
Points: 12,741, Visits: 31,053
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1332830
Posted Sunday, July 22, 2012 8:22 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, October 05, 2012 8:14 AM
Points: 34, Visits: 148
Thank You :)
Post #1333576
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse