April 22, 2013 at 8:23 am
[font="Verdana"]Hi All,
In Server I have 2 users one is MasterAccess and other one is ap. there are many tables but masteracess only can view few tables of CAS database and ap user does not have the access of CAS database but he is the db_owner of other databases.
Now in AP5 database I have created a user MasterAccess and Now I have given the rights to the ap user who can view the tables of MasterAccess user. I have written the code like below
select * from CAS.dbo.
BankOffices
and the code is working fine. Now if i update the same table it is working.
Begin tran
update CAS.dbo.
BankOffices
set Bankoffices_Id =5
where bank_code=115
and the result shows me 2 rows affected....how? MasterAccess can only view the CAS table. He can not edit, insert; only he can view the selected 5 tables...
Now what I want is that ap user will also able to view these 5 table but he will not be able to insert, update delete etc...
Please help me[/font]
April 22, 2013 at 8:43 am
create a specific role with only the limited permissions you want.
once the role is created, then add the users to that role.
remember roles are cumulative, so if you add the user to other roles (like built in roles like db_owner, or db_data_reader) they may have more permissiosn than you wnat.
also, make sure the users'login is not in the sysadmin role, which would short circuit all permissions, and they could do anything they want to any data they want.
stick with the least permissions model:
a basic example:
--create a role for the envisioned process
CREATE ROLE [FiveTablesReadOnly]
--grant only the desired permissions for the five specific tables.
GRANT SELECT ON dbo.BankOffices TO [FiveTablesReadOnly];
GRANT SELECT ON dbo.BankInvoices TO [FiveTablesReadOnly];
GRANT SELECT ON dbo.BankAccounts TO [FiveTablesReadOnly];
GRANT SELECT ON dbo.BankLocations TO [FiveTablesReadOnly];
GRANT SELECT ON dbo.BankDetails TO [FiveTablesReadOnly];
--finally add our user(s) to the role:
EXEC sp_addrolemember N'FiveTablesReadOnly', N'MyAppUser'
Lowell
April 22, 2013 at 9:03 am
Hi Lowell,
Thanks for your reply I have craeted role under CAS database..then grant select option and after that when I add rolemember then it shows me error
User or role 'ap' does not exist in this database. as 'ap' does not have any access of CAS database.
Am I creating role in right place?
Please help...
April 22, 2013 at 9:28 am
you have to map the user to the role from the error i guess that the user is not mapped to the role that you created. Go to the role that you created and check if the user is there.
Plz let us know
April 22, 2013 at 9:39 am
niladri.primalink (4/22/2013)
Hi Lowell,Thanks for your reply I have craeted role under CAS database..then grant select option and after that when I add rolemember then it shows me error
User or role 'ap' does not exist in this database. as 'ap' does not have any access of CAS database.
Am I creating role in right place?
Please help...
most likely, like ldobusiness stated, you'll need to add the login for "ap" as a user in the CAS database; THEN add the user to the role:
for my code example below, Im guessing that the login is "ap", but modify to your actual names:
Create USER [ap] FOR LOGIN [ap]
EXEC sp_addrolemember N'FiveTablesReadOnly', N'ap'
then you can test it yourself, without the user's credentials/password, since you are a sysadmin:
--change into the test user:
EXECUTE AS USER='ap';
SELECT * FROM BankOffices --should work
DELETE FROM BankOffices WHERE 1=0 --will fail due to permissions; otherwise, no rows updated, but the user has permissiosn from another source.
REVERT;--change back into superuser
Lowell
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply