SQLAgentMail: users

Showing posts with label users. Show all posts

Wednesday, March 28, 2012

Question related to Analysis Service Roles

Hi,

One of my users is supposed to have full access to all other dimesions except for one and thus I have defined a customized role on one of 4 dimensions that I have.

When I test the role (from the front-end), I am getting a strange message in my front-end :confused: and could it be that I also have to define a role for this user on all other dimensions? Should I define the role on the server or the client :confused: I am a bit confused and will appreciate your help.

Thanks.Are you testing the role from your machine or from the users machine? What happens when they log on to your machine? Can you post the error message that you are getting?|||Are you testing the role from your machine or from the users machine? What happens when they log on to your machine? Can you post the error message that you are getting?
Hi and thanks for the reply.

At the moment I am testing this from my own machine by logging in (from my machine) as the test user and then trying to use the cube. Could this be the reason? The front-end that I'm using is MSDA (Microsoft Data Analyzer) and the error message that I get is as follows:

Formula error - cannot bind: unknown member: "[Product].[_MAXIMAL_MIN_]"

Any clues please.sql

Monday, March 26, 2012

Question regarding "New Role Assignment"

Hi All,
I have share point server and report server on two different machines but in
the same domain. Our application categorizes users ,from Active Directory, in
Sharepoint sitegroups. For eg Marketing , Finance , Manager are sitegroups in
our application.
Is it possible to add these sitegroups in "New Role Assignment" for a given
report so that each member of the sitegroup can now access to the given
report?
Thanks in advance for any assistance provided.
KunjalTry using domain\Marketing
"Kunjal" wrote:
> Hi All,
> I have share point server and report server on two different machines but in
> the same domain. Our application categorizes users ,from Active Directory, in
> Sharepoint sitegroups. For eg Marketing , Finance , Manager are sitegroups in
> our application.
> Is it possible to add these sitegroups in "New Role Assignment" for a given
> report so that each member of the sitegroup can now access to the given
> report?
> Thanks in advance for any assistance provided.
> Kunjal
>|||I'm not sure if you can use SharePoint Sitegroups in New Role Assignment, as
they are probably SharePoint-only.
But you can use AD groups in Reporting Services, like Dillig says. If you
base both SharePoint Sitegroups and your Reporting Services Roles on the
same AD groups, you should be fine. Add <domainname>\<groupname> to the
roles you need to add them to.
Kaisa M. Lindahl
"Kunjal" <Kunjal@.discussions.microsoft.com> wrote in message
news:45CF5AC6-8DCA-4283-B0C5-1A136A77D12E@.microsoft.com...
> Hi All,
> I have share point server and report server on two different machines but
> in
> the same domain. Our application categorizes users ,from Active Directory,
> in
> Sharepoint sitegroups. For eg Marketing , Finance , Manager are sitegroups
> in
> our application.
> Is it possible to add these sitegroups in "New Role Assignment" for a
> given
> report so that each member of the sitegroup can now access to the given
> report?
> Thanks in advance for any assistance provided.
> Kunjal
>

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
> > > >
> > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>

Tuesday, March 20, 2012

Question on security

Hi,
I have a feeling what I want to do is not possible. I have a reportserver
(everything works fine) and I have an asp.net app which allows users to run
some reports. I dont use the reportviewer control, I just opent the report in
a new IE window by using url access e.g.
http://reportserver/reports/TimeReport&rs:command=render.
This works fine but is there a way of preventing users just typing in
http://reportserver/reports into the url and therefore accessing all the
reports. I know I could use windows authentication for the reports but I cant
do that for various reasons.
Is there any setting I can set on the http://reportserver/reports site to
only allow certain users access it but yet allow url access to reports as
normal using e.g. http://reportserver/reports/TimeReport&rs:command=render.
Its a bit of a contradiction really, I want the reportsite to be secure but
yet be able to run url access reports from it.
Thanks
NYou could deny access to the root from the report manager. Just allow
access to the subfolders/individual reports. Of course, they could then
directly access the subfolder...
Mike G.
"NH" <NH@.discussions.microsoft.com> wrote in message
news:846A037D-582D-498E-A6A1-5BF59AA772AF@.microsoft.com...
> Hi,
> I have a feeling what I want to do is not possible. I have a reportserver
> (everything works fine) and I have an asp.net app which allows users to
> run
> some reports. I dont use the reportviewer control, I just opent the report
> in
> a new IE window by using url access e.g.
> http://reportserver/reports/TimeReport&rs:command=render.
> This works fine but is there a way of preventing users just typing in
> http://reportserver/reports into the url and therefore accessing all the
> reports. I know I could use windows authentication for the reports but I
> cant
> do that for various reasons.
> Is there any setting I can set on the http://reportserver/reports site to
> only allow certain users access it but yet allow url access to reports as
> normal using e.g.
> http://reportserver/reports/TimeReport&rs:command=render.
> Its a bit of a contradiction really, I want the reportsite to be secure
> but
> yet be able to run url access reports from it.
> Thanks
> N
>|||Thanks Mike G, I dont think there is a way to do this.
"Mike G." wrote:
> You could deny access to the root from the report manager. Just allow
> access to the subfolders/individual reports. Of course, they could then
> directly access the subfolder...
> Mike G.
>
> "NH" <NH@.discussions.microsoft.com> wrote in message
> news:846A037D-582D-498E-A6A1-5BF59AA772AF@.microsoft.com...
> > Hi,
> >
> > I have a feeling what I want to do is not possible. I have a reportserver
> > (everything works fine) and I have an asp.net app which allows users to
> > run
> > some reports. I dont use the reportviewer control, I just opent the report
> > in
> > a new IE window by using url access e.g.
> > http://reportserver/reports/TimeReport&rs:command=render.
> >
> > This works fine but is there a way of preventing users just typing in
> > http://reportserver/reports into the url and therefore accessing all the
> > reports. I know I could use windows authentication for the reports but I
> > cant
> > do that for various reasons.
> >
> > Is there any setting I can set on the http://reportserver/reports site to
> > only allow certain users access it but yet allow url access to reports as
> > normal using e.g.
> > http://reportserver/reports/TimeReport&rs:command=render.
> > Its a bit of a contradiction really, I want the reportsite to be secure
> > but
> > yet be able to run url access reports from it.
> >
> > Thanks
> > N
> >
> >
>
>

Wednesday, March 28, 2012

Question related to Analysis Service Roles

Monday, March 26, 2012

Question regarding "New Role Assignment"

Friday, March 23, 2012

Question on Users and Logins

Question on Users and Logins

Question on Users and Logins

Tuesday, March 20, 2012

Question on security

SQLAgentMail

Blog Archive

About Me