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
304 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
  • 2
6 Comments
 
LVL 26

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 26

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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Java Flight Recorder and Java Mission Control together create a complete tool chain to continuously collect low level and detailed runtime information enabling after-the-fact incident analysis. Java Flight Recorder is a profiling and event collectio…
Introduction This article is the second of three articles that explain why and how the Experts Exchange QA Team does test automation for our web site. This article covers the basic installation and configuration of the test automation tools used by…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
This theoretical tutorial explains exceptions, reasons for exceptions, different categories of exception and exception hierarchy.

707 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now