SQLServerCentral.com / SQL Server 2008 / SQL Server 2008 Administration / How to Give User Access to another user / Latest PostsInstantForum.NET v2.9.0SQLServerCentral.comhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSun, 02 Aug 2015 14:59:55 GMT20RE: How to Give User Access to another userhttp://www.sqlservercentral.com/Forums/Topic1445004-1550-1.aspx[quote][b]niladri.primalink (4/22/2013)[/b][hr]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 errorUser 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...[/quote]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:[code]Create USER [ap] FOR LOGIN [ap]EXEC sp_addrolemember N'FiveTablesReadOnly', N'ap'[/code]then you can test it yourself, without the user's credentials/password, since you are a sysadmin:[code]--change into the test user:EXECUTE AS USER='ap';SELECT * FROM BankOffices --should workDELETE 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[/code]Mon, 22 Apr 2013 09:39:47 GMTLowellRE: How to Give User Access to another userhttp://www.sqlservercentral.com/Forums/Topic1445004-1550-1.aspxyou 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 knowMon, 22 Apr 2013 09:28:14 GMTldobusinessRE: How to Give User Access to another userhttp://www.sqlservercentral.com/Forums/Topic1445004-1550-1.aspxHi 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 errorUser 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...Mon, 22 Apr 2013 09:03:27 GMTniladri.primalinkRE: How to Give User Access to another userhttp://www.sqlservercentral.com/Forums/Topic1445004-1550-1.aspxcreate 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:[code]--create a role for the envisioned processCREATE 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'[/code]Mon, 22 Apr 2013 08:43:51 GMTLowellHow to Give User Access to another userhttp://www.sqlservercentral.com/Forums/Topic1445004-1550-1.aspx[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 [b]ap user does not have the access of CAS database[/b] 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 tranupdate CAS.dbo.BankOffices set Bankoffices_Id =5where bank_code=115and 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]Mon, 22 Apr 2013 08:23:53 GMTniladri.primalink