Solved

mssql and jdbc: how do i check the privileges of users and give the result to java code

Posted on 2011-09-20
6
313 Views
Last Modified: 2012-06-22
hello all,

in this question i have resolved the privileges problem in mysql. now i would like to resolve the same problem  in mssql 2005.

http://www.experts-exchange.com/Programming/Languages/Java/Q_27316322.html

i would like to know, how do one check the  privileges of  mssql database users and give the result to java code.

thanks,

wantime
0
Comment
Question by:wantime
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
6 Comments
 
LVL 27

Assisted Solution

by:Zberteoc
Zberteoc earned 200 total points
ID: 36567260
Try the query below. The way to get the results in Java would be the same as for MySQL. Just change the connection to the MS-SQL server and adapt your code for the new select statement.
select 
	sys.schemas.name 'Schema', 
	sys.objects.name Object, 
	sys.database_principals.name username, 
	sys.database_permissions.type permissions_type,
	sys.database_permissions.permission_name,
	sys.database_permissions.state permission_state,
	sys.database_permissions.state_desc,
	state_desc + ' ' + permission_name + ' on ['+ sys.schemas.name + '].[' + sys.objects.name + '] to [' + sys.database_principals.name + ']' COLLATE LATIN1_General_CI_AS
from 
	sys.database_permissions
	join sys.objects 
		on sys.database_permissions.major_id = sys.objects.object_id
	join sys.schemas 
		on sys.objects.schema_id = sys.schemas.schema_id
	join sys.database_principals 
		on sys.database_permissions.grantee_principal_id = sys.database_principals.principal_id
order by 
	1, 2, 3, 5

Open in new window

0
 

Author Comment

by:wantime
ID: 36572104
thanks you for you examples.

but the returned value what i needed was actually a boolean type.

i.e., i would like to find out if a user "myuser" has "DROP Privelage", or if he has "CREATE Privelage" etc.
0
 
LVL 27

Accepted Solution

by:
mrcoffee365 earned 300 total points
ID: 36602437
There are several queries to SQL Server which give you what you're looking for:

Return Caller's permission on the current server
SELECT * FROM fn_my_permissions(NULL, 'SERVER')

Return Caller's permission on the current database
SELECT * FROM fn_my_permissions(NULL, 'DATABASE')


Return Caller's permission on the given object
SELECT * FROM fn_my_permissions('Sales.Company', 'OBJECT')

See this page for this info:
http://sqltips.wordpress.com/2007/05/28/retreive-current-user-permissions-in-sql-server-2005/
0
 

Author Comment

by:wantime
ID: 36709862
thanks, it helps.
0

Featured Post

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this post we will learn how to connect and configure Android Device (Smartphone etc.) with Android Studio. After that we will run a simple Hello World Program.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
This theoretical tutorial explains exceptions, reasons for exceptions, different categories of exception and exception hierarchy.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

623 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question