locked
question RRS feed

  • Question

  • Hi all,

     Do sql server Roles are copied into secondary server when a manual failover is done in Log shipping?

    How to move sql server 2000 master database to sql server 2005 server

    Friday, May 24, 2013 10:29 AM

Answers

  • Hallo SLNSH,

    I do not really know what type of roles you are meaning? if it is "SQL Server Roles" there is no need for it because it is server based security and has nothing to do with the database itself.

    If you mean LOGINS the answer is NO.
    You have to take care by yourself for the transfer of the logins.

    If it is windows security only you don't have to worry - just add the missing logins with CREATE LOGIN domain\user_or_group FROM WINDOWS;

    If it is a SQL account you have to take care of it and I would strongly recommend to transfer the sql logins with sp_helprevlogin instead of creation of the SQL login manually on the DR-Server. The reason is quite simple and is called "orphaned user".

    To understand orphaned users you have to know that SQL Server identifies a database user not by the name of a valid login but by the SId which is no problem if you use WINDOWS authentication (the SId will be generated by the AD and is uniqueness all around the domain).

    The SId of a SQL Server Login is different in each server. If the SId of the user in the database is not matching the SId of the LOGIN you create an "orphaned user".

    The really most stupid action which will be taken by some - unexperienced - dba is dropping the user from the database and adding the formerly new created Login. This can cause really much frustration if the dba hasn't checked the REAL privileges of the user. Most of above dba only look for the membership of database roles but didn't recognize that there are hundreds of securables which need to be checked, too.

    If you create a sql login on the primary server and add it to the secondary it is a "transaction" which means that the transaction which took place IN the log shipped database will be transferred to the secondary (SId, name, ... of primary) and will enter the database user as authorized object.

    If you need more details please concrete the first question please.

    SQL 2000 master database to SQL 2005 is nonsence because there are so much new features in architecture and objects in SQL 2005 that it isn't worse the spent time. BTW: I do not know whether it is possible from the technical side.

    If you only want to transfer the LOGINS it is so simple by T-SQL

    -- Script all domain users. SQL Users should be tranferred with sp_help_revlogin!!!
    SELECT	'IF NOT EXISTS (SELECT * FROM * sys.server_principals WHERE name ' + QUOTENAME(name, '''') + ')
    CREATE LOGIN ' + QUOTENAME(name) + ' FROM WINDOWS;'
    FROM	sys.syslogins
    WHERE	hasaccess = 1 AND
    		(
    			isntgroup = 1 OR
    			isntuser = 1
    		)
    
    -- Script the server role membership
    SELECT	'EXEC sp_addsrvrolemember @loginame = ' + QUOTENAME(name, '''') + ', @rolename = ''sysadmin'';'
    FROM	sys.syslogins
    WHERE	sysadmin = 1 AND
    		hasaccess = 1 AND
    		(
    			isntgroup = 1 OR
    			isntuser = 1
    		)
    
    UNION ALL
    
    SELECT	'EXEC sp_addsrvrolemember @loginame = ' + QUOTENAME(name, '''') + ', @rolename = ''securityadmin'';'
    FROM	sys.syslogins
    WHERE	securityadmin = 1 AND
    		hasaccess = 1 AND
    		(
    			isntgroup = 1 OR
    			isntuser = 1
    		)
    		
    UNION ALL
    
    SELECT	'EXEC sp_addsrvrolemember @loginame = ' + QUOTENAME(name, '''') + ', @rolename = ''serveradmin'';'
    FROM	sys.syslogins
    WHERE	serveradmin = 1 AND
    		hasaccess = 1 AND
    		(
    			isntgroup = 1 OR
    			isntuser = 1
    		)
    
    UNION ALL
    
    SELECT	'EXEC sp_addsrvrolemember @loginame = ' + QUOTENAME(name, '''') + ', @rolename = ''setupadmin'';'
    FROM	sys.syslogins
    WHERE	setupadmin = 1 AND
    		hasaccess = 1 AND
    		(
    			isntgroup = 1 OR
    			isntuser = 1
    		)
    
    UNION ALL
    
    SELECT	'EXEC sp_addsrvrolemember @loginame = ' + QUOTENAME(name, '''') + ', @rolename = ''processadmin'';'
    FROM	sys.syslogins
    WHERE	processadmin = 1 AND
    		hasaccess = 1 AND
    		(
    			isntgroup = 1 OR
    			isntuser = 1
    		)
    
    UNION ALL
    
    SELECT	'EXEC sp_addsrvrolemember @loginame = ' + QUOTENAME(name, '''') + ', @rolename = ''diskadmin'';'
    FROM	sys.syslogins
    WHERE	diskadmin = 1 AND
    		hasaccess = 1 AND
    		(
    			isntgroup = 1 OR
    			isntuser = 1
    		)
    
    UNION ALL
    
    SELECT	'EXEC sp_addsrvrolemember @loginame = ' + QUOTENAME(name, '''') + ', @rolename = ''dbcreator'';'
    FROM	sys.syslogins
    WHERE	dbcreator = 1 AND
    		hasaccess = 1 AND
    		(
    			isntgroup = 1 OR
    			isntuser = 1
    		)
    
    UNION ALL
    
    SELECT	'EXEC sp_addsrvrolemember @loginame = ' + QUOTENAME(name, '''') + ', @rolename = ''bulkadmin'';'
    FROM	sys.syslogins
    WHERE	bulkadmin = 1 AND
    		hasaccess = 1 AND
    		(
    			isntgroup = 1 OR
    			isntuser = 1
    		)

    The above script returns for all WINDOWS logins the sql commands for their creation and their server roles. Please consider to use sp_help_revlogin if you want to transfer sql logins.

    BTW: Exactly the same problem will be occured if you use mirroring! That's were I've got painful experiences with :)


    Uwe Ricken

    MCSE - SQL Server 2012
    MCSA - SQL Server 2012
    MCITP Database Administrator 2005
    MCITP Database Administrator 2008
    MCITP Microsoft SQL Server 2008, Database Development

    db Berater GmbH
    http://www-db-berater.de
    SQL Server Blog (german only)

    Friday, May 24, 2013 11:40 AM

All replies

  • You cannot move system databases from one version of SQL to another. You need to upgrade it from SQL 2000 to SQL 2005.

    Thanks & Regards RAJUKIRAN L Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers.

    Friday, May 24, 2013 10:59 AM
  •  Do sql server Roles are copied into secondary server when a manual failover is done in Log shipping?

    The standby server should be setup the same way as the primary server for easy failover.


    Kalman Toth Database & OLAP Architect sqlusa.com
    New Book / Kindle: Pass SQL Exam 70-461 & Job Interview: Programming SQL Server 2012

    Friday, May 24, 2013 11:23 AM
  • Hallo SLNSH,

    I do not really know what type of roles you are meaning? if it is "SQL Server Roles" there is no need for it because it is server based security and has nothing to do with the database itself.

    If you mean LOGINS the answer is NO.
    You have to take care by yourself for the transfer of the logins.

    If it is windows security only you don't have to worry - just add the missing logins with CREATE LOGIN domain\user_or_group FROM WINDOWS;

    If it is a SQL account you have to take care of it and I would strongly recommend to transfer the sql logins with sp_helprevlogin instead of creation of the SQL login manually on the DR-Server. The reason is quite simple and is called "orphaned user".

    To understand orphaned users you have to know that SQL Server identifies a database user not by the name of a valid login but by the SId which is no problem if you use WINDOWS authentication (the SId will be generated by the AD and is uniqueness all around the domain).

    The SId of a SQL Server Login is different in each server. If the SId of the user in the database is not matching the SId of the LOGIN you create an "orphaned user".

    The really most stupid action which will be taken by some - unexperienced - dba is dropping the user from the database and adding the formerly new created Login. This can cause really much frustration if the dba hasn't checked the REAL privileges of the user. Most of above dba only look for the membership of database roles but didn't recognize that there are hundreds of securables which need to be checked, too.

    If you create a sql login on the primary server and add it to the secondary it is a "transaction" which means that the transaction which took place IN the log shipped database will be transferred to the secondary (SId, name, ... of primary) and will enter the database user as authorized object.

    If you need more details please concrete the first question please.

    SQL 2000 master database to SQL 2005 is nonsence because there are so much new features in architecture and objects in SQL 2005 that it isn't worse the spent time. BTW: I do not know whether it is possible from the technical side.

    If you only want to transfer the LOGINS it is so simple by T-SQL

    -- Script all domain users. SQL Users should be tranferred with sp_help_revlogin!!!
    SELECT	'IF NOT EXISTS (SELECT * FROM * sys.server_principals WHERE name ' + QUOTENAME(name, '''') + ')
    CREATE LOGIN ' + QUOTENAME(name) + ' FROM WINDOWS;'
    FROM	sys.syslogins
    WHERE	hasaccess = 1 AND
    		(
    			isntgroup = 1 OR
    			isntuser = 1
    		)
    
    -- Script the server role membership
    SELECT	'EXEC sp_addsrvrolemember @loginame = ' + QUOTENAME(name, '''') + ', @rolename = ''sysadmin'';'
    FROM	sys.syslogins
    WHERE	sysadmin = 1 AND
    		hasaccess = 1 AND
    		(
    			isntgroup = 1 OR
    			isntuser = 1
    		)
    
    UNION ALL
    
    SELECT	'EXEC sp_addsrvrolemember @loginame = ' + QUOTENAME(name, '''') + ', @rolename = ''securityadmin'';'
    FROM	sys.syslogins
    WHERE	securityadmin = 1 AND
    		hasaccess = 1 AND
    		(
    			isntgroup = 1 OR
    			isntuser = 1
    		)
    		
    UNION ALL
    
    SELECT	'EXEC sp_addsrvrolemember @loginame = ' + QUOTENAME(name, '''') + ', @rolename = ''serveradmin'';'
    FROM	sys.syslogins
    WHERE	serveradmin = 1 AND
    		hasaccess = 1 AND
    		(
    			isntgroup = 1 OR
    			isntuser = 1
    		)
    
    UNION ALL
    
    SELECT	'EXEC sp_addsrvrolemember @loginame = ' + QUOTENAME(name, '''') + ', @rolename = ''setupadmin'';'
    FROM	sys.syslogins
    WHERE	setupadmin = 1 AND
    		hasaccess = 1 AND
    		(
    			isntgroup = 1 OR
    			isntuser = 1
    		)
    
    UNION ALL
    
    SELECT	'EXEC sp_addsrvrolemember @loginame = ' + QUOTENAME(name, '''') + ', @rolename = ''processadmin'';'
    FROM	sys.syslogins
    WHERE	processadmin = 1 AND
    		hasaccess = 1 AND
    		(
    			isntgroup = 1 OR
    			isntuser = 1
    		)
    
    UNION ALL
    
    SELECT	'EXEC sp_addsrvrolemember @loginame = ' + QUOTENAME(name, '''') + ', @rolename = ''diskadmin'';'
    FROM	sys.syslogins
    WHERE	diskadmin = 1 AND
    		hasaccess = 1 AND
    		(
    			isntgroup = 1 OR
    			isntuser = 1
    		)
    
    UNION ALL
    
    SELECT	'EXEC sp_addsrvrolemember @loginame = ' + QUOTENAME(name, '''') + ', @rolename = ''dbcreator'';'
    FROM	sys.syslogins
    WHERE	dbcreator = 1 AND
    		hasaccess = 1 AND
    		(
    			isntgroup = 1 OR
    			isntuser = 1
    		)
    
    UNION ALL
    
    SELECT	'EXEC sp_addsrvrolemember @loginame = ' + QUOTENAME(name, '''') + ', @rolename = ''bulkadmin'';'
    FROM	sys.syslogins
    WHERE	bulkadmin = 1 AND
    		hasaccess = 1 AND
    		(
    			isntgroup = 1 OR
    			isntuser = 1
    		)

    The above script returns for all WINDOWS logins the sql commands for their creation and their server roles. Please consider to use sp_help_revlogin if you want to transfer sql logins.

    BTW: Exactly the same problem will be occured if you use mirroring! That's were I've got painful experiences with :)


    Uwe Ricken

    MCSE - SQL Server 2012
    MCSA - SQL Server 2012
    MCITP Database Administrator 2005
    MCITP Database Administrator 2008
    MCITP Microsoft SQL Server 2008, Database Development

    db Berater GmbH
    http://www-db-berater.de
    SQL Server Blog (german only)

    Friday, May 24, 2013 11:40 AM
  • Microsoft has an excellent script for copying logins from one server to another - It includes the SID so you won't have to deal with orphaned users

    http://support.microsoft.com/kb/246133?wa=wsignin1.0

    Regards


    • Proposed as answer by SQL24 Friday, May 24, 2013 12:52 PM
    • Edited by SQL24 Friday, May 24, 2013 1:21 PM
    Friday, May 24, 2013 12:52 PM
  • Hallo SQL24,

    can you please avoid proposing your postings PERMANENTLY as answer.
    I would recommend to wait until the Threadstarter reads it and let ´him decide it by himself.

    Another option - just my point of view - is the review of answers by the regulars here.

    It isn't because I want to have my answers the correct one but...

    in the thread list is this thread marked as "nearby solved" because there is a green sign at this thread. In such a case others won't have a look into the thread for deeper details (like I do when I see an "answered" thread and at least one of the regulars).

    Thank you for attention!


    Uwe Ricken

    MCSE - SQL Server 2012
    MCSA - SQL Server 2012
    MCITP Database Administrator 2005
    MCITP Database Administrator 2008
    MCITP Microsoft SQL Server 2008, Database Development

    db Berater GmbH
    http://www-db-berater.de
    SQL Server Blog (german only)


    • Edited by Uwe RickenMVP Friday, May 24, 2013 1:11 PM
    • Marked as answer by SLNSH Friday, May 24, 2013 1:25 PM
    • Unmarked as answer by Olaf HelperMVP Friday, May 24, 2013 2:13 PM
    Friday, May 24, 2013 1:11 PM
  • Hallo SQL24,

    can you please avoid proposing your postings PERMANENTLY as answer.
    I would recommend to wait until the Threadstarter reads it and let ´him decide it by himself.

    Another option - just my point of view - is the review of answers by the regulars here.

    It isn't because I want to have my answers the correct one but...

    in the thread list is this thread marked as "nearby solved" because there is a green sign at this thread. In such a case others won't have a look into the thread for deeper details (like I do when I see an "answered" thread and at least one of the regulars).

    Thank you for attention!


    Uwe Ricken

    MCSE - SQL Server 2012
    MCSA - SQL Server 2012
    MCITP Database Administrator 2005
    MCITP Database Administrator 2008
    MCITP Microsoft SQL Server 2008, Database Development

    db Berater GmbH
    http://www-db-berater.de
    SQL Server Blog (german only)


    Hi Uwe

    My apologies - I'm new to the forums on MSDN and still finding my way around

    Regards

    Friday, May 24, 2013 1:22 PM
  • My apologies - I'm new to the forums on MSDN and still finding my way around

    Hello SQL24,

    It's no problem, but may you have a look at this TechNet Wiki article, which explains it more details: Whether or Not You Should Self Propose an Answer in an MSDN or TechNet Forum


    Olaf Helper

    Blog Xing

    Friday, May 24, 2013 2:12 PM