locked
Read access to readonly database? RRS feed

  • Question

  • Hello,

    I have a logshipping standby database on sql 2005 which is in readonly mode currently used for 1 user. The database is being logshipped from the production server every hour. How would I go about and create a login for a new user on the standby server without creating it in the production server and give it readonly access? Is it not possible?

    thanks in advance!

    Wednesday, April 4, 2012 5:57 PM

Answers

  • I think this is doable, at least with SQL Server authentication.

    Create a login on the production server, and a user in the database from that login. Grant that user the required permission. Then revoke CONNECT permission for that login. Run a SELECT from sys.server_principals and make note of the SID.

    On the standby server create a login with the same SID:

    CREATE LOGIN loggie WITH PASSWORD = 'p/&2P?´456rGG' SID = 0x....

    This is achievable with Windows authentication as well, but it may be more difficult to keep the user out.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Proposed as answer by Alex Feng (SQL) Monday, April 9, 2012 3:37 AM
    • Marked as answer by Maggie Luo Friday, April 13, 2012 9:14 AM
    Wednesday, April 4, 2012 9:59 PM
  • Run a SELECT from sys.server_principals and make note of the SID.

    On the standby server create a login with the same SID:

    CREATE LOGIN loggie WITH PASSWORD = 'p/&2P?´456rGG' SID = 0x....


    Or - concerning password AND SID - a suitable way is to use sp_help_revlogin which creates a scripted version of the sql login:
    http://support.microsoft.com/kb/918992

    Uwe Ricken

    MCITP Database Administrator 2005
    MCITP Database Administrator 2008
    MCITP Microsoft SQL Server 2008, Database Development

    db Berater GmbH
    http://www-db-berater.de

    • Proposed as answer by Sunil Gure Thursday, April 5, 2012 11:21 AM
    • Marked as answer by Maggie Luo Friday, April 13, 2012 9:14 AM
    Thursday, April 5, 2012 6:25 AM

All replies

  • Hello,

    I have a logshipping standby database on sql 2005 which is in readonly mode currently used for 1 user. The database is being logshipped from the production server every hour. How would I go about and create a login for a new user on the standby server without creating it in the production server and give it readonly access? Is it not possible?

    thanks in advance!

    it is possible if you give this user privileged access - eg. sysadmin etc. - but I don't think that you like it this way.

    Another way is to use Windows group(s) but those need to be added to the principal too.

    Wednesday, April 4, 2012 6:15 PM
  • Yeah.. i don't want to give sysadmin privileges to the user. So I guess its just not possible to do this in Sql server is it?
    Wednesday, April 4, 2012 6:40 PM
  • I think this is doable, at least with SQL Server authentication.

    Create a login on the production server, and a user in the database from that login. Grant that user the required permission. Then revoke CONNECT permission for that login. Run a SELECT from sys.server_principals and make note of the SID.

    On the standby server create a login with the same SID:

    CREATE LOGIN loggie WITH PASSWORD = 'p/&2P?´456rGG' SID = 0x....

    This is achievable with Windows authentication as well, but it may be more difficult to keep the user out.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Proposed as answer by Alex Feng (SQL) Monday, April 9, 2012 3:37 AM
    • Marked as answer by Maggie Luo Friday, April 13, 2012 9:14 AM
    Wednesday, April 4, 2012 9:59 PM
  • Run a SELECT from sys.server_principals and make note of the SID.

    On the standby server create a login with the same SID:

    CREATE LOGIN loggie WITH PASSWORD = 'p/&2P?´456rGG' SID = 0x....


    Or - concerning password AND SID - a suitable way is to use sp_help_revlogin which creates a scripted version of the sql login:
    http://support.microsoft.com/kb/918992

    Uwe Ricken

    MCITP Database Administrator 2005
    MCITP Database Administrator 2008
    MCITP Microsoft SQL Server 2008, Database Development

    db Berater GmbH
    http://www-db-berater.de

    • Proposed as answer by Sunil Gure Thursday, April 5, 2012 11:21 AM
    • Marked as answer by Maggie Luo Friday, April 13, 2012 9:14 AM
    Thursday, April 5, 2012 6:25 AM
  • Here are the steps you need to follow:

    1. Create login (do not map to user within database) on Primary server.
    2. Copy the revlogin script from KB article: http://support.microsoft.com/kb/918992 and execute it on primary server on master database.
    3. Create revlogin script of new created login at step1 using: Exec sp_help_revlogin @login_name = 'Loginname'
    4. Copy the output of above query and execute it on secondary server. It will create the login on secondary server with same SID
    5. On Primary server create a user for this login and map to logship database (db_datareader in your case)
    6. Once the logship is in sync with next t-log backup, you will have the read only permissions on secondary database.


    Kindly mark the reply as answer if they help

    Friday, April 6, 2012 7:45 AM
  • Good steps.  The only comment I have to add is that it is not necessary to separate step 1 and step 5. 

    If the logship user is synched to the secondary server before the login is added to that server, it will cause no problem.  The user will exist in the database, but without the login it is useless.   Once the login is created (step 4) and has the same SID that the user has, it will connect up again.

    Therefore I would make step 1 match what Erland said: 

    1. Create a login on the production server, and a user in the database from that login. Grant that user the required permission.

    FWIW,
    RLF

    Friday, April 6, 2012 1:42 PM