July 26, 2010 at 11:33 pm
Hi all,
How can we transfer the logins associated (mapped to) to only a particular database to another SQL Server.
I have tried the "Transfer Logins Task" in the SSIS. But when I execute the task I dont get the Logins at the SQL Server level (at server A),which are mapped to DATABASE1, to the server B.
I refuse to use the sp_change_users_login SP to create the SQL Loogins.
John
July 26, 2010 at 11:42 pm
try this
/*******************************************************
* SQL 2005 Extract Logins
* Hugh Scott
* 2010/01/06
*
* Description:
* Modified from the original to remove dependency on sp_VarbinaryToHex
* stored procedure. Other minor modifications as well.
*
* Modification History:
* DateInitialsModification
* 2010/01/06HMS- Original
*
*********************************************************/
use master
DECLARE @login_name sysname
SET @login_name = null
DECLARE @name sysname
DECLARE @type varchar (1)
DECLARE @hasaccess int
DECLARE @denylogin int
DECLARE @is_disabled int
DECLARE @PWD_varbinary varbinary (256)
DECLARE @PWD_string varchar (514)
DECLARE @SID_varbinary varbinary (85)
DECLARE @SID_string varchar (514)
DECLARE @tmpstr varchar (1024)
DECLARE @is_policy_checked varchar (3)
DECLARE @is_expiration_checked varchar (3)
DECLARE @defaultdb sysname
IF (@login_name IS NULL)
DECLARE login_curs CURSOR FOR
SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM
sys.server_principals p LEFT JOIN sys.syslogins l
ON ( l.name = p.name ) WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name <> 'sa'
ELSE
DECLARE login_curs SCROLL CURSOR FOR
SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM
sys.server_principals p LEFT JOIN sys.syslogins l
ON ( l.name = p.name ) WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name = @login_name
OPEN login_curs
FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin
IF (@@fetch_status = -1)
BEGIN
PRINT 'No login(s) found.'
CLOSE login_curs
DEALLOCATE login_curs
-- RETURN -1
END
SET @tmpstr = '/* sp_help_revlogin script '
PRINT @tmpstr
SET @tmpstr = '** Generated ' + CONVERT (varchar, GETDATE()) + ' on ' + @@SERVERNAME + ' */'
PRINT @tmpstr
PRINT ''
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
PRINT ''
SET @tmpstr = '-- Login: ' + @name
PRINT @tmpstr
IF (@type IN ( 'G', 'U'))
BEGIN -- NT authenticated account/group
SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' FROM WINDOWS WITH DEFAULT_DATABASE = [' + @defaultdb + ']'
END
ELSE BEGIN -- SQL Server authentication
-- obtain password and sid
SET @PWD_varbinary = CAST( LOGINPROPERTY( @name, 'PasswordHash' ) AS varbinary (256) )
-- EXEC sp_hexadecimal @PWD_varbinary, @PWD_string OUT
SELECT @PWD_string = master.dbo.fn_varbintohexstr(@PWD_varbinary)
-- EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT
SELECT @SID_string = master.dbo.fn_varbintohexstr(@SID_varbinary)
-- obtain password policy state
SELECT @is_policy_checked = CASE is_policy_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END FROM sys.sql_logins WHERE name = @name
SELECT @is_expiration_checked = CASE is_expiration_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END FROM sys.sql_logins WHERE name = @name
SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' WITH PASSWORD = ' + @PWD_string + ' HASHED, SID = ' + @SID_string + ', DEFAULT_DATABASE = [' + @defaultdb + ']'
IF ( @is_policy_checked IS NOT NULL )
BEGIN
SET @tmpstr = @tmpstr + ', CHECK_POLICY = ' + @is_policy_checked
END
IF ( @is_expiration_checked IS NOT NULL )
BEGIN
SET @tmpstr = @tmpstr + ', CHECK_EXPIRATION = ' + @is_expiration_checked
END
END
IF (@denylogin = 1)
BEGIN -- login is denied access
SET @tmpstr = @tmpstr + '; DENY CONNECT SQL TO ' + QUOTENAME( @name )
END
ELSE IF (@hasaccess = 0)
BEGIN -- login exists but does not have access
SET @tmpstr = @tmpstr + '; REVOKE CONNECT SQL TO ' + QUOTENAME( @name )
END
IF (@is_disabled = 1)
BEGIN -- login is disabled
SET @tmpstr = @tmpstr + '; ALTER LOGIN ' + QUOTENAME( @name ) + ' DISABLE'
END
PRINT @tmpstr
END
FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin
END
CLOSE login_curs
DEALLOCATE login_curs
-- RETURN 0
GO
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
July 26, 2010 at 11:42 pm
try:
http://support.microsoft.com/kb/246133
should sort you out...
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle
July 26, 2010 at 11:50 pm
Thanks for responding.
But I believe the above two methods are for transfering all the SQL Logins in the SQL server to another server.
I'm going to transfer a database x from Server A to B.
I just need to transfer those logins that are mapped to database x.
Any help?
Thanks
John
July 26, 2010 at 11:59 pm
this links contains many scripts , see which one can help you.
http://qa.sqlservercentral.com/search/?q=transfer+the+logins
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
July 27, 2010 at 12:30 am
You need to execute the script from the support website as given above on the server A.
Then execute the following stored procedure on the master database of Server A.
USE [master]
GO
EXEC sp_help_revlogin
The above sp will generate all the logins present on the serverA.
Browse to the database, whose logins you want to copy, expand the database and go to security.
You will get a list of users for the database. Copy the corresponding logins for the users from the output of the above sp.
Execute it on ServerB, the logins will be created there.
Here is a link for the step-by-step method.
Hope, this may help.
Thanks & Regards,
Sudeepta.
http://twitter.com/skganguly
July 27, 2010 at 12:39 am
Hi ,
If the SQL logins mapped to a Database X, then corresponding dabtase users will be created.
But if the SQL Logins mapped to this database X has the 'DEAFAULT_DATABASE' option set as 'master', then the SSIS task 'Transfer SQL Logins' cannot transfer the SQL logins (mapped to database x) to another server.
I believe so as the T-SQL methods, because we take the information from sys.server_principals.
Please can any one guide on this, because in my case the all SQL logins has a setting of 'DEFAULT_DATBASE' as 'master'
John
July 27, 2010 at 12:47 am
Thanks Sudeepta,
Actually I was looking for a straight forward TSQL method or SSIS task to pick the SQL logins for particular databases and trasfer to another database.
I believe if the DEFAULT_DATABASE option for SQL login is not set correctly accroding to the respective databases/logins, then we need to try the manual method suggested by you.
(like picking only the script for databases that are required, from the output of rev_login)
Thanks
John
July 28, 2010 at 2:52 am
$partacu$ (7/27/2010)
Hi ,If the SQL logins mapped to a Database X, then corresponding dabtase users will be created.
But if the SQL Logins mapped to this database X has the 'DEAFAULT_DATABASE' option set as 'master', then the SSIS task 'Transfer SQL Logins' cannot transfer the SQL logins (mapped to database x) to another server.
I believe so as the T-SQL methods, because we take the information from sys.server_principals.
Please can any one guide on this, because in my case the all SQL logins has a setting of 'DEFAULT_DATBASE' as 'master'
Hi John "The Default Database" being master shouldn't effect your logins.
If you want to filter the logins for a particular db using tsql you can use the sp_helprevlogin script given above and add the following join when populating the cursor.
inner join dbname.sys.database_principals d
ON ( p.name = d.name )
Eoin
July 28, 2010 at 7:22 am
You can do this from SQL script task wizard..
select users-->in advanced select script login=true
Hope this will help
July 29, 2010 at 9:47 am
Do a find/replace of YOURDBNAME in this script and that should give you what you are looking for. It's a modified version of the sp_help_revlogin for SQL Server 2005 that works with 05/08.
--Description: Scripts logins for one db
--*****IMPORTANT replace YOURDBNAME with the name of the database that holds the users that you need scripted
USE master
GO
IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL
DROP PROCEDURE sp_hexadecimal
GO
CREATE PROCEDURE sp_hexadecimal
@binvalue varbinary(256),
@hexvalue varchar (514) OUTPUT
AS
DECLARE @charvalue varchar (514)
DECLARE @i int
DECLARE @length int
DECLARE @hexstring char(16)
SELECT @charvalue = '0x'
SELECT @i = 1
SELECT @length = DATALENGTH (@binvalue)
SELECT @hexstring = '0123456789ABCDEF'
WHILE (@i <= @length)
BEGIN
DECLARE @tempint int
DECLARE @firstint int
DECLARE @secondint int
SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1))
SELECT @firstint = FLOOR(@tempint/16)
SELECT @secondint = @tempint - (@firstint*16)
SELECT @charvalue = @charvalue +
SUBSTRING(@hexstring, @firstint+1, 1) +
SUBSTRING(@hexstring, @secondint+1, 1)
SELECT @i = @i + 1
END
SELECT @hexvalue = @charvalue
GO
--DROP and CREATE sp_help_revlogin SP
IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL
DROP PROCEDURE sp_help_revlogin
GO
CREATE PROCEDURE [dbo].[sp_help_YOURDBNAME_revlogin] @login_name sysname = NULL AS
DECLARE @name sysname
DECLARE @type varchar (1)
DECLARE @hasaccess int
DECLARE @denylogin int
DECLARE @is_disabled int
DECLARE @PWD_varbinary varbinary (256)
DECLARE @PWD_string varchar (514)
DECLARE @SID_varbinary varbinary (85)
DECLARE @SID_string varchar (514)
DECLARE @tmpstr varchar (1024)
DECLARE @is_policy_checked varchar (3)
DECLARE @is_expiration_checked varchar (3)
DECLARE @defaultdb sysname
IF (@login_name IS NULL)
DECLARE login_curs CURSOR FOR
SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin
FROM sys.server_principals p
LEFT JOIN
sys.syslogins l
ON
(l.name = p.name)
JOIN
YOURDBNAME.sys.sysusers YOURDBNAME on (l.sid = YOURDBNAME.sid)
WHERE p.type IN ( 'S', 'G', 'U' )
AND
p.name <> 'sa'
ELSE
DECLARE login_curs CURSOR FOR
SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin
FROM sys.server_principals p
LEFT JOIN
sys.syslogins l
ON (l.name = p.name)
JOIN
YOURDBNAME.sys.sysusers YOURDBNAME on (l.sid = YOURDBNAME.sid)
WHERE p.type IN ( 'S', 'G', 'U' )
AND
p.name = @login_name
OPEN login_curs
FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin
IF (@@fetch_status = -1)
BEGIN
PRINT 'No login(s) found.'
CLOSE login_curs
DEALLOCATE login_curs
RETURN -1
END
SET @tmpstr = '/* sp_help_revlogin script '
PRINT @tmpstr
SET @tmpstr = '** Generated ' + CONVERT (varchar, GETDATE()) + ' on ' + @@SERVERNAME + ' */'
PRINT @tmpstr
PRINT ''
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
PRINT ''
SET @tmpstr = '-- Login: ' + @name
PRINT @tmpstr
IF (@type IN ( 'G', 'U'))
BEGIN -- NT authenticated account/group
SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' FROM WINDOWS WITH DEFAULT_DATABASE = [' + @defaultdb + ']'
END
ELSE BEGIN -- SQL Server authentication
-- obtain password and sid
SET @PWD_varbinary = CAST( LOGINPROPERTY( @name, 'PasswordHash' ) AS varbinary (256) )
EXEC sp_hexadecimal @PWD_varbinary, @PWD_string OUT
EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT
-- obtain password policy state
SELECT @is_policy_checked = CASE is_policy_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END FROM sys.sql_logins WHERE name = @name
SELECT @is_expiration_checked = CASE is_expiration_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END FROM sys.sql_logins WHERE name = @name
SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' WITH PASSWORD = ' + @PWD_string + ' HASHED, SID = ' + @SID_string + ', DEFAULT_DATABASE = [' + @defaultdb + ']'
IF ( @is_policy_checked IS NOT NULL )
BEGIN
SET @tmpstr = @tmpstr + ', CHECK_POLICY = ' + @is_policy_checked
END
IF ( @is_expiration_checked IS NOT NULL )
BEGIN
SET @tmpstr = @tmpstr + ', CHECK_EXPIRATION = ' + @is_expiration_checked
END
END
IF (@denylogin = 1)
BEGIN -- login is denied access
SET @tmpstr = @tmpstr + '; DENY CONNECT SQL TO ' + QUOTENAME( @name )
END
ELSE IF (@hasaccess = 0)
BEGIN -- login exists but does not have access
SET @tmpstr = @tmpstr + '; REVOKE CONNECT SQL TO ' + QUOTENAME( @name )
END
IF (@is_disabled = 1)
BEGIN -- login is disabled
SET @tmpstr = @tmpstr + '; ALTER LOGIN ' + QUOTENAME( @name ) + ' DISABLE'
END
PRINT @tmpstr
END
FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin
END
CLOSE login_curs
DEALLOCATE login_curs
RETURN 0
GO
--execute the SP to get the scripts
EXEC [dbo].[sp_help_YOURDBNAME_revlogin]
GO
--drop the SP after the scripts are printed (no longer necessary to keep)
DROP PROCEDURE [dbo].[sp_help_YOURDBNAME_revlogin]
GO
Best regards,
Steve
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply