How to create SQL Server login with Sid?
As per architecture of the users and login in SQL Server – there would be a user in database mapped to login. For simplicity let’s say login and user is AppUser which is available in AppDB. USE AppDB GO SELECT name, sid FROM sys.sysusers WHERE name = ‘AppUser’ GO USE MASTER GO SELECT name, sid FROM sys.sql_logins WHERE name = ‘AppUser’ GO.
Where are SIDS stored on a secondary server?
Here is what we would see on secondary server if login is created using UI. The solution of this would be to drop and create login with known SID which is stored in sys.sysusers in the database.
How to match SIDS in SQL Server 2005?
In SQL Server 2005 and above, we’ll use the sys.server_principals catalog view: Now, when we’re talking about matching up the SID, we’ll either do so by joining to the sysusers table (SQL Server 2000) or sys.database_principals catalog view (SQL Server 2005+).
How are SIDS assigned in Windows Active Directory?
Well, the Windows user will be assigned a new SID by the Active Directory. The new SID gets the next numbering and incremented by 1. The repercussion is that the new Windows user u007 SID will not be able to connect to SQL Server SQLP1 and SQLP2 because the binary SID value stored in SQL Server would not match.
Why do I have different SIDS in different databases?
A login may have different SIDs in different databases on a server. In this case, the login can only access the database that has the SID that matches the SID in the sys.server_principals view. This problem may occur if the two databases are combined from different servers.
How are logins created on the same server?
If server A and server B are in the same domain, the same SID is used. Therefore, users are unlikely to be orphaned. In the output script, the logins are created by using the encrypted password. This is because of the HASHED argument in the CREATE LOGIN statement.
Is there a way to get a Sid to work?
There is no way to get this to work. The only reason that Aaron’s approach appears to work (I am referring only to the SID that he is using; the dynamic SQL stuff is entirely correct) is by chopping off the excess bytes so that there are only 16 bytes left.