July 22, 2011 at 7:26 am
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
July 22, 2011 at 7:40 am
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
July 22, 2011 at 7:43 am
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/
July 22, 2011 at 11:41 am
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
October 13, 2011 at 8:43 am
Thanks this was helpfull
June 27, 2012 at 11:05 am
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
June 27, 2012 at 11:19 am
bmanning 59285 (6/27/2012)
HiI 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
July 20, 2012 at 3:24 am
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 ?
July 20, 2012 at 4:30 am
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
July 22, 2012 at 8:22 pm
Thank You 🙂
July 24, 2013 at 1:01 pm
Hi Lowell,
I created one database called encrypt_test1, in that I created one table called Customer_data.
I encrypted single column, called "AccountNumber" in table called Customer_data.
Now I want to restrict other users to run the following command.
These other users are my team mates, and they have same access as me on "sqlserver/sqlserver2008r2" server.
right now they can run the following command, and can see encrypted data into decrypted form.
USE encrypt_test1;
GO
OPEN SYMMETRIC KEY SymmetricKey1
DECRYPTION BY CERTIFICATE Certificate1;
GO
-- Now list the original ID, the encrypted ID
SELECT Customer_id, Credit_card_number_encrypt AS 'Encrypted Credit Card Number',
CONVERT(varchar, DecryptByKey(Credit_card_number_encrypt)) AS 'Decrypted Credit Card Number'
FROM dbo.Customer_data;
-- Close the symmetric key
CLOSE SYMMETRIC KEY SymmetricKey1;
GO
i want to restrict them , so that they can not run the above select query and can not see the decrypted data/
Please Help.
thanks.
July 24, 2013 at 1:43 pm
Learner, it kind of sounds like these users are sysadmins? i don't think you can deny a sysadmin anything, so you have to take away their superpowers before you can do anything with them.
If you can't take away their superpowers, you are stuck, i think.
if they are not sysadmins, then some group they are in was given GRANT Symmetric Key Permissions (Transact-SQL), so you can simply explicitly deny them instead:
DENY Symmetric Key Permissions (Transact-SQL)
Lowell
July 24, 2013 at 1:49 pm
Thank Lowell!
Is it possible to restore the certifacate and symmetric key from backup location into the database's security folder into SQL SERVER 2008 R2?
thanks.
March 17, 2015 at 3:56 am
Hi Orlando,
If I need to give access more than 300 users only on two views, these views going to be used in SSRS and Tabular Model. So I need to implement security in Tabular level for more than 300 users, the script you have given is suitable in this case as well?
Many Thanks
Sangeeth
March 17, 2015 at 8:32 am
Sangeeth878787 (3/17/2015)
Hi Orlando,If I need to give access more than 300 users only on two views, these views going to be used in SSRS and Tabular Model. So I need to implement security in Tabular level for more than 300 users, the script you have given is suitable in this case as well?
Many Thanks
Sangeeth
Sure, follow the 3 steps I showed above.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply