[go4expert]

I would like to get the priviledges I [or any user] have for the specified database or object. I know the stored procedures like sp_helpuser and sp_helplogins exist for SQL Server but I would like to know the general sql for oracle and/or sql server.

[Amit Ray]

Hi ..

Oracle privileges can be largely classified as : System privileges and Object privileges. The following tables contain all the privilege information in Oracle :

DBA_SYS_PRIVS : The System Previleges.

DESC DBA_TAB_PRIVS : The Table Privileges.

DESC DBA_ROLE_PRIVS : Roles consist of a set of privileges which can be assigned to users instead of granting privileges one by one.

DESC DBA_COL_PRIVS : The Table Column Privileges.

The text attachment contains a sample session in a Oracle DB which will make a lot things clear.

Amit Ray.

[shabbir]

You can also use sp_helpuser Stored Procedure in SQL Server

Here is the complete description of it

Quote:

Originally Posted by Microsoft MSDN

Syntax
sp_helpuser [ [ @name_in_db = ] 'security_account' ]
Arguments

[@name_in_db =] 'security_account'

Is the name of a SQL Server user, Windows NT user, or database role in the current database. security_account must exist in the current database. security_account is sysname, with a default of NULL. If security_account is not specified, the system procedure reports on all users, Windows NT users, and roles in the current database. When specifying a Windows NT user, specify the name that the Windows NT user is known by in the database (added using sp_grantdbaccess).

Return Code Values
0 (success) or 1 (failure)

Remarks
Use sp_helpsrvrole and sp_helpsrvrolemember to return information about fixed server roles.

Executing sp_helpuser for a database role is equivalent to executing sp_helpgroup for that database role.

Permissions
Execute permissions default to the public role.