Pwdcompare()

I have a Oracle view which has user-info : user-name , passwords. Passwords in this view are encrypted. I exported this view to SQL Server. Can i use PWDCOMPARE(<plaintext>, <encryptedtext>) function on exported table to compare the Passwords?I don't think this is going to resolve your issue. Encryption done by Oracle is *proprietary* to Oracle. Pdwcompare() is proprietary to SQL. I also want to note that the use of this undocumented function is not supported by MS and they could change its behavior at anytime.|||With pwd.. functions and large strings, ANY user can crash pre-SP3 sql server!|||So how can i secure the user-info table which has "user-name & password" in SQL Server. Is thier a way that i can hide the password?

Thanks|||Do not save passwords in open format, even encrypted.
If your table is used to authorize users, store hash of password,
for example SHA1. Try http://www.activecrypt.com.

Good luck !|||Hi ispaleny,

Thank you for the link. It was really helpful. I just went through the link. It defines some encrytion algorithms. I am using SQL Server 7, and it doesn't allow "CREATE FUNCTION". Is their any other alternative?

Thanks|||Use stored procedure to encrypt row by row.|||I am able to use the stored procedure to encrypt each row one by one. But i am facing problem comparing the password.

I tried using "exec <storedprocedure name> " in the select statement and it doesn't allow me to do so. i want to use the return value of the stored procedure in the SELECT statement. I wrote stored procedure with two input variable and one output variable. I want to use the value of this output variable in the select statement.

Thanks|||Is this what you want ?

declare @.Ret bit
exec usp_YourSP '&^%','@.$#%^^$^#',@.Ret OUTPUT
select * from YourTable where YourCol=@.Ret|||No I was trying to use something like this

Select * from table1 where col1 = exec <storedprocedure> @.Ret OUTPUT.

I wanted call this stored procedure in my servlet program. I got that working, by using CallableStatement. Link you posted http://www.activecrypt.com. was very helpful. Thank you.

Pulling data from 2 tables, 1 with possibly multiple records

I'm trying to create a view which pulls data from the following 2 tables:
CREATE TABLE PC (
PCName varchar(50) NOT NULL,
Make varchar(50),
Model varchar(50),
SerialNumber varchar(50)
PRIMARY KEY
(PCName)
)
INSERT INTO PC(PCName, Make, Model, SerialNumber) values ('TEST1', 'Dell',
'OptiPlex GX1', '12345')
INSERT INTO PC(PCName, Make, Model, SerialNumber) values ('TEST2', 'Dell',
'PowerEdge 6450', '23456')
CREATE TABLE CPU (
PCName varchar(50) NOT NULL REFERENCES PC(PCName),
Row int NOT NULL,
Type varchar(50),
Speed int
PRIMARY KEY
(PCName, Row)
)
INSERT INTO CPU (PCName, Row, Type, Speed) values ('TEST1', 1, 'Pentium
III', 1000)
INSERT INTO CPU (PCName, Row, Type, Speed) values ('TEST2', 1, 'Pentium III
Xeon', 700)
INSERT INTO CPU (PCName, Row, Type, Speed) values ('TEST2', 2, 'Pentium III
Xeon', 700)
INSERT INTO CPU (PCName, Row, Type, Speed) values ('TEST2', 3, 'Pentium III
Xeon', 700)
INSERT INTO CPU (PCName, Row, Type, Speed) values ('TEST2', 4, 'Pentium III
Xeon', 700)
The CPU table holds an entry for each CPU in the PC. Now I want the view to
retrieve 1 row per PC and look like this...
PCName Make Model SerialNumber Type
Speed NumberOfCPUs
========================================
==============================
TEST1 Dell OptiPlex GX1 12345 Pentium III
1000 1
TEST2 Dell PowerEdge 6450 23456 Pentium III
Xeon 1200 4
... where NumberOfCPUs is the max(Row) for each particular PC.
Any ideas? Unfortunately we are stuck with the table structure as is (from
a 3rd party).
ThanksJim
Thanks for posting DDL
See , if this helps you
SELECT PC.PCName,Make,Model,SerialNumber,
Type, Speed,NumberOfCPUs FROM PC JOIN
(
SELECT MAX(Row)NumberOfCPUs,PCName,Type,Speed FROM CPU
GROUP BY PCName,Type,Speed
) AS Der ON PC.PCName=Der.PCName
"Jim Coyne" <REcoyneMO_jimVE@.hoMEtmail.com> wrote in message
news:usUSwQlvFHA.2072@.TK2MSFTNGP14.phx.gbl...
> I'm trying to create a view which pulls data from the following 2 tables:
> CREATE TABLE PC (
> PCName varchar(50) NOT NULL,
> Make varchar(50),
> Model varchar(50),
> SerialNumber varchar(50)
> PRIMARY KEY
> (PCName)
> )
> INSERT INTO PC(PCName, Make, Model, SerialNumber) values ('TEST1', 'Dell',
> 'OptiPlex GX1', '12345')
> INSERT INTO PC(PCName, Make, Model, SerialNumber) values ('TEST2', 'Dell',
> 'PowerEdge 6450', '23456')
> CREATE TABLE CPU (
> PCName varchar(50) NOT NULL REFERENCES PC(PCName),
> Row int NOT NULL,
> Type varchar(50),
> Speed int
> PRIMARY KEY
> (PCName, Row)
> )
> INSERT INTO CPU (PCName, Row, Type, Speed) values ('TEST1', 1, 'Pentium
> III', 1000)
> INSERT INTO CPU (PCName, Row, Type, Speed) values ('TEST2', 1, 'Pentium
> III Xeon', 700)
> INSERT INTO CPU (PCName, Row, Type, Speed) values ('TEST2', 2, 'Pentium
> III Xeon', 700)
> INSERT INTO CPU (PCName, Row, Type, Speed) values ('TEST2', 3, 'Pentium
> III Xeon', 700)
> INSERT INTO CPU (PCName, Row, Type, Speed) values ('TEST2', 4, 'Pentium
> III Xeon', 700)
>
> The CPU table holds an entry for each CPU in the PC. Now I want the view
> to retrieve 1 row per PC and look like this...
> PCName Make Model SerialNumber Type Speed
> NumberOfCPUs
> ========================================
==============================
> TEST1 Dell OptiPlex GX1 12345 Pentium
> III 1000 1
> TEST2 Dell PowerEdge 6450 23456 Pentium III
> Xeon 1200 4
>
> ... where NumberOfCPUs is the max(Row) for each particular PC.
> Any ideas? Unfortunately we are stuck with the table structure as is
> (from a 3rd party).
> Thanks
>|||Yes, that certainly does. Thank you very much.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uMIH0gmvFHA.2312@.TK2MSFTNGP14.phx.gbl...
> Jim
> Thanks for posting DDL
> See , if this helps you
>
> SELECT PC.PCName,Make,Model,SerialNumber,
> Type, Speed,NumberOfCPUs FROM PC JOIN
> (
> SELECT MAX(Row)NumberOfCPUs,PCName,Type,Speed FROM CPU
> GROUP BY PCName,Type,Speed
> ) AS Der ON PC.PCName=Der.PCName
>
> "Jim Coyne" <REcoyneMO_jimVE@.hoMEtmail.com> wrote in message
> news:usUSwQlvFHA.2072@.TK2MSFTNGP14.phx.gbl...
>

Published Reports in /ReportServer, not in /Reports

I can publish reports to http://myserver/ReportServer just fine, but
when I try to view them through http://myserver/Reports, nothing is
there...no folders, no reports, not even the ability to access the
security. Can you help me?Make sure you are not running Reports website in anonymous.
Also, read up on Roles in RS. You might need to logon as someone in the
adminstrators group (the local machine group). That group automatically is
in a role that will allow you to setup other users/groups into appropriate
roles. However, if you are anonymous then no matter how you log on you will
not have the rights to do this (because everyone is anonymous).
Bruce Loehle-Conger
MVP SQL Server Reporting Services
<robbiesmith79@.gmail.com> wrote in message
news:1141055431.139441.263650@.t39g2000cwt.googlegroups.com...
>I can publish reports to http://myserver/ReportServer just fine, but
> when I try to view them through http://myserver/Reports, nothing is
> there...no folders, no reports, not even the ability to access the
> security. Can you help me?
>|||As I bang my head into the computer shouting "Doh!"... I had annoymous
access turned on on the Reports part.
Thanks Bruce!|||hi mr smith how have you been i was wondering if you got the message that i sent you well if you didnt the message was that i am a black belt canidi for 08 i cant believe i have come so far with the help of so many including you well i hope to hear from you soon if you want to give me a call some time my house number is 1512-295-4847 or my cell phone 1512-396-7786 well like to talk to you soon but bye for now
Sincerely
Robert Hunsucke
From http://www.developmentnow.com/g/115_2006_2_0_0_706496/Published-Reports-in-ReportServer-not-in-Reports.ht
Posted via DevelopmentNow.com Group
http://www.developmentnow.com

Public Synonym?

Hi,

I am an Oracle Developer and just started working on a SQL Server database. I created a view and gave ALL rights to PUBLIC and my supervisor. However, when my supervisor logs on as herself she can see the query (in Query Analyzer) but she can't select data from it.

In Oracle I typically have to create a public synonym so users can use an object without specifying the owner of that object. Does the same apply in SQL Server and if so, how do I create a public synonym?

Thanks,
KDoes that login have access to the database - and within that database does public have "select" rights ? When you manage the permissions for the view do you see that login and if so click on "select" rights.|||Did you create your view in your own schema? (as in user.viewname)

Basically, MS goes through a routine for each table on each query. Suppose you issue:

select * from table

SQL Server first looks for username.table where username is your username. If that fails, SQL Server than looks for dbo.table. If that fails, then an error is returned. Because of that first lookup, it is suggested that you supply the owner of the table for queries that are run many many times per hour. The first lookup is not too work intensie, but if you do it a million times an hour...

As for a public synonym, you have to create a view as dbo.view. Then anyone can query it as just select * from view (provided they have no view or table of trhe same name in their own schema).

public server role

In exploring permissions that users have, I find that they all have VIEW ANY DATABASE permission which they inherit from the public server role. You can see this by selecting the Permissions page on the Server Properties dialog and highlighting "public". The permission shows as having been granted by sa. This is listed as a server role. However, it does not show in the list of server roles and I can't find any documentation for it (RTM BOL). Interestingly, if I revoke this permission (which is the only permission this role has), the public server role disappears from view. But I can subsequently regrant the permission with Transact-SQL in master and the role comes back.

I would like more information about this role. It seems to be sort of "secret".

When I revoke the permission, users can't see any databases except master and tempdb (both of which have active guest users) even though they have been granted access to other databases.

What I was trying to accomplish by changing this permission was to allow a user to see only those databases which they are allowed to use. But that does not seem to be possible.

Thanks for the help.

Sharon

All logins belong to the public server role. It's like the "Everyone" group in Windows. There is a bug filed against Management Studio for not displaying this role in the list of fixed server roles.

The VIEW ANY DATABASE permission is assigned to public for backward compatibility with SQL Server 2000.

Here are a couple of BOL articles that mention this role:

http://msdn2.microsoft.com/en-us/library/ms175892(SQL.90).aspx

http://msdn2.microsoft.com/en-us/library/ms187096(SQL.90).aspx

Thanks
Laurentiu

|||

The role does not show in sp_helpsrvrole, either. I didn't have time to look for an appropriate catalog view, so there may be one that shows it.

Sharon

|||

Catalogs show it: sys.server_principals.

You can see the permissions granted to it in sys.server_permissions.

Thanks
Laurentiu