October 23, 2011 at 7:04 am
Hi,
How can I migrate SQL Server 2005 logins and its permissions from one server to another ? As per my finding Microsoft has provided two stored procedure (sp_hexadecimal and sp_help_revlogin ) but I am not sure where these procedures will script out permissions as well. Please suggest.
Thanks in advance.
October 23, 2011 at 1:33 pm
database user permissions are held in the database itself. You only need to ensure that the server login SID matches the database user and this si what sp_helprevlogin does
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
October 24, 2011 at 6:53 am
Thanks Perry..:-)
October 25, 2011 at 6:17 am
there are functions within SSIS that can do this as well ...
MCITP: Database Administrator 2005
MCTS SQL Server 2008
MCP SQL 2012/2014
MCSA SQL Server 2012/2014
MCSE Data Management and Analytics
October 26, 2011 at 11:53 am
sp_help_revlogin will not copy any server roles assigned to the logins. That will be an additional step.
Also, just a side note, because I ran into this.... the default database is scripted out for the login, so it must exist on the destination or the statement will error out and not create the login.
October 26, 2011 at 12:01 pm
Ed Zann (10/26/2011)
the default database is scripted out for the login, so it must exist on the destination or the statement will error out and not create the login.
This is true for the SSIS task too 😉
At least with the output from sp_help_revlogin you have the option of editing it before executing.
I.e. set the def database to master for all accounts
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply