What you will have to do is connect to the instance that has a mis-matched SID, and you'll have to recreate the login and specify an explicit SID. For instance, on the instance where you have the orphaned user and the following returns the user:
exec sp_change_users_login 'report';
go
Copy the SID from the column UserSID
. And if you already have an existing login that you want to preserve the name from on that instance, you can't. There is no way to ALTER LOGIN
and change the SID. So, you will need to drop the login and recreate it:
drop login YourLogin;
go
create login YourLogin
with
password = 'password',
check_policy = off, -- simple password and no check policy for example only
sid = 0xC26909...................;
go
Again, you will want to set the sid
parameter of CREATE LOGIN
to the UserSID
you retrieved from executing sp_change_users_login
with the report
.
Note: You will want to set the password on this/these replica(s) to the same so that you don't have a password mismatch between replicas.
I am assuming that your login(s) in question here are SQL Server logins, as this shouldn't be a problem for Windows Auth domain accounts because the SIDs should be the same on each instance.
You could also leverage contained databases here and have the database principal authenticate at the database level. That way you wouldn't have to worry about orphaned users.
Here is some documentation on this:
BOL reference on Management of Logins and Jobs for the Databases of an Availability Group
KB article on How to transfer logins and passwords between instances of SQL Server
Also, this should go without saying, but test this out in a development/QA environment to verify and ensure proper functionality before hitting production.
to do it correctly is not very simple, although once setup, your done.
Data users or application roles default have database scoped permissions. Database users are linked to Logins and therefore can have server wide permissions from their Login. Application roles aren't linked and can only get server wide permission by using a form of impersonation.
Any way to give these server scope permissions by means of impersonating will fail by default.This is by design.
You have 2 options:
- Tell SQL Server that you as a DBA trust any user in that database to have privilegde escalation to server wide permission if impersonation is used. You acomplish this by setting the option
TRUSTWORTHY
to ON
However if you don't fully understand what this option does first read:
SQL Server 2012 Best Practice Whitepaper (Specifically page 18 and 19 about "Database Ownership and Trust")
- Use signed stored procedures to accomplish your task.
I'll first give you the example of option 1:
CREATE LOGIN [appRoleProxy] WITH PASSWORD ='1234abcd!@'
GO
EXEC sp_addsrvrolemember @loginame='appRoleProxy',
@rolename='securityadmin'
GO
CREATE DATABASE [Stefaan]
go
ALTER DATABASE [Stefaan] SET TRUSTWORTHY ON
GO
USE [Stefaan]
GO
--add the proxy account to the dbo role since your stored procedure will be running in the context of this account and is also accessing your user database.
ALTER ROLE [db_owner] ADD MEMBER [appRoleProxy]
GO
CREATE APPLICATION ROLE appRole WITH PASSWORD ='Passw0rd!';
GO
CREATE PROCEDURE dbo.usp_testproc
@LoginName nvarchar(128),
@Password nvarchar(128),
@DBName nvarchar(128)
WITH EXECUTE AS 'appRoleProxy' --Impersonate a SQL server login with the proper server level permissions
AS
DECLARE @SQL varchar(max)
BEGIN
IF NOT EXISTS ( SELECT name
FROM sys.server_principals
WHERE name = @LoginName )
BEGIN
SET @SQL = 'CREATE LOGIN [' + @LoginName + '] WITH PASSWORD = '''
+ @Password + ''', DEFAULT_DATABASE=[' + @DBNAME
+ '], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF';
EXECUTE(@SQL);
END
IF NOT EXISTS ( SELECT name
FROM sys.database_principals
WHERE name = @LoginName )
BEGIN
SET @SQL = 'CREATE USER [' + @LoginName + '] FOR LOGIN ['
+ @LoginName + ']';
EXECUTE(@SQL);
END
IF EXISTS ( SELECT name
FROM sys.server_principals
WHERE name = @LoginName )
BEGIN
EXEC sp_addsrvrolemember @LoginName, 'securityadmin'
END
END
GO
GRANT EXECUTE ON [dbo].[usp_testproc] TO [appRole]
GO
sp_setapprole @rolename='appRole',
@password='Passw0rd!'
EXEC usp_testproc 'testlogin','password23@','stefaan'
Now for an example of option 2 I'd like to point out a answer a gave on another question:
Msg 4834 “You do not have permission to use the bulk load statement”
Best Answer
The query below is a good starting point. It returns a list of logins that have no matching user in any attached database. Be aware there are many reasons a login might exist at the server level, but not have an explicitly created user in any user database. Before removing the logins shown by this code, you should validate that they are not needed. As an example, this code may list the service accounts used to run SQL Server services; removing them might be a bad idea.
It filters out server roles, since they cannot be added to databases as users. Similarly for certificate-mapped logins, since they can only be used for code signing.
The query gathers the list of security identifiers from each database in the SQL Server instance, then returns logins that don't appear in that list of security identifiers.
There are a couple of "gotchas" with this approach. If any databases are offline when the code runs, users in the offline database(s) will not be excluded from the output, resulting in possible false-positives.
Since the script uses the security identifier, instead of the name of the user, it may return false positives for users and logins with the same name, but different SIDs. This can easily occur If you restore a database from a different source SQL Server and run this code against it without "fixing" the orphaned users first1.
1 - I am associated with the site SQLServerScience.com and wrote this blog post