question

asmasm avatar image
asmasm asked

Issue in database users. Could not access database while moving .mdf from one server to another

I have moved mdf and log files from sql sever 2014 to 2017 but i tried to remove all users and then re-created them with same name. Now i am facing issues.

I could not be able to access database through ssms but my application is using same username and password.

I have created user with below script

use master
go

Create LOGIN Lakes_Edit WITH PASSWORD='276', CHECK_POLICY=OFF ;
DENY VIEW ANY DATABASE TO Lakes_Edit;
ALTER AUTHORIZATION ON DATABASE:: Lakes TO Lakes_Edit;
go

Now i'm unable to access lakes db? Please help to to resolve it

permissionsuser rights
1 comment
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

ThomasRushton avatar image ThomasRushton ♦♦ commented ·

What do you see if you run `sp_change_users_login 'report'`?

If you see anything there, you'll need to go through a process sometimes known as "unorphaning" to reassociate the user ID in the database with the login ID on the server, as chances are, it's been recreated with a different SID. See https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-change-users-login-transact-sql?view=sql-server-2017

0 Likes 0 ·

0 Answers

·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.