How to force CRUDs be handled through Stored Proc.

Hi,
If I want no one to be able to use then native select, delete, update etc..
and rather force the user to use stored procedure that I have included in th
e
server. how to do that?
Give:
the database has 2- accounts...one limited privileges account for the users
to use and one for me the owner. I want to deny usage of stored procedures.
plus I want to encrypt the procedure listing so now one can see the inside
Thank youDon't give the users permissions directly on the tables, only the stored pro
cedures. As for
encryption, create the procs using WITH ENCRYPTION (however, if someone want
s to, they can Google
for decryption and find it within a minute).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Lottoman2000 NEWBE" <Lottoman2000NEWBE@.discussions.microsoft.com> wrote in
message
news:76285543-FE12-4D61-80F9-E07A6BFDEABD@.microsoft.com...
> Hi,
> If I want no one to be able to use then native select, delete, update etc.
.
> and rather force the user to use stored procedure that I have included in
the
> server. how to do that?
> Give:
> the database has 2- accounts...one limited privileges account for the user
s
> to use and one for me the owner. I want to deny usage of stored procedures
.
> plus I want to encrypt the procedure listing so now one can see the inside
> Thank you|||"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:edPqteKSFHA.3788@.tk2msftngp13.phx.gbl...
> Don't give the users permissions directly on the tables, only the stored
> procedures. As for encryption, create the procs using WITH ENCRYPTION
> (however, if someone wants to, they can Google for decryption and find it
> within a minute).
Yeah, WITH ENCRYPTION tends to keep "honest people honest"|||Then how do i protect my stored procedures for listing?
"Michael C#" wrote:

> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n
> message news:edPqteKSFHA.3788@.tk2msftngp13.phx.gbl...
> Yeah, WITH ENCRYPTION tends to keep "honest people honest"
>
>|||You can't.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Lottoman2000 NEWBE" <Lottoman2000NEWBE@.discussions.microsoft.com> wrote in
message
news:2505B075-0207-48EB-BA08-87C57FE3088D@.microsoft.com...
> Then how do i protect my stored procedures for listing?
> "Michael C#" wrote:
>|||Thank you all for the help,
I want to highlight that the user account is secret as well. the users must
use an application to access the db. the application has an obfuscated user
account and password that is authorized to do cruds through stored procedure
s
(assuming I denied direct access to the table) so this user account is the
only way a user can access the db. For a user to log to the SQl sever he mus
t
guess the account and password. which as securely saved/protected PKI model.
But assume he guessed the account (limited privileges) and password, and he
is now on the server. he will not be able to use the stored procedures
because I designed the procedures to take a parameter that is secret and
saved again within the application that user must use to access the
database... so the user can see the stored procedure signature I presume bu
t
have to guess the key. he is not the owner so he cant delete, and it was
saved WITH ENCRYPTION so it's encrypted and he can't see the listing and
hence see the" IF ELSE" where I check for the secret key value passed to the
procedure. Now the nightmare is that he decrypts the stored procedure...so
I have 3 questions
1- How can I protect him from opening the stored procedures?
2- Can I program the stored procedure to include check such if else
etc,,(obviously I'm novice to T-Sql)
3- can I use the e-mail mechanism from within a stored procedure to notify
me of suspicious attempts. such as when the key entered was bad. based on my
closed model on failed key attempt is too many and would trigger a
notification email.
Thank you so very much
"Tibor Karaszi" wrote:

> Don't give the users permissions directly on the tables, only the stored p
rocedures. As for
> encryption, create the procs using WITH ENCRYPTION (however, if someone wa
nts to, they can Google
> for decryption and find it within a minute).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Lottoman2000 NEWBE" <Lottoman2000NEWBE@.discussions.microsoft.com> wrote i
n message
> news:76285543-FE12-4D61-80F9-E07A6BFDEABD@.microsoft.com...
>
>|||1. You can't.
2. Yes. There are procedural constructs in TSQL. See for instance IF..ELSE i
n Books Online.
3. You could use xp_sendmail or xp_smtp_sendmail (better, doesn't use MAPI,
but you need to download
and install from www.sqldev.net). Or put enough info in a table and have an
outside process (like
SQL Server Agent job) regularly read this table and send emails. Or use Noti
fication Services (free
download from MS).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Lottoman2000 NEWBE" <Lottoman2000NEWBE@.discussions.microsoft.com> wrote in
message
news:CC563474-6A2B-4792-86DC-5AE5CFC0F352@.microsoft.com...
> Thank you all for the help,
> I want to highlight that the user account is secret as well. the users mus
t
> use an application to access the db. the application has an obfuscated use
r
> account and password that is authorized to do cruds through stored procedu
res
> (assuming I denied direct access to the table) so this user account is the
> only way a user can access the db. For a user to log to the SQl sever he m
ust
> guess the account and password. which as securely saved/protected PKI mode
l.
> But assume he guessed the account (limited privileges) and password, and h
e
> is now on the server. he will not be able to use the stored procedures
> because I designed the procedures to take a parameter that is secret and
> saved again within the application that user must use to access the
> database... so the user can see the stored procedure signature I presume
but
> have to guess the key. he is not the owner so he cant delete, and it was
> saved WITH ENCRYPTION so it's encrypted and he can't see the listing and
> hence see the" IF ELSE" where I check for the secret key value passed to t
he
> procedure. Now the nightmare is that he decrypts the stored procedure...so
> I have 3 questions
> 1- How can I protect him from opening the stored procedures?
> 2- Can I program the stored procedure to include check such if else
> etc,,(obviously I'm novice to T-Sql)
> 3- can I use the e-mail mechanism from within a stored procedure to notify
> me of suspicious attempts. such as when the key entered was bad. based on
my
> closed model on failed key attempt is too many and would trigger a
> notification email.
> Thank you so very much
> "Tibor Karaszi" wrote:
>|||If i cant prevemt decrymption, then my only lien of defence the is embeded
acount and password. and hope the user will never abe able to guess.
Do you have better suggestions?
"Tibor Karaszi" wrote:

> 1. You can't.
> 2. Yes. There are procedural constructs in TSQL. See for instance IF..ELSE
in Books Online.
> 3. You could use xp_sendmail or xp_smtp_sendmail (better, doesn't use MAPI
, but you need to download
> and install from www.sqldev.net). Or put enough info in a table and have a
n outside process (like
> SQL Server Agent job) regularly read this table and send emails. Or use No
tification Services (free
> download from MS).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Lottoman2000 NEWBE" <Lottoman2000NEWBE@.discussions.microsoft.com> wrote i
n message
> news:CC563474-6A2B-4792-86DC-5AE5CFC0F352@.microsoft.com...
>
>|||> 1- How can I protect him from opening the stored procedures?
You can't easily. The better way to get the level of security you are reques
ting
here would be to encrypt the actual data and have the stored procs merely
provide CRUD services. Thus, even if the user sees the stored proc, without
the
ability to decrypt the data itself, the stored proc by itself would useless.

> 2- Can I program the stored procedure to include check such if else
> etc,,(obviously I'm novice to T-Sql)
Not sure what you mean here.

> 3- can I use the e-mail mechanism from within a stored procedure to notify
> me of suspicious attempts. such as when the key entered was bad. based on
my
> closed model on failed key attempt is too many and would trigger a
> notification email.
> Thank you so very much
Yes but it might be trickier than you think. This can be done in the stored
procs themselves and/or in your middle layer code.
Thomas|||"Lottoman2000 NEWBE" <Lottoman2000NEWBE@.discussions.microsoft.com> wrote in
message news:7EB4CFB4-BCC6-4B7D-9204-B17B16C916E6@.microsoft.com...
> If i cant prevemt decrymption, then my only lien of defence the is embeded
> acount and password. and hope the user will never abe able to guess.
> Do you have better suggestions?
WITH ENCRYPTION keeps "honest people honest", and prevents novices from
hacking into your code, and it's not all that secure. There are just too
many tools available to decrypt SP's. Another idea might be to store your
queries internally to your application in an encrypted format and decrypt
right before execution, instead of using SP's. You'll take a performance
hit on this, however, which may or may not be negligible. This brings you
back full-circle to your original question, however, about forcing access to
tables only via SP's...sql

How to flush the buffer to trc file

I want to trace the user logins by using a stored procedure. This script (sp_login_trace) is created by the SQL Profiler tool. (Once this procedure works well, I will use sp_procoption to run it automatically everytime the SQL Server startup.)

After I successfully created sp_login_trace, I run it (exec sp_login_trace). The trace process is started and TraceID is 1. (I use select * from ::fn_trace_getinfo(default) to verify it). However the file size of login_trace.trc is always 0 even after I use Query Ananlysis or Eneterprise manager to let some users to login into the SQL Server instance. (when I use SQL Profiler to start a trace, the trace file size will increase along with users continaully login in). At that time if I use SQL Profiler to open the login_trace.trc file, the system will give me an error message: No data since Empty File.

After I stop and delete the trace process, I find that the file size of login_trace.trc becomes 128K and I can see the login records caught by sp_login_trace if I use SQL Profiler to open this file again.

How can I flush the buffer to trc file frequently without need of stopping trace process?

Thanks for helps in advance.

LeonHere is the script of sp_login_trace (Leon)

CREATE PROCEDURE sp_login_trace
AS
BEGIN
/************************************************** **/
/* The following statements contain the SQL Server Profiler-generated */
/* script to create the trace with the required events and data columns. */
/************************************************** **/
-- Create a queue
DECLARE @.rc int
DECLARE @.TraceID int
DECLARE @.maxfilesize bigint
SET @.maxfilesize = 5
EXEC @.rc = sp_trace_create @.TraceID output, 2,
N'C:\Allprojects\SQL_Server_2000\login_trace',
@.maxfilesize, NULL
IF(@.rc != 0) GOTO error

-- You can't script the client-side file and table.
-- Set the events.
declare @.on bit
set @.on = 1
exec sp_trace_setevent @.TraceID, 14, 1, @.on
exec sp_trace_setevent @.TraceID, 14, 6, @.on
exec sp_trace_setevent @.TraceID, 14, 9, @.on
exec sp_trace_setevent @.TraceID, 14, 10, @.on
exec sp_trace_setevent @.TraceID, 14, 11, @.on
exec sp_trace_setevent @.TraceID, 14, 12, @.on
exec sp_trace_setevent @.TraceID, 14, 13, @.on
exec sp_trace_setevent @.TraceID, 14, 14, @.on
exec sp_trace_setevent @.TraceID, 14, 16, @.on
exec sp_trace_setevent @.TraceID, 14, 17, @.on
exec sp_trace_setevent @.TraceID, 14, 18, @.on

-- Set the Filters
declare @.intfilter int
declare @.bigintfilter bigint

exec sp_trace_setfilter @.TraceID, 10, 0, 7, N'SQL Profiler'

-- Set the trace status to start.
EXEC sp_trace_setstatus @.TraceID, 1

GOTO finish

error:
SELECT ErrorCode=@.rc

finish:
END

GOsql

How to find the end month date of the particular date.

Suppose i am taking abc as input as a parameter from the user to generate report.
user input 10/20/2006 i want's to find out the end month date of this date that is 10/30/2006
or if he input 1/15/2005 |I want to find 1/31/2006.
I want to do from the report function not query.
how can i do that

> I want to do from the report function not query.

I'm not quite sure what you mean by this. I supose this means that you don't want to do it in MDX, but you don't mention what you are using as a reporting front end. In psuedo code what you would do is

1) subtract the day number from the date ( this should give you the last day of the previous month)

2) add 1 month to the date you got from the first step.

|||

Can you please give a working example ?

Do you know about any thing about date subtract in SSRS
Like I have 2/15/2007 as input parameter from report I want's to subtract 15 days and result should be like 1/31/2007

I have get the day from the date and i can add a month also in the date But I cant' find any thing about how to subtract days from the date and result should be another date

|||

You should be able to add the following in the code section of your report and then just call this function.

function GetLastDayInMonth(dt as DateTime) as DateTime

return dt.AddDays(-dt.Day).AddMonths(1)

end function

sql

how to find records in database using select

I need to be able to find certain data as the user has submitted that data twice and delete one record,except that have hundreds of tables and don't know the table where she would have submitted the data, but I have some other key info that I can start with.

My question is,how do i select a record from the database if I don't know the table it comes from?

Could somebody give me an expamle please?How did they submit the data? Do you know the data submitted? You have to have something to go on.|||Can you pose the question exactly as the teacher wrote it in your assignment? Just type it verbatim into a posting, and you'll get a much better quality answer.

-PatP

How to find out which databases a user have access to?

Hi, my first question, tried to use search first but couldn?t find what I need.

Hopefully someone has asuggestion.

I work for a ISV producing HR applications.

When user starts the applications the program shows all databases that the user have access to. This is done by select all names from master..sysdatabases and then trying to "USE dbname" to see if user have access,. Users doesn't have sa rights.

This procedure takes approximately 90 secs for a server with 500 databases and that has become a problem.

Does anyone know off a faster method, any suggestions?

/Regards Anders

If you use the sys.databases view you will only see the databases where you have access to, as the meta data is secured in SQL Server 2005. If you are using a version prior SQL Server 2005, you will have to go through all database getting the information from the appropiate tables.

Jens K. Suessmeyer.

http://www.sqlserver2005.de

How to find out when a user last logged in

Hi There

I am in the process of cosolidating sql servers.

One of them literally has hundreds of sql logins, obviously i need to clean these up, however i a not sure how to determine when a user last logged in ?

How can i find out the last login date of a user ?

Thanx

Hi,

AFAIK this is not stored in tables in SQL Server, but it is either stored in

1. The event log if you turned on the Audit feature in the server properties.
2. In the c2 audit logs (if turned on)
3. In the profiler trace (if you are running one and are wathing the Audit events on the server)

HTH, jens Suessmeyer.

http://www.sqlserver2005.de

|||

Oh man that is bad news i guess, i could spend days sifting through the event logs, and even then i wont be sure a login is not used, for example if someone simply has not logged in this week.

Thanx Anyway

How to find objects by owner

I have an application wich generates database objects (mostly views) within
the application itself. So I have a bunch of views created by a user who I
have to delete but I can't do it because he is owning objects.
Can I write a query to list al the objects owned by this user?
Regardsselect name from sysobjects where uid = user_id('NameOfOwner')
"Zekske" wrote:
> I have an application wich generates database objects (mostly views) within
> the application itself. So I have a bunch of views created by a user who I
> have to delete but I can't do it because he is owning objects.
> Can I write a query to list al the objects owned by this user?
> Regards|||You could use the inbuilt views - just change 'dbo' to your user...
-- tables and views
select * from information_schema.tables
where table_schema = 'dbo'
-- stored procs and functions
select routine_name from information_schema.routines
where routine_schema = 'dbo'
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

How to find objects by owner

I have an application wich generates database objects (mostly views) within
the application itself. So I have a bunch of views created by a user who I
have to delete but I can't do it because he is owning objects.
Can I write a query to list al the objects owned by this user?
Regards
select name from sysobjects where uid = user_id('NameOfOwner')
"Zekske" wrote:

> I have an application wich generates database objects (mostly views) within
> the application itself. So I have a bunch of views created by a user who I
> have to delete but I can't do it because he is owning objects.
> Can I write a query to list al the objects owned by this user?
> Regards
|||You could use the inbuilt views - just change 'dbo' to your user...
-- tables and views
select * from information_schema.tables
where table_schema = 'dbo'
-- stored procs and functions
select routine_name from information_schema.routines
where routine_schema = 'dbo'
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

How to find objects by owner

I have an application wich generates database objects (mostly views) within
the application itself. So I have a bunch of views created by a user who I
have to delete but I can't do it because he is owning objects.
Can I write a query to list al the objects owned by this user?
Regardsselect name from sysobjects where uid = user_id('NameOfOwner')
"Zekske" wrote:

> I have an application wich generates database objects (mostly views) withi
n
> the application itself. So I have a bunch of views created by a user who I
> have to delete but I can't do it because he is owning objects.
> Can I write a query to list al the objects owned by this user?
> Regards|||You could use the inbuilt views - just change 'dbo' to your user...
-- tables and views
select * from information_schema.tables
where table_schema = 'dbo'
-- stored procs and functions
select routine_name from information_schema.routines
where routine_schema = 'dbo'
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

How to find group user login Name

Our system administrator set up an NT server group in order to allow
our users to login to our application via https to our sql server.
The group appears as a User in SQL Server when you look at it in
Enterprise Manager. That said, I can not see the users associated
with the group from Enterprise Manager, but know they can login to the
database.
The problem is this. When we login via the web we get access to the
database without problem, but when you look at the current_user what
you see is the login Name the user entered and NOT the name of the
group/User. That is to say, I can see a UserID which is not listed as
a User in SQL Server and can't see the name of the group, which is
listed as a user in SQL Server.
I need to know who's logging in order to direct them to the
appropriate web page via their role. Before the admin set up the
group, I was using sp_helpuser to get the role, but then again I had
the userID to do this.
The question I have now, is there any way to see what the
group/user is who logged in i.e. the goup listed as the User in
Enterprise Manager? Otherwise I have to build a table of userIDs and
their group/User name, which seems to defeat the purpose of having he
server authenticate users.

Thanks,
Tom"Tom Loach" <tcl4p@.virginia.edu> wrote in message
news:o7vttvktkbdbib39pqklga3f8s9lgdsksc@.4ax.com...
> Our system administrator set up an NT server group in order to allow
> our users to login to our application via https to our sql server.
> The group appears as a User in SQL Server when you look at it in
> Enterprise Manager. That said, I can not see the users associated
> with the group from Enterprise Manager, but know they can login to the
> database.
> The problem is this. When we login via the web we get access to the
> database without problem, but when you look at the current_user what
> you see is the login Name the user entered and NOT the name of the
> group/User. That is to say, I can see a UserID which is not listed as
> a User in SQL Server and can't see the name of the group, which is
> listed as a user in SQL Server.
> I need to know who's logging in order to direct them to the
> appropriate web page via their role. Before the admin set up the
> group, I was using sp_helpuser to get the role, but then again I had
> the userID to do this.
> The question I have now, is there any way to see what the
> group/user is who logged in i.e. the goup listed as the User in
> Enterprise Manager? Otherwise I have to build a table of userIDs and
> their group/User name, which seems to defeat the purpose of having he
> server authenticate users.
> Thanks,
> Tom

I'm not sure I understand your setup completely, but if you use CURRENT_USER
you will see the user name in the current database, not the login name. If
you use SYSTEM_USER instead, you should see the user's login - does this
give the result you expect?

If not, then perhaps you could clarify the sequence of events used to set up
the group, espeically the difference between a server login and a database
user - this is what's not entirely clear from your post above. If you can
provide the actual SQL commands used to set up server access and permissions
for the Windows group, that would be the least ambiguous description,
although of course you may well have done it through Enterprise Manager
instead.

Simon|||Simon,
thanks for your suggestion, but alas it did not work. Iam sorrry
I was not able to make the situation more clear, but perhaps another
try.
We have a group account set up, which if you look at in Enterprise
manager appears as a User i.e. a_Group. Obviously, since this is a
group account it is comprised of multiple users who need access to SQL
Server. The problem we're having is, if you login to the system and
if I run a stored procedure that returns the Current_User, what I get
is not the name of the User i.e. a_Group that appears in Enterprise
Manager, but the UserID i.e. h3tcl that was used to login to the
Windows server.
When I tried to exec sp_helpuser using Current_User I get an error
since SQL Server does not know who the user is (it sees h3tcl). If I
exec sp_helpuser and enter the user name (a_Group) found in
Enterprise manager sp_helpuser works fine and returns the appropriate
data from SQL Server.
So I'm back to the problem is how and where do I look for the group
name? If you have any other ideas I'm more than open. Thanks again
for your input.

Regards,
Tom

On Tue, 16 Dec 2003 16:44:21 +0100, "Simon Hayes" <sql@.hayes.ch>
wrote:

>"Tom Loach" <tcl4p@.virginia.edu> wrote in message
>news:o7vttvktkbdbib39pqklga3f8s9lgdsksc@.4ax.com...
>> Our system administrator set up an NT server group in order to allow
>> our users to login to our application via https to our sql server.
>> The group appears as a User in SQL Server when you look at it in
>> Enterprise Manager. That said, I can not see the users associated
>> with the group from Enterprise Manager, but know they can login to the
>> database.
>> The problem is this. When we login via the web we get access to the
>> database without problem, but when you look at the current_user what
>> you see is the login Name the user entered and NOT the name of the
>> group/User. That is to say, I can see a UserID which is not listed as
>> a User in SQL Server and can't see the name of the group, which is
>> listed as a user in SQL Server.
>> I need to know who's logging in order to direct them to the
>> appropriate web page via their role. Before the admin set up the
>> group, I was using sp_helpuser to get the role, but then again I had
>> the userID to do this.
>> The question I have now, is there any way to see what the
>> group/user is who logged in i.e. the goup listed as the User in
>> Enterprise Manager? Otherwise I have to build a table of userIDs and
>> their group/User name, which seems to defeat the purpose of having he
>> server authenticate users.
>>
>> Thanks,
>> Tom
>I'm not sure I understand your setup completely, but if you use CURRENT_USER
>you will see the user name in the current database, not the login name. If
>you use SYSTEM_USER instead, you should see the user's login - does this
>give the result you expect?
>If not, then perhaps you could clarify the sequence of events used to set up
>the group, espeically the difference between a server login and a database
>user - this is what's not entirely clear from your post above. If you can
>provide the actual SQL commands used to set up server access and permissions
>for the Windows group, that would be the least ambiguous description,
>although of course you may well have done it through Enterprise Manager
>instead.
>Simon

How to find All Permissions in the Current Database for a particular User

How to find All Permissions in the Current Database for a particular
User in SQL 2005 ?

Thanks

M A Srinivasmasri999@.gmail.com wrote:

Quote:

Originally Posted by

How to find All Permissions in the Current Database for a particular
User in SQL 2005 ?
>
>
Thanks
>
M A Srinivas

sp_helprotect @.username = 'SomeUser'

How to filter reports user logon info

I am new to reporting services
Is there an example of how to obtain user logon information to use as input
for queries before report renders. We will deploy reports to a portal and
want to use the user logon info as a filter for initial queries ( for example
to restrict information viewed by client ). Thanks.You can use the User!UserID global, which is the logged on username. Pass
it into a query parameter. Of course, you will need to have data in your
source database tables that includes the UserID values, to join and filter
your data.
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"MJ Taft" <MJTaft@.discussions.microsoft.com> wrote in message
news:927CF045-4639-48E0-BF84-46D8A52BD532@.microsoft.com...
>I am new to reporting services
> Is there an example of how to obtain user logon information to use as
> input
> for queries before report renders. We will deploy reports to a portal and
> want to use the user logon info as a filter for initial queries ( for
> example
> to restrict information viewed by client ). Thanks.|||Where is it picking up the global value from? When I was working with
Cleverpath portal before (CA's portal product) the reports were published to
the portal and the logon id was obtained from the portal signon. In the case
of RS, the reports are deployed to the report server - are there tables
associated with the report server? Is it getting the value from the browser
session?
"Jeff A. Stucker" wrote:
> You can use the User!UserID global, which is the logged on username. Pass
> it into a query parameter. Of course, you will need to have data in your
> source database tables that includes the UserID values, to join and filter
> your data.
> --
> Cheers,
> '(' Jeff A. Stucker
> \
> Business Intelligence
> www.criadvantage.com
> ---
> "MJ Taft" <MJTaft@.discussions.microsoft.com> wrote in message
> news:927CF045-4639-48E0-BF84-46D8A52BD532@.microsoft.com...
> >I am new to reporting services
> > Is there an example of how to obtain user logon information to use as
> > input
> > for queries before report renders. We will deploy reports to a portal and
> > want to use the user logon info as a filter for initial queries ( for
> > example
> > to restrict information viewed by client ). Thanks.
>
>|||Yes, it's getting the user name from the session. If you have Windows
Authentication, it's getting it from the OS. If you have custom security
and forms auth, then it's getting it from the forms auth cookie.
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"MJ Taft" <MJTaft@.discussions.microsoft.com> wrote in message
news:A5CD5045-9C76-4F0E-B092-F1AB2B372A1E@.microsoft.com...
> Where is it picking up the global value from? When I was working with
> Cleverpath portal before (CA's portal product) the reports were published
> to
> the portal and the logon id was obtained from the portal signon. In the
> case
> of RS, the reports are deployed to the report server - are there tables
> associated with the report server? Is it getting the value from the
> browser
> session?
> "Jeff A. Stucker" wrote:
>> You can use the User!UserID global, which is the logged on username.
>> Pass
>> it into a query parameter. Of course, you will need to have data in your
>> source database tables that includes the UserID values, to join and
>> filter
>> your data.
>> --
>> Cheers,
>> '(' Jeff A. Stucker
>> \
>> Business Intelligence
>> www.criadvantage.com
>> ---
>> "MJ Taft" <MJTaft@.discussions.microsoft.com> wrote in message
>> news:927CF045-4639-48E0-BF84-46D8A52BD532@.microsoft.com...
>> >I am new to reporting services
>> > Is there an example of how to obtain user logon information to use as
>> > input
>> > for queries before report renders. We will deploy reports to a portal
>> > and
>> > want to use the user logon info as a filter for initial queries ( for
>> > example
>> > to restrict information viewed by client ). Thanks.
>>

how to filter output based on user's AD group membership?

I want to filter reports based on a users active directory security i.e. what
parameters they can select, what columns they can see
But I can't figure out the best way of doing this and the documentation on
AD seems to assume that the user already understands AD.
From what I can tell I have two options
1) use sql with the openquery syntax - don't think this is going to be an
option due to the hassle here of setting up linked servers (bureaucracy in
the extreme)
2)use system.directoryservices with some code built into to the report - i
don't really understand how to do this, none of the code samples i have seen
seem to do what I want e.g. pass in username from global report parameters
along with the groupname i want to check against, and return whether they are
in that particular group as true/false
help greatly appreciated!
do you know a better way of doing this? code samples? etc
thanks!AD queries in SQL Server using the ADSI can be a problem... I am told it will
only search the first 1000 rows returned by the AD..
if you are using SQL 2005... Take a look at the sys.login_token
It shows all of the AD groups the user is a member of.
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"adolf garlic" wrote:
> I want to filter reports based on a users active directory security i.e. what
> parameters they can select, what columns they can see
> But I can't figure out the best way of doing this and the documentation on
> AD seems to assume that the user already understands AD.
> From what I can tell I have two options
> 1) use sql with the openquery syntax - don't think this is going to be an
> option due to the hassle here of setting up linked servers (bureaucracy in
> the extreme)
> 2)use system.directoryservices with some code built into to the report - i
> don't really understand how to do this, none of the code samples i have seen
> seem to do what I want e.g. pass in username from global report parameters
> along with the groupname i want to check against, and return whether they are
> in that particular group as true/false
> help greatly appreciated!
> do you know a better way of doing this? code samples? etc
> thanks!

How To filter data set based on value from another

I have a data set that contains rows with a column for "User ID". I want to filter a second data set that contains one or more user "role names" by "User ID" . I want to do this dynamically without having to define a parameter. I then want to to take the filtered "Role Names" data set and display on a report. Any pointers would be appreciated.

Data Set #1
User ID First Last ........
crace Chares Race
mmiller Mike Miller

Data Set #2
User ID Role
crace administrator
crace guest
mmiller guest

Desired Report Result

Page 1
crace Charles Race
Roles administrator
guest

Page 2
mmiller Mike Miller
Roles guest
Thanks
CharlesHi,
cycle through data set 1 with a for loop and save the id. then cylcle with a second loop through dataset 2 and look for the user ID. Then you have a variable which contains the row where this user is in the first dataset and a diferent one, which contains the rownumber in which the user is in dataset 2.|||Even though this would work it wouldn't be very efficient as there are over 20,000 rows to traverse through. Would there be a more efficient approach?

Thanks
Charles|||When importing the data into the new dataset, store the key in a array (or hashtable) and before inserting the next record - check the hashtable if it already contains the key, if so skip it.....|||Hi,

can you not use a sql statement with a where clause when retriving your data from the database?! so you would have one huge dataset containing the data in the way you want to have it.