Sql-server – How to grant select on a table being used in a different database to a user role in SQL

sql serversql-server-2008

I have a view that's selecting rows from a table on a different database. When calling this view from my application I get an inner exception thrown saying the application user is not able to access the database being referenced. How do I grant permissions to this user on the separate database? I've tried it the traditional way but of course that user role wasn't found of that db. Should I create a user with the same name on the other db, this doesn't seem like it would work. I haven't been able to find a solution for this specific instance.

Best Answer

  1. Open SQL Server Management studio 2008.

  2. Expand the server instance > Security > Logins > and locate the log in you have having an issue with.

  3. Right click the user and select Properties.

  4. Select User Mappings.

  5. Locate the database you want the user to have access to and check the checkbox in the map column.

  6. Set the correct database membership for this user and click OK.

  7. Try your code again, be sure to qualify the path properly like [database_name]..[Table_Name] in your FROM.