May 22, 2014 at 1:55 pm
I hate to ask this question because I might get raked over the coals because its a green question however I need to ask 🙁
SQL Logins - SIDS have to match
Domain Users - Do the SIDS have to match?
I added the users to the secondary server and it is mapped to the database with the permissions. I cannot fail it over yet to test, after hours thing, so is it just the SQL Logins that need the SIDS matched or both SQL and Domain?
Thanks so much!
MCSE SQL Server 2012\2014\2016
May 22, 2014 at 2:22 pm
The SQL login SIDs need to match but SQL Server gets the SIDs for domain users from active directory so it is the same everywhere.
May 22, 2014 at 2:29 pm
Thanks Jeremy, that would explain why I can't find the SIDS for domain users, LOL:-P
MCSE SQL Server 2012\2014\2016
May 22, 2014 at 2:33 pm
If you have added a login to SQL Server you can query either sys.syslogins or sys.server_principals to see the SIDs.
May 22, 2014 at 3:16 pm
JeremyE (5/22/2014)
If you have added a login to SQL Server you can query either sys.syslogins or sys.server_principals to see the SIDs.
Ya, I was using that table, was just making sure I wasn't totally crazy and blond today, still wishing it was Friday! HA!
I also found this on the net a few months ago;
-- Get Sids for all SQL Server logins on the old server instance
SELECT name, [sid]
FROM sys.server_principals
WHERE [type] = 's';
-- Create new SQL Login on new server instance
IF EXISTS (SELECT * FROM sys.server_principals WHERE name = N'SQLAppUser')
DROP LOGIN SQLAppUser;
GO
-- Use the sid from the old server instance
CREATE LOGIN SQLAppUser WITH PASSWORD = N'YourStrongPassword#', sid = 0x2F5B769F543973419BCEF78DE9FC1A64,
DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF;
GO
MCSE SQL Server 2012\2014\2016
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply