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.

  • Can't you add it to Security at the server level in SSMS (assuming thats what your using)? Then both databases can have the necessary privileges. Alternatively you could use a domain account.
    – adam
    Jul 8, 2015 at 19:41
  • One way would be to add a user to the other database which has the same name and maps to the same server-level login as the original user. Then of course grant select on at least that view. Jul 8, 2015 at 19:43

1 Answer 1

  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.

  • Under 'Security' I had 'Users' where I was able to user I had an issue with, there was no 'Logins' folder. Once in the properties for this user however, I couldn't find any 'User Mappings'. Jul 8, 2015 at 20:32
  • What version of SQL Server 2008 are you using? Are you using a SQL account that has all access like SA? you may not be able to see that feature if you do not have sufficient privileges under your current account. Jul 8, 2015 at 20:34
  • Microsoft SQL Server 2008 it all it says under Help > About, also this... Microsoft SQL Server Management Studio 10.0.1600.22 ((SQL_PreRelease).080709-1414 ). It's probably my account permissions... Jul 8, 2015 at 20:36
  • Contact your DBA and let them know what you need access to under your account, they will be able to make the change for you very easy. Be clear if you need select, update, insert, and delete permissions for what you are doing. In the event you are ever granted DBA rights this is how you would make the change to an account. Jul 8, 2015 at 20:48
  • 1
    @LaRae That's because you were looking under database > security (where users are defined) and not under server instance > security (where logins are defined). Jul 8, 2015 at 21:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.