Update passwords of SQL Server 2008 R2 users via script
Background: we have an old system in production on SQL Server 2008 R2. Due to some reasons real time replication to secondary is not implemented. In case of disaster at primary, we will switch to secondary machine and restore latest available backups (except for master, msdb, model dbs). Each of our user accesses the application via SQL Server database users.
Issue: now my concern is since we will not restore master database to the secondary server, how to deal with situation where users at primary database change their passwords. When we activate the services from secondary, how to restore their passwords from primary?
One approach is that we export the users from primary and restore it on secondary. But issue with this approach is that the users creation date on secondary will be change and this will be a audit issue. Is their any way we can update passwords only?
Regards
Salman
sql-server-2008
edited Nov 26 '18 at 10:10
marc_s
583k13011241270
asked Nov 26 '18 at 8:03
Salman RazaSalman Raza
34
add a comment |
Background: we have an old system in production on SQL Server 2008 R2. Due to some reasons real time replication to secondary is not implemented. In case of disaster at primary, we will switch to secondary machine and restore latest available backups (except for master, msdb, model dbs). Each of our user accesses the application via SQL Server database users.
Issue: now my concern is since we will not restore master database to the secondary server, how to deal with situation where users at primary database change their passwords. When we activate the services from secondary, how to restore their passwords from primary?
One approach is that we export the users from primary and restore it on secondary. But issue with this approach is that the users creation date on secondary will be change and this will be a audit issue. Is their any way we can update passwords only?
Regards
Salman
sql-server-2008
edited Nov 26 '18 at 10:10
marc_s
583k13011241270
asked Nov 26 '18 at 8:03
Salman RazaSalman Raza
34
add a comment |
Background: we have an old system in production on SQL Server 2008 R2. Due to some reasons real time replication to secondary is not implemented. In case of disaster at primary, we will switch to secondary machine and restore latest available backups (except for master, msdb, model dbs). Each of our user accesses the application via SQL Server database users.
Issue: now my concern is since we will not restore master database to the secondary server, how to deal with situation where users at primary database change their passwords. When we activate the services from secondary, how to restore their passwords from primary?
One approach is that we export the users from primary and restore it on secondary. But issue with this approach is that the users creation date on secondary will be change and this will be a audit issue. Is their any way we can update passwords only?
Regards
Salman
sql-server-2008
edited Nov 26 '18 at 10:10
marc_s
583k13011241270
asked Nov 26 '18 at 8:03
Salman RazaSalman Raza
34
Background: we have an old system in production on SQL Server 2008 R2. Due to some reasons real time replication to secondary is not implemented. In case of disaster at primary, we will switch to secondary machine and restore latest available backups (except for master, msdb, model dbs). Each of our user accesses the application via SQL Server database users.
Issue: now my concern is since we will not restore master database to the secondary server, how to deal with situation where users at primary database change their passwords. When we activate the services from secondary, how to restore their passwords from primary?
One approach is that we export the users from primary and restore it on secondary. But issue with this approach is that the users creation date on secondary will be change and this will be a audit issue. Is their any way we can update passwords only?
Regards
Salman
sql-server-2008
sql-server-2008
edited Nov 26 '18 at 10:10
marc_s
583k13011241270
asked Nov 26 '18 at 8:03
Salman RazaSalman Raza
34
edited Nov 26 '18 at 10:10
marc_s
583k13011241270
asked Nov 26 '18 at 8:03
Salman RazaSalman Raza
34
edited Nov 26 '18 at 10:10
marc_s
583k13011241270
edited Nov 26 '18 at 10:10
marc_s
583k13011241270
edited Nov 26 '18 at 10:10
marc_s
583k13011241270
583k13011241270
asked Nov 26 '18 at 8:03
Salman RazaSalman Raza
34
asked Nov 26 '18 at 8:03
Salman RazaSalman Raza
34
asked Nov 26 '18 at 8:03
Salman RazaSalman Raza
34
34
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
dbatools has very handy PowerShell cmdlet (Copy-DbaLogin) to do this. Just create a job to run the following script:
Copy-DbaLogin -Source sqlserversrc -Destination sqlserverdst -Force
It will copy all logins from sqlserversrc to sqlserverdst and overwrite them in case they exists (which will handle password changes).
Another option is to use SSIS and Transfer Logins Task.
If you upgrade to SQL Server 2012 (or newer) you can also consider using contained databases. Then you will not need logins on the server, because the users with passwords are part of the database itself.
answered Nov 26 '18 at 9:56
Andrey NikolovAndrey Nikolov
4,3733922
Thank you, but if I use Copy-DbaLogin then either it will create user at the destination or overwrite it if it already exist, in both cases the user creation date will be change.
– Salman Raza
Nov 27 '18 at 5:06
No, but what's the point of doing that? The user is in the database, which will be overwritten when you restore it from the backup.
– Andrey Nikolov
Nov 27 '18 at 6:15
Actually each of our user has SQL Server DB id , our application use this id for user authentication . As SQL DB users resides in master table which we will not restore on secondary machines.
– Salman Raza
Nov 27 '18 at 7:32
There is no "DB id" in SQL Server. There are logins and users. In your case "DB id" must be a login (i.e. credentials, name and password). These are stored in master database. Copy-DbaLogin copies these. Then there are users, which are logins mapped to specific database. They are saved in the user database and knows the SID of the corresponding login. There is no password for the user - the login has a password. So when you restore a database, the worst case is that you created a new user after the last backup, but this must be covered by your RPO strategy anyway.
– Andrey Nikolov
Nov 27 '18 at 8:49
Agree our users will be available as we restore the databases. Logins are already available in secondary. New logins will be imported via script. Only issue is logins whose passwords have been change in primary. We have 2 options 1. Delete logins from secondary and recreate with same SID/Credentials(we have a script). But user creation date will be change (Audit Issue). 2. Other option is that we will request only those user to change passwords once we switch to DR. If I don't get method to update logins passwords then I'll go with the second option and made it part of DR Strategy.
– Salman Raza
Nov 27 '18 at 12:02
|
show 1 more comment
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53476851%2fupdate-passwords-of-sql-server-2008-r2-users-via-script%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
dbatools has very handy PowerShell cmdlet (Copy-DbaLogin) to do this. Just create a job to run the following script:
Copy-DbaLogin -Source sqlserversrc -Destination sqlserverdst -Force
It will copy all logins from sqlserversrc to sqlserverdst and overwrite them in case they exists (which will handle password changes).
Another option is to use SSIS and Transfer Logins Task.
If you upgrade to SQL Server 2012 (or newer) you can also consider using contained databases. Then you will not need logins on the server, because the users with passwords are part of the database itself.
answered Nov 26 '18 at 9:56
Andrey NikolovAndrey Nikolov
4,3733922
Thank you, but if I use Copy-DbaLogin then either it will create user at the destination or overwrite it if it already exist, in both cases the user creation date will be change.
– Salman Raza
Nov 27 '18 at 5:06
No, but what's the point of doing that? The user is in the database, which will be overwritten when you restore it from the backup.
– Andrey Nikolov
Nov 27 '18 at 6:15
Actually each of our user has SQL Server DB id , our application use this id for user authentication . As SQL DB users resides in master table which we will not restore on secondary machines.
– Salman Raza
Nov 27 '18 at 7:32
There is no "DB id" in SQL Server. There are logins and users. In your case "DB id" must be a login (i.e. credentials, name and password). These are stored in master database. Copy-DbaLogin copies these. Then there are users, which are logins mapped to specific database. They are saved in the user database and knows the SID of the corresponding login. There is no password for the user - the login has a password. So when you restore a database, the worst case is that you created a new user after the last backup, but this must be covered by your RPO strategy anyway.
– Andrey Nikolov
Nov 27 '18 at 8:49
Agree our users will be available as we restore the databases. Logins are already available in secondary. New logins will be imported via script. Only issue is logins whose passwords have been change in primary. We have 2 options 1. Delete logins from secondary and recreate with same SID/Credentials(we have a script). But user creation date will be change (Audit Issue). 2. Other option is that we will request only those user to change passwords once we switch to DR. If I don't get method to update logins passwords then I'll go with the second option and made it part of DR Strategy.
– Salman Raza
Nov 27 '18 at 12:02
|
show 1 more comment
dbatools has very handy PowerShell cmdlet (Copy-DbaLogin) to do this. Just create a job to run the following script:
Copy-DbaLogin -Source sqlserversrc -Destination sqlserverdst -Force
It will copy all logins from sqlserversrc to sqlserverdst and overwrite them in case they exists (which will handle password changes).
Another option is to use SSIS and Transfer Logins Task.
If you upgrade to SQL Server 2012 (or newer) you can also consider using contained databases. Then you will not need logins on the server, because the users with passwords are part of the database itself.
answered Nov 26 '18 at 9:56
Andrey NikolovAndrey Nikolov
4,3733922
Thank you, but if I use Copy-DbaLogin then either it will create user at the destination or overwrite it if it already exist, in both cases the user creation date will be change.
– Salman Raza
Nov 27 '18 at 5:06
No, but what's the point of doing that? The user is in the database, which will be overwritten when you restore it from the backup.
– Andrey Nikolov
Nov 27 '18 at 6:15
Actually each of our user has SQL Server DB id , our application use this id for user authentication . As SQL DB users resides in master table which we will not restore on secondary machines.
– Salman Raza
Nov 27 '18 at 7:32
There is no "DB id" in SQL Server. There are logins and users. In your case "DB id" must be a login (i.e. credentials, name and password). These are stored in master database. Copy-DbaLogin copies these. Then there are users, which are logins mapped to specific database. They are saved in the user database and knows the SID of the corresponding login. There is no password for the user - the login has a password. So when you restore a database, the worst case is that you created a new user after the last backup, but this must be covered by your RPO strategy anyway.
– Andrey Nikolov
Nov 27 '18 at 8:49
Agree our users will be available as we restore the databases. Logins are already available in secondary. New logins will be imported via script. Only issue is logins whose passwords have been change in primary. We have 2 options 1. Delete logins from secondary and recreate with same SID/Credentials(we have a script). But user creation date will be change (Audit Issue). 2. Other option is that we will request only those user to change passwords once we switch to DR. If I don't get method to update logins passwords then I'll go with the second option and made it part of DR Strategy.
– Salman Raza
Nov 27 '18 at 12:02
|
show 1 more comment
dbatools has very handy PowerShell cmdlet (Copy-DbaLogin) to do this. Just create a job to run the following script:
Copy-DbaLogin -Source sqlserversrc -Destination sqlserverdst -Force
It will copy all logins from sqlserversrc to sqlserverdst and overwrite them in case they exists (which will handle password changes).
Another option is to use SSIS and Transfer Logins Task.
If you upgrade to SQL Server 2012 (or newer) you can also consider using contained databases. Then you will not need logins on the server, because the users with passwords are part of the database itself.
answered Nov 26 '18 at 9:56
Andrey NikolovAndrey Nikolov
4,3733922
dbatools has very handy PowerShell cmdlet (Copy-DbaLogin) to do this. Just create a job to run the following script:
Copy-DbaLogin -Source sqlserversrc -Destination sqlserverdst -Force
It will copy all logins from sqlserversrc to sqlserverdst and overwrite them in case they exists (which will handle password changes).
Another option is to use SSIS and Transfer Logins Task.
If you upgrade to SQL Server 2012 (or newer) you can also consider using contained databases. Then you will not need logins on the server, because the users with passwords are part of the database itself.
answered Nov 26 '18 at 9:56
Andrey NikolovAndrey Nikolov
4,3733922
answered Nov 26 '18 at 9:56
Andrey NikolovAndrey Nikolov
4,3733922
answered Nov 26 '18 at 9:56
Andrey NikolovAndrey Nikolov
4,3733922
answered Nov 26 '18 at 9:56
Andrey NikolovAndrey Nikolov
4,3733922
4,3733922
Thank you, but if I use Copy-DbaLogin then either it will create user at the destination or overwrite it if it already exist, in both cases the user creation date will be change.
– Salman Raza
Nov 27 '18 at 5:06
No, but what's the point of doing that? The user is in the database, which will be overwritten when you restore it from the backup.
– Andrey Nikolov
Nov 27 '18 at 6:15
Actually each of our user has SQL Server DB id , our application use this id for user authentication . As SQL DB users resides in master table which we will not restore on secondary machines.
– Salman Raza
Nov 27 '18 at 7:32
There is no "DB id" in SQL Server. There are logins and users. In your case "DB id" must be a login (i.e. credentials, name and password). These are stored in master database. Copy-DbaLogin copies these. Then there are users, which are logins mapped to specific database. They are saved in the user database and knows the SID of the corresponding login. There is no password for the user - the login has a password. So when you restore a database, the worst case is that you created a new user after the last backup, but this must be covered by your RPO strategy anyway.
– Andrey Nikolov
Nov 27 '18 at 8:49
Agree our users will be available as we restore the databases. Logins are already available in secondary. New logins will be imported via script. Only issue is logins whose passwords have been change in primary. We have 2 options 1. Delete logins from secondary and recreate with same SID/Credentials(we have a script). But user creation date will be change (Audit Issue). 2. Other option is that we will request only those user to change passwords once we switch to DR. If I don't get method to update logins passwords then I'll go with the second option and made it part of DR Strategy.
– Salman Raza
Nov 27 '18 at 12:02
|
show 1 more comment
Thank you, but if I use Copy-DbaLogin then either it will create user at the destination or overwrite it if it already exist, in both cases the user creation date will be change.
– Salman Raza
Nov 27 '18 at 5:06
No, but what's the point of doing that? The user is in the database, which will be overwritten when you restore it from the backup.
– Andrey Nikolov
Nov 27 '18 at 6:15
Actually each of our user has SQL Server DB id , our application use this id for user authentication . As SQL DB users resides in master table which we will not restore on secondary machines.
– Salman Raza
Nov 27 '18 at 7:32
There is no "DB id" in SQL Server. There are logins and users. In your case "DB id" must be a login (i.e. credentials, name and password). These are stored in master database. Copy-DbaLogin copies these. Then there are users, which are logins mapped to specific database. They are saved in the user database and knows the SID of the corresponding login. There is no password for the user - the login has a password. So when you restore a database, the worst case is that you created a new user after the last backup, but this must be covered by your RPO strategy anyway.
– Andrey Nikolov
Nov 27 '18 at 8:49
Agree our users will be available as we restore the databases. Logins are already available in secondary. New logins will be imported via script. Only issue is logins whose passwords have been change in primary. We have 2 options 1. Delete logins from secondary and recreate with same SID/Credentials(we have a script). But user creation date will be change (Audit Issue). 2. Other option is that we will request only those user to change passwords once we switch to DR. If I don't get method to update logins passwords then I'll go with the second option and made it part of DR Strategy.
– Salman Raza
Nov 27 '18 at 12:02
Thank you, but if I use Copy-DbaLogin then either it will create user at the destination or overwrite it if it already exist, in both cases the user creation date will be change.
– Salman Raza
Nov 27 '18 at 5:06
Thank you, but if I use Copy-DbaLogin then either it will create user at the destination or overwrite it if it already exist, in both cases the user creation date will be change.
– Salman Raza
Nov 27 '18 at 5:06
No, but what's the point of doing that? The user is in the database, which will be overwritten when you restore it from the backup.
– Andrey Nikolov
Nov 27 '18 at 6:15
No, but what's the point of doing that? The user is in the database, which will be overwritten when you restore it from the backup.
– Andrey Nikolov
Nov 27 '18 at 6:15
Actually each of our user has SQL Server DB id , our application use this id for user authentication . As SQL DB users resides in master table which we will not restore on secondary machines.
– Salman Raza
Nov 27 '18 at 7:32
Actually each of our user has SQL Server DB id , our application use this id for user authentication . As SQL DB users resides in master table which we will not restore on secondary machines.
– Salman Raza
Nov 27 '18 at 7:32
There is no "DB id" in SQL Server. There are logins and users. In your case "DB id" must be a login (i.e. credentials, name and password). These are stored in master database. Copy-DbaLogin copies these. Then there are users, which are logins mapped to specific database. They are saved in the user database and knows the SID of the corresponding login. There is no password for the user - the login has a password. So when you restore a database, the worst case is that you created a new user after the last backup, but this must be covered by your RPO strategy anyway.
– Andrey Nikolov
Nov 27 '18 at 8:49
There is no "DB id" in SQL Server. There are logins and users. In your case "DB id" must be a login (i.e. credentials, name and password). These are stored in master database. Copy-DbaLogin copies these. Then there are users, which are logins mapped to specific database. They are saved in the user database and knows the SID of the corresponding login. There is no password for the user - the login has a password. So when you restore a database, the worst case is that you created a new user after the last backup, but this must be covered by your RPO strategy anyway.
– Andrey Nikolov
Nov 27 '18 at 8:49
Agree our users will be available as we restore the databases. Logins are already available in secondary. New logins will be imported via script. Only issue is logins whose passwords have been change in primary. We have 2 options 1. Delete logins from secondary and recreate with same SID/Credentials(we have a script). But user creation date will be change (Audit Issue). 2. Other option is that we will request only those user to change passwords once we switch to DR. If I don't get method to update logins passwords then I'll go with the second option and made it part of DR Strategy.
– Salman Raza
Nov 27 '18 at 12:02
Agree our users will be available as we restore the databases. Logins are already available in secondary. New logins will be imported via script. Only issue is logins whose passwords have been change in primary. We have 2 options 1. Delete logins from secondary and recreate with same SID/Credentials(we have a script). But user creation date will be change (Audit Issue). 2. Other option is that we will request only those user to change passwords once we switch to DR. If I don't get method to update logins passwords then I'll go with the second option and made it part of DR Strategy.
– Salman Raza
Nov 27 '18 at 12:02
|
show 1 more comment
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53476851%2fupdate-passwords-of-sql-server-2008-r2-users-via-script%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
