Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How to Give User Access to another user


How to Give User Access to another user

Author
Message
niladri.primalink
niladri.primalink
SSC-Enthusiastic
SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)

Group: General Forum Members
Points: 117 Visits: 226
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

Lowell
Lowell
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14958 Visits: 38972
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

--
help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

niladri.primalink
niladri.primalink
SSC-Enthusiastic
SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)

Group: General Forum Members
Points: 117 Visits: 226
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...
ldobusiness
ldobusiness
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 166
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
Lowell
Lowell
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14958 Visits: 38972
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

--
help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search