How do I make a function private?

Hi,
Am just wondering how to make a function private within a package??
FUNCTION get_new_student_id
Thanks.Simply leave the function out of the package specification:

CREATE OR REPLACE PACKAGE pkg IS
PROCEDURE public_proc;
END;
/

CREATE OR REPLACE PACKAGE BODY pkg IS

-- This procedure is private: it can only be called from within the package
PROCEDURE private_proc IS
BEGIN
NULL;
END;

PROCEDURE public_proc IS
BEGIN
private_proc;
END;

END;
/|||In case you're interested... you can also do this within a procedure.

CREATE OR REPLACE PROCEDURE INET.show_test
IS

FUNCTION my_test (a VARCHAR2)
RETURN VARCHAR2
IS
BEGIN
RETURN a;
END;

BEGIN

dbms_output.put_line( my_test('b') );

END;

JoeB

How do i give SQL administrator full rights to SQL 2005 without OS rights

The DBA at our location is demanding local admin (windows) right's to the box so he can function. Right now when he logs in i have given him right's to the inetpub directory, sql directory, i have set him as a sysadmin on sql2005 and gone into the http:\\localhost\reports and set him up as a system manager and under site priveledges set him as a sys admin. When he tries to login and configure the report server he gets the following error:

Title-Reporting services configuration manager

Error-There was an error refreshing the UI. bla bla bla

A WMI error has occurred and no additional error information is availiable

Title-Reporting services configuration manager

Error-There was an error while switching panels. The most likely cause is an error retrieving WMI properties. bla bla bla

A WMI error has occurred and no additional error information is availiable

then when he's in sql server 2005 surface area configuation

Title-Surface Area Configuration

Error-Access denied (system.management)

Is there any documentation or anythign anyone can tell me that i can do to give this DBA full access to configure and admin the SQL portion of his system without giving him admin rights to the OS?

Please help!!

Thanks for any time anyone has taken to review this thread!!

There's no issue in giving him/her admin rights to the box. In most cases, he'll/she'll need it. Don't confuse local admin on the box with network admin rights.

Adamus

|||

That's the problem in my environment i cant give local admin rights to any box. This is per PCI requirements which is a credit card VISA format amongst other regulations i'm under.

Do you have any other info that can provide assistance?

|||I

The DBA at our location is demanding local admin (windows) right's to the box so he can function. Right now when he logs in i have given him right's to the inetpub directory, sql directory, i have set him as a sysadmin on sql2005 and gone into the http:\\localhost\reports and set him up as a system manager and under site priveledges set him as a sys admin. When he tries to login and configure the report server he gets the following error:

Title-Reporting services configuration manager

Error-There was an error refreshing the UI. bla bla bla

A WMI error has occurred and no additional error information is availiable

Title-Reporting services configuration manager

Error-There was an error while switching panels. The most likely cause is an error retrieving WMI properties. bla bla bla

A WMI error has occurred and no additional error information is availiable

then when he's in sql server 2005 surface area configuation

Title-Surface Area Configuration

Error-Access denied (system.management)

Is there any documentation or anythign anyone can tell me that i can do to give this DBA full access to configure and admin the SQL portion of his system without giving him admin rights to the OS?

|||Moving to the SQL Server Security Forum.|||

ok i think i figured out the surface area config issue, theres a link that states : Add new administrator in which i did and i can access all pages now, but it wont obviously let a non admin restart services, since there's a handful of services i think i'll just have/set the services under the SQL admins credentials, unless someone knows of a better way, in which i can allow the sql admin to restart services?

On the other issue i still cant access the reporting service configuration page, still recieving a WMI error?//

Please anyone HELP!!

|||

These sources should help you understand and configure the appropriate account/security for your DBA.

Configuration -Service Accounts, SQL Server 2005 - Setting Up Windows Service Accounts
http://msdn2.microsoft.com/en-us/library/ms143691.aspx
http://msdn2.microsoft.com/en-us/library/ms143504.aspx

Configuration -Service Accounts, SQL Server or SQL Server Agent service account
http://support.microsoft.com/kb/283811/en-us
http://msdn2.microsoft.com/en-us/library/ms143691.aspx

Configuration -Service Accounts,Selecting an Account for the SQL Server Agent Service
http://msdn2.microsoft.com/en-us/library/ms191543.aspx
http://support.microsoft.com/kb/907557

|||

Ok i was looking over the attached documents. It pretty much looks from what you've given me, if i setup the services on the local machine to run under the SQL DBA's credentials that i wont need to do anything further, this will in turn give the SQL DBA access without any errors?

Personally i dont think the service items are the issues but if you're positive this will do what i dneed to do i'll try it. Please reconfirm my understanding of the situation before i attempt to run changes please.

Again thanks for your assistance.

How do i get seq.nextval from within a pl/sql function

Hi,

Im using Oracle 9i and writing a PL/SQL function and i need to get the sequence.nextval within the function. I am getting an error saying sequence does not exist. I have tryed the following :

select mySeq.nextval
into v_myvar
from dual;

but I still get the same error message. Is there any way to get the next value in a sequence from within a PL/SQL function.

Any help would be greatly appreciated.
ThanksCREATE SEQUENCE myseq;

CREATE OR REPLACE FUNCTION fun_seq
RETURN NUMBER
AS
retval NUMBER;
BEGIN
SELECT myseq.NEXTVAL
INTO retval
FROM DUAL;

RETURN (retval);
END;
/

SELECT fun_seq FROM DUAL;

FUN_SEQ
---
1|||Hi,
Thanks. I dont need to create a sequence because the sequence that I am using (mySeq) already exists on the database.

Thanks|||Figured it out. It was a permissions issue.

How do I determine if SQL Sever Login is disabled using T-Sql?

How do I determine if SQL Sever Login is disabled using T-Sql?
I can disable it and enabled it using the following T-Sql function:
ALTER LOGIN login [ENABLE | DISABLE]
I just want to know if it is enable or disabled?It's stored in the system table sys.server_principals, in the column called
is_disabled
However, in SQL Server 2005 system tables are not visible by default; you
must use the Dedicated Administrator Connection (DAC) which you can read
about in the Books Online.
--
HTH
Kalen Delaney, SQL Server MVP
<rodrigo.gloria@.gmail.com> wrote in message
news:1156370327.654473.140550@.p79g2000cwp.googlegroups.com...
> How do I determine if SQL Sever Login is disabled using T-Sql?
> I can disable it and enabled it using the following T-Sql function:
> ALTER LOGIN login [ENABLE | DISABLE]
> I just want to know if it is enable or disabled?
>|||Hi,
You can also query the system view and look into column is_disabled.1
denotes the login is disabled
select * from sys.sql_logins
Tahnks
Hari
SQL Server MVP
<rodrigo.gloria@.gmail.com> wrote in message
news:1156370327.654473.140550@.p79g2000cwp.googlegroups.com...
> How do I determine if SQL Sever Login is disabled using T-Sql?
> I can disable it and enabled it using the following T-Sql function:
> ALTER LOGIN login [ENABLE | DISABLE]
> I just want to know if it is enable or disabled?
>|||Thanks Hari. I totally blew that answer. I was doing some testing, and was
in a connection that was not a sysadmin, just a regular user. So when I
couldn't see server_principals, I assumed it was because it was a system
table.
Then of course logging in using the DAC, I was sysadmin, so I could see that
view.
So sys.server_principals is the answer.
If you check the BOL for sys.sql_logins, you'll see that it is a view based
on sys.server_principals, containing all the columns from that view, plus a
view others.
--
HTH
Kalen Delaney, SQL Server MVP
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:OLo8wkxxGHA.2168@.TK2MSFTNGP06.phx.gbl...
> Hi,
> You can also query the system view and look into column is_disabled.1
> denotes the login is disabled
> select * from sys.sql_logins
>
> Tahnks
> Hari
> SQL Server MVP
> <rodrigo.gloria@.gmail.com> wrote in message
> news:1156370327.654473.140550@.p79g2000cwp.googlegroups.com...
>> How do I determine if SQL Sever Login is disabled using T-Sql?
>> I can disable it and enabled it using the following T-Sql function:
>> ALTER LOGIN login [ENABLE | DISABLE]
>> I just want to know if it is enable or disabled?
>|||Kalen Delaney wrote:
> Thanks Hari. I totally blew that answer. I was doing some testing, and was
> in a connection that was not a sysadmin, just a regular user. So when I
> couldn't see server_principals, I assumed it was because it was a system
> table.
> Then of course logging in using the DAC, I was sysadmin, so I could see that
> view.
> So sys.server_principals is the answer.
> If you check the BOL for sys.sql_logins, you'll see that it is a view based
> on sys.server_principals, containing all the columns from that view, plus a
> view others.
> --
> HTH
> Kalen Delaney, SQL Server MVP
>
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:OLo8wkxxGHA.2168@.TK2MSFTNGP06.phx.gbl...
> > Hi,
> >
> > You can also query the system view and look into column is_disabled.1
> > denotes the login is disabled
> >
> > select * from sys.sql_logins
> >
> >
> >
> > Tahnks
> > Hari
> > SQL Server MVP
> >
> > <rodrigo.gloria@.gmail.com> wrote in message
> > news:1156370327.654473.140550@.p79g2000cwp.googlegroups.com...
> >> How do I determine if SQL Sever Login is disabled using T-Sql?
> >>
> >> I can disable it and enabled it using the following T-Sql function:
> >>
> >> ALTER LOGIN login [ENABLE | DISABLE]
> >>
> >> I just want to know if it is enable or disabled?
> >>
> >
> >
Thank you very much.