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 rolesgives 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 ?