How to add SQL Login in Secondary server in Log Shipping?

 

For SQL Login, the SID is unique to the SQL Server instance level, so SID of SQL Login of Primary and Secondary are different.
It will result in orphan user.

Whereas Windows Login SID are unique to the Domain level. Hence when we create the Windows Login, SID are matching

Windows Login	SQL Login
If you have Windows logins, the login SID in SQL Server will match the SID from Active Directory. So all you need to do is create the same login on both your primary and secondary server.	SQL Server logins are a little more complicated, as the SQL Server assigns the SID when the login is created All database users are linked to logins by SID; if the SID of a login is different on the secondary server, the database users tied to that login will be orphaned. This can be fixed, but it's better to avoid the problem all together

 Solution :
Manually specifying of SID, while creating login on the Secondary SQL Server will resolve the issues.

Execute the sp_help_revlogin on your primary to generate all the create statements for your logins, both Windows and SQL Server. For each Windows Login you get code like this:

CREATE LOGIN [WinDomain\siva] FROM WINDOWS WITH DEFAULT_DATABASE = [master]

This will allow you to just recreate the login on the secondary server.

For each SQL Server Login you will get something like this statement:

CREATE LOGIN [sql_test1] WITH PASSWORD = 0x0100A7B0020EEAB8D0B17D28883AA11267W7575B2F55CEC9EE HASHED,
SID = 0x7DE18E2C10F4FE4CA127E9837FCE4D3C, DEFAULT_DATABASE = [master], CHECK_POLICY = OFF, CHECK_EXPIRATION = OFF

Notice that the statement contains the encrypted version of the password and the SID.
This means that when run on the secondary server, the password will match the SID and we will not have any issues after the failover.

Schema in SQL Server

 
Users and schemas are completely separate. The behavior of schemas changed in SQL Server 2005. Schemas are no longer equivalent to database users;A schema is simply a container of objects. Schemas own objects and principles own schemas.

1. A single schema can contain objects owned by multiple database users.
2. A database user can be dropped without dropping objects in a corresponding schema

A schema can be owned by any user, and its ownership is transferable.

Why Schema introduced on SQL Server?

The Users and Schemas separation means objects and schemas can be created before users are added to the database. It also means a user can be dropped without specifically dropping the objects owned by that user. A schema can only be owned by one user at a time, but a single user can simultaneously own many schemas.

How to transfer Schema ownership?

The following example transfers ownership of the schema LabProduction to user Sivaprasad.

ALTER AUTHORIZATION ON SCHEMA::LabProduction TO Sivaprasad;
GO

Changes the ownership of a securable – using ALTER AUTHORIZATION

ALTER AUTHORIZATION can be used to change the ownership of any entity that has an owner. Ownership of database-contained entities can be transferred to any database-level principal. Ownership of server-level entities can be transferred only to server-level principals

How to move objects between Schema?

ALTER SCHEMA can only be used to move securables between schemas in the same database. To change or drop a securable within a schema, use the ALTER or DROP statement specific to that securable.

For example, below modifies the schema HumanResources by transferring the table Address from schema Person into the schema

ALTER SCHEMA HumanResources TRANSFER Person.Address;
GO

-- Create TestDB Database Create database TestDB go -- Change the DB Context to TestDB Use TestDB go -- Created Schema Student Create Schema Student go --– Created table in Student schema – Create Table Student.Student_Info ( Student_No int Primary Key identity(1,1), Student_Name varchar(50) ) go --– Insert Data Insert Into Student.Student_Info Values('Sivaprasad') go --– Select Data Select * From Student.Student_Info go --– Create another schema Course Create Schema Course go --– Transfer Objects from Student Schema to Course Schema ALTER SCHEMA Course TRANSFER Student.Student_Info go --– Assign Permission to Schema GRANT SELECT ON SCHEMA::Course TO Sivaprasad go

On SQL Server Management Studio, How to list only the database, which user has access ?

 

The default behavior of SSMS when we login, it will show all the databases available on the SQL Server Instance. Irrespective of access on the databases user has.

The reason behind is, to load the SSMS faster. Otherwise, while loading the SSMS SQL Server has to verify for each databases if the user has access or not. Also if any of the databases or offline or closed, it has to figure out if that database has to be listed or not. All this takes time.
Hence by default the SSMS will show all the databases on SSMS.

If we really required to show only the database user has access, please use the command below to restrict the show all databases on SSMS,

DENY VIEW ANY DATABASE TO login_name

When VIEW ANY DATABASE is revoked, a user can only see master, tempdb, any database he owns, and the user’s current database context.