How to Give User Access to another user

  • [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]

  • 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!

  • 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...

  • 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

  • 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!

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply