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

How to Give User Access to another user Expand / Collapse
Author
Message
Posted Monday, April 22, 2013 8:23 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, November 25, 2014 2:00 AM
Points: 97, Visits: 188
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
Post #1445004
Posted Monday, April 22, 2013 8:43 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:08 PM
Points: 12,927, Visits: 32,333
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

--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 #1445016
Posted Monday, April 22, 2013 9:03 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, November 25, 2014 2:00 AM
Points: 97, Visits: 188
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...
Post #1445029
Posted Monday, April 22, 2013 9:28 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, October 29, 2014 3:04 PM
Points: 3, Visits: 141
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
Post #1445036
Posted Monday, April 22, 2013 9:39 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:08 PM
Points: 12,927, Visits: 32,333
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

--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 #1445039
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse