Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 326
  • Last Modified:

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

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
wantime
Asked:
wantime
  • 2
2 Solutions
 
ZberteocCommented:
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
 
wantimeAuthor Commented:
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
 
mrcoffee365Commented:
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
 
wantimeAuthor Commented:
thanks, it helps.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now