Shulou(Shulou.com)05/31 Report--

This article mainly explains "how to establish login access for SQL Server snapshot snapshot DB". The content of the article is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn "how to establish login access for SQL Server snapshot snapshot DB".

In order to provide the report query function of sql server, a timed snapshot job is usually established on the repository mirror to provide web query.

However, it is well known that the snapshot DB is read-only but not writable, so an error will be reported when the slave database is created login mapping and weight limit is granted:

USE [WEB02]

GO

CREATE USER [test] FOR LOGIN [test]

GO

USE [WEB02]

GO

EXEC sp_addrolemember database datareaderships, Numbtest`

GO

Failed to update database "WEB02" because the database is read-only. (Microsoft SQL Server, Error: 3906)

Establishment method:

1. First create the user of login and mapping corresponding to db in the main database

two。 Query the sid of the new login in the main database

Execute:

Select name,sid from sys.server_principals

Or select name,sid from sys.sql_logins

3. Then create a new login in the standby library and set sid equal to the sid of the main library login

USE [master]

GO

CREATE LOGIN [test] WITH PASSWORD=N'test', DEFAULT_DATABASE= [master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF sid=0xA366990EC2C0EE47B8826E5DAB5851CD

GO

So far, it's done.

The next time you refresh and establish a snapshot, the permissions related to login and user can be synchronized with the main database, and you can use login to access the snapshot DB.

Thank you for reading, the above is the content of "how to establish login access for SQL Server snapshot snapshot DB". After the study of this article, I believe you have a deeper understanding of how to establish login access for SQL Server snapshot snapshot DB, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.