locked
User can not access the database RRS feed

  • Question

  • Hi All

    I am SQL Developer and hardly know anything about the security architecture of SQL server.

     

    On a server I have found a user say reportuser who is used to view the reports so all the thing regarding execution of the procedure is done by this used.

    We are required to grant the exec rights to this user on all report related procedures (used in the MS BI solution)

    Now I am required to place the DWH DBs (there are two) on the other machine.

    I have took the backup from the database and restore it on the separate computer (which is standalone). Now when I connect to  reportuser  on the actual server i can brown through the databases but when I log in to the new sever  with reportuser login i can only browns through one of the two databases and for other there is a error message “Database DB_NAME is not accessible”.

     Please advise

    Thanks


    DigitalFM
    Tuesday, March 22, 2011 4:06 PM

Answers

  • You don't have to drop user, it put you to re-assign permission forall objects. You can assign him db_owner role which gave user to run any proc on the server.

    use this to assign permissions-

    exec sp_addrolemember 'db_owner', 'reportuser'

    • Marked as answer by DigitalFM Wednesday, March 23, 2011 2:33 PM
    Wednesday, March 23, 2011 1:58 PM

All replies

  • you might needs to create a login(reportUser) on new server.

    Please check here to transfer logins and passwords between the servers http://support.microsoft.com/kb/246133


    Tuesday, March 22, 2011 4:29 PM
  • I have checked through the admin login and found the specific user already there in the security folder for that database which it cannot access.


    DigitalFM
    Tuesday, March 22, 2011 4:35 PM
  • have you assign any permission to user ? can you double click on the user on database check what database roles has he got?

    Incase no permission assign then can you please run this in query window to allocate permissions.

    exec sp_addrolemember 'db_owner', 'reportuser'


     

    Tuesday, March 22, 2011 4:51 PM
  • User has the role with the name of the user for example if the user is report user the the role was also reportuser. I feel it strange but it is create by the DB when I have restored it.

     

     


    DigitalFM
    Tuesday, March 22, 2011 5:19 PM
  • If you know the password then I would suggest you to recreate login at server level then use this proc (sp_change_users_login) to map the orphan users

    more about fix the orphan users please check here http://msdn.microsoft.com/en-us/library/ms174378.aspx

    Tuesday, March 22, 2011 7:55 PM
  • I have dropedd the user and re-created it but it cost me to give rights to all the procedure again which it can execute, Please advise some link for the userrold management
    DigitalFM
    Wednesday, March 23, 2011 10:58 AM
  • You don't have to drop user, it put you to re-assign permission forall objects. You can assign him db_owner role which gave user to run any proc on the server.

    use this to assign permissions-

    exec sp_addrolemember 'db_owner', 'reportuser'

    • Marked as answer by DigitalFM Wednesday, March 23, 2011 2:33 PM
    Wednesday, March 23, 2011 1:58 PM