locked
Back up my security RRS feed

  • Question

  • If I want to back up my security means  to backup all logins from my instance. then wht should I do?
    "SQLSERVER DBA" "INDIA"
    Wednesday, October 6, 2010 11:45 AM

Answers

  • Hope you are taking backup of the master database; which contains all the information about the logins of the respective instance.

    HTH

    • Marked as answer by Ajay.G Thursday, October 7, 2010 5:39 AM
    Wednesday, October 6, 2010 11:58 AM
  • Logins are stored in the master system database.  As long as you backup system and user databases as part of your backup recovery plan, security principals and database permissions will be backed up too.
    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/
    • Marked as answer by Ajay.G Thursday, October 7, 2010 5:39 AM
    Wednesday, October 6, 2010 12:01 PM
  • --SQL Logins:
    SELECT 'CREATE LOGIN ' + QUOTENAME(name) + ' WITH PASSWORD='
    + sys.fn_varbintohexstr(password_hash) + ' HASHED, SID='
    + sys.fn_varbintohexstr(sid) + ', '
    + 'DEFAULT_DATABASE='+ QUOTENAME(COALESCE(default_database_name, 'master'))
    + ', DEFAULT_LANGUAGE=' + QUOTENAME(COALESCE(default_language_name,
    'us_english'))
    + ', CHECK_EXPIRATION=' + CASE is_expiration_checked WHEN 1 THEN 'ON' ELSE
    'OFF' END
    + ', CHECK_POLICY=' + CASE is_policy_checked WHEN 1 THEN 'ON' ELSE 'OFF' END
    FROM sys.sql_logins
    WHERE name<>'sa'
    UNION ALL
    --Windows logins:
    SELECT 'CREATE LOGIN ' + QUOTENAME(name) + ' FROM WINDOWS WITH '
    + 'DEFAULT_DATABASE='+ QUOTENAME(COALESCE(default_database_name, 'master'))
    + ', DEFAULT_LANGUAGE=' + QUOTENAME(COALESCE(default_language_name,
    'us_english'))
    FROM sys.server_principals
    WHERE type IN ('U','G')
    AND name NOT LIKE '%\SQLServer2005MSSQLUser$%$%'
    AND name NOT LIKE '%\SQLServer2005SQLAgentUser$%$%'
    AND name NOT LIKE '%\SQLServer2005MSFTEUser$%$%'
    AND name NOT IN ('BUILTIN\Administrators', 'NT AUTHORITY\SYSTEM');

    --Problem: when executing CREATE LOGINs generated by the query above,
    --it is not possible to assign default database when that database is
    mirrored or unavailable for some other reason.Looks like a bug.


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    • Marked as answer by Ajay.G Thursday, October 7, 2010 5:39 AM
    Wednesday, October 6, 2010 1:13 PM

All replies

  • Hope you are taking backup of the master database; which contains all the information about the logins of the respective instance.

    HTH

    • Marked as answer by Ajay.G Thursday, October 7, 2010 5:39 AM
    Wednesday, October 6, 2010 11:58 AM
  • Logins are stored in the master system database.  As long as you backup system and user databases as part of your backup recovery plan, security principals and database permissions will be backed up too.
    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/
    • Marked as answer by Ajay.G Thursday, October 7, 2010 5:39 AM
    Wednesday, October 6, 2010 12:01 PM
  • --SQL Logins:
    SELECT 'CREATE LOGIN ' + QUOTENAME(name) + ' WITH PASSWORD='
    + sys.fn_varbintohexstr(password_hash) + ' HASHED, SID='
    + sys.fn_varbintohexstr(sid) + ', '
    + 'DEFAULT_DATABASE='+ QUOTENAME(COALESCE(default_database_name, 'master'))
    + ', DEFAULT_LANGUAGE=' + QUOTENAME(COALESCE(default_language_name,
    'us_english'))
    + ', CHECK_EXPIRATION=' + CASE is_expiration_checked WHEN 1 THEN 'ON' ELSE
    'OFF' END
    + ', CHECK_POLICY=' + CASE is_policy_checked WHEN 1 THEN 'ON' ELSE 'OFF' END
    FROM sys.sql_logins
    WHERE name<>'sa'
    UNION ALL
    --Windows logins:
    SELECT 'CREATE LOGIN ' + QUOTENAME(name) + ' FROM WINDOWS WITH '
    + 'DEFAULT_DATABASE='+ QUOTENAME(COALESCE(default_database_name, 'master'))
    + ', DEFAULT_LANGUAGE=' + QUOTENAME(COALESCE(default_language_name,
    'us_english'))
    FROM sys.server_principals
    WHERE type IN ('U','G')
    AND name NOT LIKE '%\SQLServer2005MSSQLUser$%$%'
    AND name NOT LIKE '%\SQLServer2005SQLAgentUser$%$%'
    AND name NOT LIKE '%\SQLServer2005MSFTEUser$%$%'
    AND name NOT IN ('BUILTIN\Administrators', 'NT AUTHORITY\SYSTEM');

    --Problem: when executing CREATE LOGINs generated by the query above,
    --it is not possible to assign default database when that database is
    mirrored or unavailable for some other reason.Looks like a bug.


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    • Marked as answer by Ajay.G Thursday, October 7, 2010 5:39 AM
    Wednesday, October 6, 2010 1:13 PM
  • TRUE>>>Thanks
    "SQLSERVER DBA" "INDIA"
    Thursday, October 7, 2010 5:39 AM
  • You could also use the stored procedure sp_help_revlogin to script out all the logins and its passwords for SQL logins. But master backup is the best way to go
    Thanks, Leks
    Thursday, October 7, 2010 10:39 PM
    Answerer