SQLAgentMail: source

Showing posts with label source. Show all posts

Friday, March 23, 2012

Question on Users and Logins

Hi All,
How can we reveal the users? I was trying to do import exports from one
database to another .Source database is containing one user which is not
present in destination database . It raises an error of "login is not
available " , Is there any other way to import /export whole database with
logins and users as well .
Thanks & Regards,
SwatiThis is from Microsoft
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(256) OUTPUT
AS
DECLARE @.charvalue varchar(256)
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
IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL
DROP PROCEDURE sp_help_revlogin
GO
CREATE PROCEDURE sp_help_revlogin @.login_name sysname = NULL AS
DECLARE @.name sysname
DECLARE @.xstatus int
DECLARE @.binpwd varbinary (256)
DECLARE @.txtpwd sysname
DECLARE @.tmpstr varchar (256)
DECLARE @.SID_varbinary varbinary(85)
DECLARE @.SID_string varchar(256)
IF (@.login_name IS NULL)
DECLARE login_curs CURSOR FOR
SELECT sid, name, xstatus, password FROM master..sysxlogins
WHERE srvid IS NULL AND name <> 'sa'
ELSE
DECLARE login_curs CURSOR FOR
SELECT sid, name, xstatus, password FROM master..sysxlogins
WHERE srvid IS NULL AND name = @.login_name
OPEN login_curs
FETCH NEXT FROM login_curs INTO @.SID_varbinary, @.name, @.xstatus, @.binpwd
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 ''
PRINT 'DECLARE @.pwd sysname'
WHILE (@.@.fetch_status <> -1)
BEGIN
IF (@.@.fetch_status <> -2)
BEGIN
PRINT ''
SET @.tmpstr = '-- Login: ' + @.name
PRINT @.tmpstr
IF (@.xstatus & 4) = 4
BEGIN -- NT authenticated account/group
IF (@.xstatus & 1) = 1
BEGIN -- NT login is denied access
SET @.tmpstr = 'EXEC master..sp_denylogin ''' + @.name + ''''
PRINT @.tmpstr
END
ELSE BEGIN -- NT login has access
SET @.tmpstr = 'EXEC master..sp_grantlogin ''' + @.name + ''''
PRINT @.tmpstr
END
END
ELSE BEGIN -- SQL Server authentication
IF (@.binpwd IS NOT NULL)
BEGIN -- Non-null password
EXEC sp_hexadecimal @.binpwd, @.txtpwd OUT
IF (@.xstatus & 2048) = 2048
SET @.tmpstr = 'SET @.pwd = CONVERT (varchar(256), ' + @.txtpwd + ')'
ELSE
SET @.tmpstr = 'SET @.pwd = CONVERT (varbinary(256), ' + @.txtpwd + ')'
PRINT @.tmpstr
EXEC sp_hexadecimal @.SID_varbinary,@.SID_string OUT
SET @.tmpstr = 'EXEC master..sp_addlogin ''' + @.name
+ ''', @.pwd, @.sid = ' + @.SID_string + ', @.encryptopt = '
END
ELSE BEGIN
-- Null password
EXEC sp_hexadecimal @.SID_varbinary,@.SID_string OUT
SET @.tmpstr = 'EXEC master..sp_addlogin ''' + @.name
+ ''', NULL, @.sid = ' + @.SID_string + ', @.encryptopt = '
END
IF (@.xstatus & 2048) = 2048
-- login upgraded from 6.5
SET @.tmpstr = @.tmpstr + '''skip_encryption_old'''
ELSE
SET @.tmpstr = @.tmpstr + '''skip_encryption'''
PRINT @.tmpstr
END
END
FETCH NEXT FROM login_curs INTO @.SID_varbinary, @.name, @.xstatus, @.binpwd
END
CLOSE login_curs
DEALLOCATE login_curs
RETURN 0
GO
sp_help_revlogin
"swati" <swati.zingade@.ugamsolutions.com> wrote in message
news:ORXyI4tyEHA.3820@.TK2MSFTNGP11.phx.gbl...
> Hi All,
> How can we reveal the users? I was trying to do import exports from one
> database to another .Source database is containing one user which is not
> present in destination database . It raises an error of "login is not
> available " , Is there any other way to import /export whole database with
> logins and users as well .
>
> Thanks & Regards,
> Swati
>
>|||Hi Uri , I have tried this , but this is not solving purpose . This is
creating a logins on different database , but users which are present is not
copied to another server .
I have to expiclitely create same users on different server ..Pls suggest me
.
Regards,
Swati
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:OzF6b8tyEHA.1264@.TK2MSFTNGP12.phx.gbl...
> This is from Microsoft
> 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(256) OUTPUT
> AS
> DECLARE @.charvalue varchar(256)
> 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
> IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL
> DROP PROCEDURE sp_help_revlogin
> GO
> CREATE PROCEDURE sp_help_revlogin @.login_name sysname = NULL AS
> DECLARE @.name sysname
> DECLARE @.xstatus int
> DECLARE @.binpwd varbinary (256)
> DECLARE @.txtpwd sysname
> DECLARE @.tmpstr varchar (256)
> DECLARE @.SID_varbinary varbinary(85)
> DECLARE @.SID_string varchar(256)
> IF (@.login_name IS NULL)
> DECLARE login_curs CURSOR FOR
> SELECT sid, name, xstatus, password FROM master..sysxlogins
> WHERE srvid IS NULL AND name <> 'sa'
> ELSE
> DECLARE login_curs CURSOR FOR
> SELECT sid, name, xstatus, password FROM master..sysxlogins
> WHERE srvid IS NULL AND name = @.login_name
> OPEN login_curs
> FETCH NEXT FROM login_curs INTO @.SID_varbinary, @.name, @.xstatus, @.binpwd
> 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 ''
> PRINT 'DECLARE @.pwd sysname'
> WHILE (@.@.fetch_status <> -1)
> BEGIN
> IF (@.@.fetch_status <> -2)
> BEGIN
> PRINT ''
> SET @.tmpstr = '-- Login: ' + @.name
> PRINT @.tmpstr
> IF (@.xstatus & 4) = 4
> BEGIN -- NT authenticated account/group
> IF (@.xstatus & 1) = 1
> BEGIN -- NT login is denied access
> SET @.tmpstr = 'EXEC master..sp_denylogin ''' + @.name + ''''
> PRINT @.tmpstr
> END
> ELSE BEGIN -- NT login has access
> SET @.tmpstr = 'EXEC master..sp_grantlogin ''' + @.name + ''''
> PRINT @.tmpstr
> END
> END
> ELSE BEGIN -- SQL Server authentication
> IF (@.binpwd IS NOT NULL)
> BEGIN -- Non-null password
> EXEC sp_hexadecimal @.binpwd, @.txtpwd OUT
> IF (@.xstatus & 2048) = 2048
> SET @.tmpstr = 'SET @.pwd = CONVERT (varchar(256), ' + @.txtpwd + ')'
> ELSE
> SET @.tmpstr = 'SET @.pwd = CONVERT (varbinary(256), ' + @.txtpwd + ')'
> PRINT @.tmpstr
> EXEC sp_hexadecimal @.SID_varbinary,@.SID_string OUT
> SET @.tmpstr = 'EXEC master..sp_addlogin ''' + @.name
> + ''', @.pwd, @.sid = ' + @.SID_string + ', @.encryptopt = '
> END
> ELSE BEGIN
> -- Null password
> EXEC sp_hexadecimal @.SID_varbinary,@.SID_string OUT
> SET @.tmpstr = 'EXEC master..sp_addlogin ''' + @.name
> + ''', NULL, @.sid = ' + @.SID_string + ', @.encryptopt = '
> END
> IF (@.xstatus & 2048) = 2048
> -- login upgraded from 6.5
> SET @.tmpstr = @.tmpstr + '''skip_encryption_old'''
> ELSE
> SET @.tmpstr = @.tmpstr + '''skip_encryption'''
> PRINT @.tmpstr
> END
> END
> FETCH NEXT FROM login_curs INTO @.SID_varbinary, @.name, @.xstatus, @.binpwd
> END
> CLOSE login_curs
> DEALLOCATE login_curs
> RETURN 0
> GO
> sp_help_revlogin
>
>
> "swati" <swati.zingade@.ugamsolutions.com> wrote in message
> news:ORXyI4tyEHA.3820@.TK2MSFTNGP11.phx.gbl...
> > Hi All,
> >
> > How can we reveal the users? I was trying to do import exports from one
> > database to another .Source database is containing one user which is not
> > present in destination database . It raises an error of "login is not
> > available " , Is there any other way to import /export whole database
with
> > logins and users as well .
> >
> >
> > Thanks & Regards,
> > Swati
> >
> >
> >
> >
>|||swati
All database users are stored in sysuser system table.
By using RESTORE DATABASE command you are moved the users of the database.
and then run these sp from the article.
http://support.microsoft.com/default.aspx?scid=kb;en-us;246133#4
"swati" <swati.zingade@.ugamsolutions.com> wrote in message
news:uuw1GFuyEHA.2752@.TK2MSFTNGP11.phx.gbl...
> Hi Uri , I have tried this , but this is not solving purpose . This is
> creating a logins on different database , but users which are present is
not
> copied to another server .
> I have to expiclitely create same users on different server ..Pls suggest
me
> .
> Regards,
> Swati
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:OzF6b8tyEHA.1264@.TK2MSFTNGP12.phx.gbl...
> > This is from Microsoft
> >
> > 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(256) OUTPUT
> > AS
> > DECLARE @.charvalue varchar(256)
> > 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
> >
> > IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL
> > DROP PROCEDURE sp_help_revlogin
> > GO
> > CREATE PROCEDURE sp_help_revlogin @.login_name sysname = NULL AS
> > DECLARE @.name sysname
> > DECLARE @.xstatus int
> > DECLARE @.binpwd varbinary (256)
> > DECLARE @.txtpwd sysname
> > DECLARE @.tmpstr varchar (256)
> > DECLARE @.SID_varbinary varbinary(85)
> > DECLARE @.SID_string varchar(256)
> >
> > IF (@.login_name IS NULL)
> > DECLARE login_curs CURSOR FOR
> > SELECT sid, name, xstatus, password FROM master..sysxlogins
> > WHERE srvid IS NULL AND name <> 'sa'
> > ELSE
> > DECLARE login_curs CURSOR FOR
> > SELECT sid, name, xstatus, password FROM master..sysxlogins
> > WHERE srvid IS NULL AND name = @.login_name
> > OPEN login_curs
> > FETCH NEXT FROM login_curs INTO @.SID_varbinary, @.name, @.xstatus, @.binpwd
> > 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 ''
> > PRINT 'DECLARE @.pwd sysname'
> > WHILE (@.@.fetch_status <> -1)
> > BEGIN
> > IF (@.@.fetch_status <> -2)
> > BEGIN
> > PRINT ''
> > SET @.tmpstr = '-- Login: ' + @.name
> > PRINT @.tmpstr
> > IF (@.xstatus & 4) = 4
> > BEGIN -- NT authenticated account/group
> > IF (@.xstatus & 1) = 1
> > BEGIN -- NT login is denied access
> > SET @.tmpstr = 'EXEC master..sp_denylogin ''' + @.name + ''''
> > PRINT @.tmpstr
> > END
> > ELSE BEGIN -- NT login has access
> > SET @.tmpstr = 'EXEC master..sp_grantlogin ''' + @.name + ''''
> > PRINT @.tmpstr
> > END
> > END
> > ELSE BEGIN -- SQL Server authentication
> > IF (@.binpwd IS NOT NULL)
> > BEGIN -- Non-null password
> > EXEC sp_hexadecimal @.binpwd, @.txtpwd OUT
> > IF (@.xstatus & 2048) = 2048
> > SET @.tmpstr = 'SET @.pwd = CONVERT (varchar(256), ' + @.txtpwd + ')'
> > ELSE
> > SET @.tmpstr = 'SET @.pwd = CONVERT (varbinary(256), ' + @.txtpwd + ')'
> > PRINT @.tmpstr
> > EXEC sp_hexadecimal @.SID_varbinary,@.SID_string OUT
> > SET @.tmpstr = 'EXEC master..sp_addlogin ''' + @.name
> > + ''', @.pwd, @.sid = ' + @.SID_string + ', @.encryptopt = '
> > END
> > ELSE BEGIN
> > -- Null password
> > EXEC sp_hexadecimal @.SID_varbinary,@.SID_string OUT
> > SET @.tmpstr = 'EXEC master..sp_addlogin ''' + @.name
> > + ''', NULL, @.sid = ' + @.SID_string + ', @.encryptopt = '
> > END
> > IF (@.xstatus & 2048) = 2048
> > -- login upgraded from 6.5
> > SET @.tmpstr = @.tmpstr + '''skip_encryption_old'''
> > ELSE
> > SET @.tmpstr = @.tmpstr + '''skip_encryption'''
> > PRINT @.tmpstr
> > END
> > END
> > FETCH NEXT FROM login_curs INTO @.SID_varbinary, @.name, @.xstatus, @.binpwd
> > END
> > CLOSE login_curs
> > DEALLOCATE login_curs
> > RETURN 0
> > GO
> >
> > sp_help_revlogin
> >
> >
> >
> >
> > "swati" <swati.zingade@.ugamsolutions.com> wrote in message
> > news:ORXyI4tyEHA.3820@.TK2MSFTNGP11.phx.gbl...
> > > Hi All,
> > >
> > > How can we reveal the users? I was trying to do import exports from
one
> > > database to another .Source database is containing one user which is
not
> > > present in destination database . It raises an error of "login is not
> > > available " , Is there any other way to import /export whole database
> with
> > > logins and users as well .
> > >
> > >
> > > Thanks & Regards,
> > > Swati
> > >
> > >
> > >
> > >
> >
> >
>|||Hi Swati,
To transfer users between servers you can use DTS.
Open a new DTS package and click on Transfer Login task, it will ask you
source and destination server. Once the Logins are transferred to you new
server you need to add those logins to your taget database. If the database
is restored then you will be having these users. If you dont see these users
in the database then script the source database. Right Click -- > All Tasks
--> Generate Sql Script --> options Tab--> Check ,script sql server logins.
Now run this sql script on your target database. You will get all the users
to your target database.
To add login to a database it needs to be present in sql server, so run the
DTS to transfer Logins to target server first if your source database and
target database are on different servers.
Regards,
Arshad
"Uri Dimant" wrote:
> swati
> All database users are stored in sysuser system table.
> By using RESTORE DATABASE command you are moved the users of the database.
> and then run these sp from the article.
> http://support.microsoft.com/default.aspx?scid=kb;en-us;246133#4
>
>
>
> "swati" <swati.zingade@.ugamsolutions.com> wrote in message
> news:uuw1GFuyEHA.2752@.TK2MSFTNGP11.phx.gbl...
> > Hi Uri , I have tried this , but this is not solving purpose . This is
> > creating a logins on different database , but users which are present is
> not
> > copied to another server .
> > I have to expiclitely create same users on different server ..Pls suggest
> me
> > .
> >
> > Regards,
> > Swati
> >
> >
> > "Uri Dimant" <urid@.iscar.co.il> wrote in message
> > news:OzF6b8tyEHA.1264@.TK2MSFTNGP12.phx.gbl...
> > > This is from Microsoft
> > >
> > > 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(256) OUTPUT
> > > AS
> > > DECLARE @.charvalue varchar(256)
> > > 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
> > >
> > > IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL
> > > DROP PROCEDURE sp_help_revlogin
> > > GO
> > > CREATE PROCEDURE sp_help_revlogin @.login_name sysname = NULL AS
> > > DECLARE @.name sysname
> > > DECLARE @.xstatus int
> > > DECLARE @.binpwd varbinary (256)
> > > DECLARE @.txtpwd sysname
> > > DECLARE @.tmpstr varchar (256)
> > > DECLARE @.SID_varbinary varbinary(85)
> > > DECLARE @.SID_string varchar(256)
> > >
> > > IF (@.login_name IS NULL)
> > > DECLARE login_curs CURSOR FOR
> > > SELECT sid, name, xstatus, password FROM master..sysxlogins
> > > WHERE srvid IS NULL AND name <> 'sa'
> > > ELSE
> > > DECLARE login_curs CURSOR FOR
> > > SELECT sid, name, xstatus, password FROM master..sysxlogins
> > > WHERE srvid IS NULL AND name = @.login_name
> > > OPEN login_curs
> > > FETCH NEXT FROM login_curs INTO @.SID_varbinary, @.name, @.xstatus, @.binpwd
> > > 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 ''
> > > PRINT 'DECLARE @.pwd sysname'
> > > WHILE (@.@.fetch_status <> -1)
> > > BEGIN
> > > IF (@.@.fetch_status <> -2)
> > > BEGIN
> > > PRINT ''
> > > SET @.tmpstr = '-- Login: ' + @.name
> > > PRINT @.tmpstr
> > > IF (@.xstatus & 4) = 4
> > > BEGIN -- NT authenticated account/group
> > > IF (@.xstatus & 1) = 1
> > > BEGIN -- NT login is denied access
> > > SET @.tmpstr = 'EXEC master..sp_denylogin ''' + @.name + ''''
> > > PRINT @.tmpstr
> > > END
> > > ELSE BEGIN -- NT login has access
> > > SET @.tmpstr = 'EXEC master..sp_grantlogin ''' + @.name + ''''
> > > PRINT @.tmpstr
> > > END
> > > END
> > > ELSE BEGIN -- SQL Server authentication
> > > IF (@.binpwd IS NOT NULL)
> > > BEGIN -- Non-null password
> > > EXEC sp_hexadecimal @.binpwd, @.txtpwd OUT
> > > IF (@.xstatus & 2048) = 2048
> > > SET @.tmpstr = 'SET @.pwd = CONVERT (varchar(256), ' + @.txtpwd + ')'
> > > ELSE
> > > SET @.tmpstr = 'SET @.pwd = CONVERT (varbinary(256), ' + @.txtpwd + ')'
> > > PRINT @.tmpstr
> > > EXEC sp_hexadecimal @.SID_varbinary,@.SID_string OUT
> > > SET @.tmpstr = 'EXEC master..sp_addlogin ''' + @.name
> > > + ''', @.pwd, @.sid = ' + @.SID_string + ', @.encryptopt = '
> > > END
> > > ELSE BEGIN
> > > -- Null password
> > > EXEC sp_hexadecimal @.SID_varbinary,@.SID_string OUT
> > > SET @.tmpstr = 'EXEC master..sp_addlogin ''' + @.name
> > > + ''', NULL, @.sid = ' + @.SID_string + ', @.encryptopt = '
> > > END
> > > IF (@.xstatus & 2048) = 2048
> > > -- login upgraded from 6.5
> > > SET @.tmpstr = @.tmpstr + '''skip_encryption_old'''
> > > ELSE
> > > SET @.tmpstr = @.tmpstr + '''skip_encryption'''
> > > PRINT @.tmpstr
> > > END
> > > END
> > > FETCH NEXT FROM login_curs INTO @.SID_varbinary, @.name, @.xstatus, @.binpwd
> > > END
> > > CLOSE login_curs
> > > DEALLOCATE login_curs
> > > RETURN 0
> > > GO
> > >
> > > sp_help_revlogin
> > >
> > >
> > >
> > >
> > > "swati" <swati.zingade@.ugamsolutions.com> wrote in message
> > > news:ORXyI4tyEHA.3820@.TK2MSFTNGP11.phx.gbl...
> > > > Hi All,
> > > >
> > > > How can we reveal the users? I was trying to do import exports from
> one
> > > > database to another .Source database is containing one user which is
> not
> > > > present in destination database . It raises an error of "login is not
> > > > available " , Is there any other way to import /export whole database
> > with
> > > > logins and users as well .
> > > >
> > > >
> > > > Thanks & Regards,
> > > > Swati
> > > >
> > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>

Question on Tranaction Isolation Level.

Source:
SQL2K
SP3
Destination:
SQL2K5
SP2
I have requirements to import data from the source to the destination, and
once on the destination the data will be read only. I also have the
situation where the data owners (not me) may make schema changes on the
source whenever they want to, and do not need to tell me. Most of the time
this would only be widening an existing column, so I'm not all that worried.
This data is only to be grabbed once a night during a slow period.
Options:
Replication: This is out because the data owner will want to widen a column
without needing to consult with me first (I would need to drop replication
first). I'm aware of the changes to 2005 replication, but they won't be
upgrading anytime soon.
DTS/ SSIS: This is out because they will not want to tell me if they widen a
column. Just because they widen the source, that doesn't help my
destination.
Because of all these requirements/ circumstances, I want to just create a
Linked Server and do a Select...Into every night and drop/ re-create the
table on the destination. This will make schema changes on the source
transparent to me. Of course though, I don't want my Select...Into to block
users on the source while the import is occurring. That being said, I was
thinking about setting the Transaction Isolation Level (TIL) to Read
Uncommitted (RU) in the Select...Into. I don't really care if the
consistency is off a bit as it's only updated once a day anyways. This is
how it would look:
Set Transaction Isolation Level Read Uncommitted
select * into T48
from myLinkedServer.database.dbo.T48
So my question is: Will setting the TIL to RU like this actually set it for
the source (the Linked Server) or the destination (my server)? If it does in
fact set it for the source, would NOLOCK work?
TIA, ChrisR1) Can't you use SSIS and force it to drop/create tables each night? That
would probably be most efficient mechanism.
2) You pick up performance if you set the database to ReadOnly when you are
done moving data.
3) I don't believe the set (or other hints) will translate over to the
linked server.
"ChrisR" <ChrisR@.foo.com> wrote in message
news:Oet8c6dAIHA.1204@.TK2MSFTNGP03.phx.gbl...
> Source:
> SQL2K
> SP3
> Destination:
> SQL2K5
> SP2
> I have requirements to import data from the source to the destination, and
> once on the destination the data will be read only. I also have the
> situation where the data owners (not me) may make schema changes on the
> source whenever they want to, and do not need to tell me. Most of the time
> this would only be widening an existing column, so I'm not all that
> worried. This data is only to be grabbed once a night during a slow
> period.
> Options:
> Replication: This is out because the data owner will want to widen a
> column without needing to consult with me first (I would need to drop
> replication first). I'm aware of the changes to 2005 replication, but they
> won't be upgrading anytime soon.
> DTS/ SSIS: This is out because they will not want to tell me if they widen
> a column. Just because they widen the source, that doesn't help my
> destination.
>
> Because of all these requirements/ circumstances, I want to just create a
> Linked Server and do a Select...Into every night and drop/ re-create the
> table on the destination. This will make schema changes on the source
> transparent to me. Of course though, I don't want my Select...Into to
> block users on the source while the import is occurring. That being said,
> I was thinking about setting the Transaction Isolation Level (TIL) to Read
> Uncommitted (RU) in the Select...Into. I don't really care if the
> consistency is off a bit as it's only updated once a day anyways. This is
> how it would look:
> Set Transaction Isolation Level Read Uncommitted
> select * into T48
> from myLinkedServer.database.dbo.T48
> So my question is: Will setting the TIL to RU like this actually set it
> for the source (the Linked Server) or the destination (my server)? If it
> does in fact set it for the source, would NOLOCK work?
> TIA, ChrisR
>|||1) I don't think SSIS will widen the columns on the destination
automatically if they have been widened on the source (perhaps it can
somehow as I don't have a lot of SSIS experience?). Select...Into will take
care of this for me. Sure I can bury my Select...Into code in a SSIS
Package, but I don't think that's what you were getting at.
2) Not really an option, but thanks.
3) This is what I was afraid of.
Thanks!
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:13fq948dgolb0a5@.corp.supernews.com...
> 1) Can't you use SSIS and force it to drop/create tables each night? That
> would probably be most efficient mechanism.
> 2) You pick up performance if you set the database to ReadOnly when you
> are done moving data.
> 3) I don't believe the set (or other hints) will translate over to the
> linked server.
>
> "ChrisR" <ChrisR@.foo.com> wrote in message
> news:Oet8c6dAIHA.1204@.TK2MSFTNGP03.phx.gbl...
>> Source:
>> SQL2K
>> SP3
>> Destination:
>> SQL2K5
>> SP2
>> I have requirements to import data from the source to the destination,
>> and once on the destination the data will be read only. I also have the
>> situation where the data owners (not me) may make schema changes on the
>> source whenever they want to, and do not need to tell me. Most of the
>> time this would only be widening an existing column, so I'm not all that
>> worried. This data is only to be grabbed once a night during a slow
>> period.
>> Options:
>> Replication: This is out because the data owner will want to widen a
>> column without needing to consult with me first (I would need to drop
>> replication first). I'm aware of the changes to 2005 replication, but
>> they won't be upgrading anytime soon.
>> DTS/ SSIS: This is out because they will not want to tell me if they
>> widen a column. Just because they widen the source, that doesn't help my
>> destination.
>>
>> Because of all these requirements/ circumstances, I want to just create a
>> Linked Server and do a Select...Into every night and drop/ re-create the
>> table on the destination. This will make schema changes on the source
>> transparent to me. Of course though, I don't want my Select...Into to
>> block users on the source while the import is occurring. That being said,
>> I was thinking about setting the Transaction Isolation Level (TIL) to
>> Read Uncommitted (RU) in the Select...Into. I don't really care if the
>> consistency is off a bit as it's only updated once a day anyways. This is
>> how it would look:
>> Set Transaction Isolation Level Read Uncommitted
>> select * into T48
>> from myLinkedServer.database.dbo.T48
>> So my question is: Will setting the TIL to RU like this actually set it
>> for the source (the Linked Server) or the destination (my server)? If it
>> does in fact set it for the source, would NOLOCK work?
>> TIA, ChrisR
>|||One other approach would be to import into a permanent staging table -
perhaps in a staging database - that matches your data SOURCE which I
take it is not changing. Then use SQL to INSERT to the target table
from staging.
Roy Harvey
Beacon Falls, CT
On Fri, 28 Sep 2007 07:49:13 -0700, "ChrisR" <ChrisR@.foo.com> wrote:
>Source:
>SQL2K
>SP3
>Destination:
>SQL2K5
>SP2
>I have requirements to import data from the source to the destination, and
>once on the destination the data will be read only. I also have the
>situation where the data owners (not me) may make schema changes on the
>source whenever they want to, and do not need to tell me. Most of the time
>this would only be widening an existing column, so I'm not all that worried.
>This data is only to be grabbed once a night during a slow period.
>Options:
>Replication: This is out because the data owner will want to widen a column
>without needing to consult with me first (I would need to drop replication
>first). I'm aware of the changes to 2005 replication, but they won't be
>upgrading anytime soon.
>DTS/ SSIS: This is out because they will not want to tell me if they widen a
>column. Just because they widen the source, that doesn't help my
>destination.
>
>Because of all these requirements/ circumstances, I want to just create a
>Linked Server and do a Select...Into every night and drop/ re-create the
>table on the destination. This will make schema changes on the source
>transparent to me. Of course though, I don't want my Select...Into to block
>users on the source while the import is occurring. That being said, I was
>thinking about setting the Transaction Isolation Level (TIL) to Read
>Uncommitted (RU) in the Select...Into. I don't really care if the
>consistency is off a bit as it's only updated once a day anyways. This is
>how it would look:
>Set Transaction Isolation Level Read Uncommitted
>select * into T48
>from myLinkedServer.database.dbo.T48
>So my question is: Will setting the TIL to RU like this actually set it for
>the source (the Linked Server) or the destination (my server)? If it does in
>fact set it for the source, would NOLOCK work?
>TIA, ChrisR
>|||I think what you are saying is to first do a Select...Into a local table as
opposed to directly into the Linked Server? If so, I was already thinking
the same thing. If not, could you please elaborate?
"Roy Harvey (SQL Server MVP)" <roy_harvey@.snet.net> wrote in message
news:jvjqf39vh2kut6smef1rg2dj33crnkstij@.4ax.com...
> One other approach would be to import into a permanent staging table -
> perhaps in a staging database - that matches your data SOURCE which I
> take it is not changing. Then use SQL to INSERT to the target table
> from staging.
> Roy Harvey
> Beacon Falls, CT
> On Fri, 28 Sep 2007 07:49:13 -0700, "ChrisR" <ChrisR@.foo.com> wrote:
>>Source:
>>SQL2K
>>SP3
>>Destination:
>>SQL2K5
>>SP2
>>I have requirements to import data from the source to the destination, and
>>once on the destination the data will be read only. I also have the
>>situation where the data owners (not me) may make schema changes on the
>>source whenever they want to, and do not need to tell me. Most of the time
>>this would only be widening an existing column, so I'm not all that
>>worried.
>>This data is only to be grabbed once a night during a slow period.
>>Options:
>>Replication: This is out because the data owner will want to widen a
>>column
>>without needing to consult with me first (I would need to drop replication
>>first). I'm aware of the changes to 2005 replication, but they won't be
>>upgrading anytime soon.
>>DTS/ SSIS: This is out because they will not want to tell me if they widen
>>a
>>column. Just because they widen the source, that doesn't help my
>>destination.
>>
>>Because of all these requirements/ circumstances, I want to just create a
>>Linked Server and do a Select...Into every night and drop/ re-create the
>>table on the destination. This will make schema changes on the source
>>transparent to me. Of course though, I don't want my Select...Into to
>>block
>>users on the source while the import is occurring. That being said, I was
>>thinking about setting the Transaction Isolation Level (TIL) to Read
>>Uncommitted (RU) in the Select...Into. I don't really care if the
>>consistency is off a bit as it's only updated once a day anyways. This is
>>how it would look:
>>Set Transaction Isolation Level Read Uncommitted
>>select * into T48
>>from myLinkedServer.database.dbo.T48
>>So my question is: Will setting the TIL to RU like this actually set it
>>for
>>the source (the Linked Server) or the destination (my server)? If it does
>>in
>>fact set it for the source, would NOLOCK work?
>>TIA, ChrisR|||On Fri, 28 Sep 2007 13:03:23 -0700, "ChrisR" <ChrisR@.foo.com> wrote:
>I think what you are saying is to first do a Select...Into a local table as
>opposed to directly into the Linked Server? If so, I was already thinking
>the same thing. If not, could you please elaborate?
You have a source table somewhere on another server, call it Source.
You have a target table on the target server, call it Target. Target
is subject to change, but Source is not. Create a permanent table
Target_Imported, on the target server. Put it in the same database as
Target, or in a staging database. Make the columns match the Target
table in name and order, but make the column data types match the
related column in Source. Since the types match importing from Source
into Target_Import using BCP, DTS, SSIS or even INSERT/SELECT from a
linked server should be straight forward. Because the column names
match, copying the data between Target_Imported and Target requires a
simple INSERT/SELECT. You could add WITH RECOMPILE to the stored
procedure that does the copy so that the sort of minor datatype
changes you describe are handled without intervention.
Hope that is clearer. I don't know that it is any better than what
you proposed, just another idea.
Roy Harvey
Beacon Falls, CT|||Thanks, but you have it backwards. The source can change at any time,
without any notice. Therefore, if the source changes, I want the target to
change as well. Hence, Select...Into.
Thanks again!
"Roy Harvey (SQL Server MVP)" <roy_harvey@.snet.net> wrote in message
news:c2oqf3p37k0ieg5n6j34120j662a0e319a@.4ax.com...
> On Fri, 28 Sep 2007 13:03:23 -0700, "ChrisR" <ChrisR@.foo.com> wrote:
>>I think what you are saying is to first do a Select...Into a local table
>>as
>>opposed to directly into the Linked Server? If so, I was already thinking
>>the same thing. If not, could you please elaborate?
> You have a source table somewhere on another server, call it Source.
> You have a target table on the target server, call it Target. Target
> is subject to change, but Source is not. Create a permanent table
> Target_Imported, on the target server. Put it in the same database as
> Target, or in a staging database. Make the columns match the Target
> table in name and order, but make the column data types match the
> related column in Source. Since the types match importing from Source
> into Target_Import using BCP, DTS, SSIS or even INSERT/SELECT from a
> linked server should be straight forward. Because the column names
> match, copying the data between Target_Imported and Target requires a
> simple INSERT/SELECT. You could add WITH RECOMPILE to the stored
> procedure that does the copy so that the sort of minor datatype
> changes you describe are handled without intervention.
> Hope that is clearer. I don't know that it is any better than what
> you proposed, just another idea.
> Roy Harvey
> Beacon Falls, CT

Question on the Dimension Wizard

Hi, all,

I have a questions on the dimension wizard.

Question 1: Main table: the data source table where we want to derive the new dimension data from?

Key columns: the keys columns which are the key of the main table above?

Column containing the member name Here I am confused,), as when we choose composite keys for the main table above, we will have to choose the column containing the member name? What is this column for? And what does it really mean? This column containing what member name?

Please any of you here give me any advices on this and I am looking forward to hearing from you shortly and thanks a lot in advance.

With best regards,

Yours sincerely,

The key columns uniquely identify each member of an attribute. This is different from the key columns of the table which uniquely identify each row of the table. As an example, if you had a column containing Month names with 12 values and another column containing years with 5 values, but you wanted an attribute that contained individual members for every month of every year, 60 values, then you would use both Month and Year as the key columns of your attribute. Now the name column determines what each attribute will be called. In this example, you could use Month as the name column, but you won't be able to tell your January of 2006 from your January of 2007 (unless you just use this attribute in a hierarchy with Year above it.) Here you'd probably want another column - likely a named calculation which contactinates month and year - as your name column.

You might want to try the Analysis Services 2005 Tutorial included with the product to help with some concepts if you have further problems with this.

|||

Hi, Matt,

Thank you very much for your kind advices.

I now have a clearer understanding of it. Thanks again.

With best regards,

Yours sincerely,

|||

Hi, Matt,

Thank you very much for your kind advices.

But I am still a bit confused as key columns are the columns which relate the new derived dimension to the fact table (where this new derived dimension table from), therefore these key columns will be the dimension granularity of the usage of the new derived dimension to the fact table. But the fact table has composite keys consisting of different FKs of different tables, in this case, the name column will be the column naming the above composite keys? Actually in fact table, there is no such columns yet, therefore I will have to go to the data source view to create a such new name column for the above key columns which make up of the composite keys of the fact table?

Hope my question is clear and please give me further advices on this issue.

I am looking forward to hearing from you shortly and thanks a lot in advance.

With best regards,

Yours sincerely,

|||The key columns of the key attribute of the dimension uniquely identify each member of the attribute witht he lowest granularity in the dimension. Although it is common, it is not necessary that these key columns correspond to the relational key of the table which uniquely identifies each row in the table. It sounds like you have a table which contains both fact information and some denormalized dimensional information. In cases such as this where tables are not normalized, it is common to create dimension keys which are not the same as the table key. For example, if you had a table with sales information and it also contained the columns EmployeeNumber, EmployeeName, EmployeeDepartment, then you would probably want to create an employee dimension whose key attribute's key column was EmployeeNumber. Note that the table containing EmployeeNumber might not even have any key as it is common for fact type tables to allow duplicate transaction entries. (So a Customer would be allowed to buy the same product from the same employee on the same day, even if there is no notion of transaction ID.)|||

Hi,

Thank you for your advices. The schema of my fact table contains transactional facts, and FKs from its related dimensional tables. E.g

FK1

FK2

FK3

TransactionID

.......

Thanks again.

With best regards,

Yours sincerely,

|||

Hi, Matt,

Please ignore my previous post, I have figured out the problem.

Thanks again.

With best regards,

Yours sincerely,

Saturday, February 25, 2012

Question on data source and data source view of MOLAP cube

Hi, all experts here,

Thank you very much for your kind attention.

I am having a question on the data source and data source view of MOLAP Cube. As we know MOLAP Cube stores all data in MOLAP format, it does not have any thing to do with its source data base. As a result, I am wondering if we could actually remove the data source and data source view of the MOLAP cube? (in my case, no update is necessary to be informed from the underlying data source to the MOLAP cube)

Hope it is clear for your help.

I am looking forward to hearing from you for your advices and help.

With best regards,

Yours sincerely,

Hello again Helen.

You will always need a data source and a data source view for the cube.

What you can do, in order to create an off-line cube is to use local cubes, if this is the scenario you are thinking about?

With local cubes you create a local cube file that you can put on a laptop and analyze data without being connected to SSAS2005.

Both ProClarity and Excel2007 have this option.

HTH

Thomas Ivarsson

|||

Hi, Thomas,

Thank you for your very kind advices.

But I found that change of the data source view does not really reflect on the data source view of the cube though? I mean after I created the cube, and I edited the data source view, but when I clicked on the cube, its data source view remained as the one before I changed to the new data source view?

Also, I am a bit confused, as all the data for the cube has already stored in SSAS2005 Data base (MOLAP), so why we still need to care about the data source and data source view if we dont need to have any update from the underlying data source?

Thank you for your kind attention and I am looking forward to hearing from you shortly for your further advices.

With best regards,

Yours sincerely,

|||

Hi, Helen.

I am not sure about the scenario here? Do you mean that you will create the cube once and never process it again?

If new dimension records and new fact records arrives from the data source you will always need to process the cube. If you need to process you will need to have the data source view.

During a days work new transactions will be registered in the source system. You will have to do a new process of the cube in order to get them from the source system into the cube.

Are you making cubes for laptop users that they will use off-line? Or are you building cubes using real time OLAP?

Regards

Thomas Ivarsson

|||

Hi, Thomas,

Thank you for your reply. In my case, well, I do deploy the whole project containing the cube after I changed the data source and data source view, but the cube remains the same as before I did any change. Therefore, I thought data source and data source view does not have anything to do with the MOLAP cube after its first deployment to the analysis services server.

Does it work in this way? Or I miss out anything of it? Thank you and your advices are most welcome.

With best regards,

Yours sincerely,

|||

Hello Helen. If you make a change like adding an attribute in the data source (source table/view) or the data source view(=named calculation) this change will not be a part of the cube until you add the attribute in the dimension editor.

In order to make a change in a cube you must mapp new columns to the measure group or to the dimension in the cube/dimension editor.

The data source view do not automatically update the cube or the dimensions. If you do a refresh in the data source view(right click) all changes in the source database will exported to the data source view.

HTH

Thomas Ivarsson

|||

Hi, Thomas,

Thanks a lot for your kind advices.

It cleared my confusion over the above question.

Thanks again.

With best regards,

Yours sincerely,

Question on data source and data source view of MOLAP cube

Hi, all experts here,

Thank you very much for your kind attention.

Hope it is clear for your help.

I am looking forward to hearing from you for your advices and help.

With best regards,

Yours sincerely,

Hello again Helen.

You will always need a data source and a data source view for the cube.

What you can do, in order to create an off-line cube is to use local cubes, if this is the scenario you are thinking about?

With local cubes you create a local cube file that you can put on a laptop and analyze data without being connected to SSAS2005.

Both ProClarity and Excel2007 have this option.

HTH

Thomas Ivarsson

|||

Hi, Thomas,

Thank you for your very kind advices.

Thank you for your kind attention and I am looking forward to hearing from you shortly for your further advices.

With best regards,

Yours sincerely,

|||

Hi, Helen.

I am not sure about the scenario here? Do you mean that you will create the cube once and never process it again?

If new dimension records and new fact records arrives from the data source you will always need to process the cube. If you need to process you will need to have the data source view.

During a days work new transactions will be registered in the source system. You will have to do a new process of the cube in order to get them from the source system into the cube.

Are you making cubes for laptop users that they will use off-line? Or are you building cubes using real time OLAP?

Regards

Thomas Ivarsson

|||

Hi, Thomas,

Does it work in this way? Or I miss out anything of it? Thank you and your advices are most welcome.

With best regards,

Yours sincerely,

|||

In order to make a change in a cube you must mapp new columns to the measure group or to the dimension in the cube/dimension editor.

HTH

Thomas Ivarsson

|||

Hi, Thomas,

Thanks a lot for your kind advices.

It cleared my confusion over the above question.

Thanks again.

With best regards,

Yours sincerely,

Friday, March 23, 2012

Question on Users and Logins

Question on Users and Logins

Question on Users and Logins

Question on Tranaction Isolation Level.

Question on the Dimension Wizard

Saturday, February 25, 2012

Question on data source and data source view of MOLAP cube

Question on data source and data source view of MOLAP cube

SQLAgentMail

Blog Archive

About Me