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
310 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 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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Server / Update DB? 22 38
Update one table with results from another table in SQL 6 29
passing enum to a method 4 23
SQL 2012 clustering 9 13
Basic understanding on "OO- Object Orientation" is needed for designing a logical solution to solve a problem. Basic OOAD is a prerequisite for a coder to ensure that they follow the basic design of OO. This would help developers to understand the b…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
This tutorial covers a practical example of lazy loading technique and early loading technique in a Singleton Design Pattern.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

820 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