Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
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
Medium Priority
?
325 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
4 Comments
 
LVL 27

Assisted Solution

by:Zberteoc
Zberteoc earned 800 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 1200 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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
This video teaches viewers about errors in exception handling.
This tutorial will introduce the viewer to VisualVM for the Java platform application. This video explains an example program and covers the Overview, Monitor, and Heap Dump tabs.
Suggested Courses

571 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