0

When I log in to SQL SERVER with MS SQL Management Studio with user xyz and password letmein to server myserver, instance myinstance, port 1111 and database mydatabase then I have this list of permissions:
SELECT * FROM fn_my_permissions (NULL, 'DATABASE')

CREATE TABLE
CREATE VIEW
CREATE PROCEDURE
CREATE FUNCTION
CREATE RULE
CREATE DEFAULT
BACKUP DATABASE
BACKUP LOG
CREATE TYPE
CREATE ASSEMBLY
CREATE XML SCHEMA COLLECTION
CREATE SCHEMA
CREATE SYNONYM
CREATE AGGREGATE
CREATE ROLE
CREATE MESSAGE TYPE
CREATE SERVICE
CREATE CONTRACT
CREATE REMOTE SERVICE BINDING
CREATE ROUTE
CREATE QUEUE
CREATE SYMMETRIC KEY
CREATE ASYMMETRIC KEY
CREATE FULLTEXT CATALOG
CREATE CERTIFICATE
CREATE DATABASE DDL EVENT NOTIFICATION
CONNECT
CONNECT REPLICATION
CHECKPOINT
SUBSCRIBE QUERY NOTIFICATIONS
AUTHENTICATE
SHOWPLAN
ALTER ANY USER
ALTER ANY ROLE
ALTER ANY APPLICATION ROLE
ALTER ANY SCHEMA
ALTER ANY ASSEMBLY
ALTER ANY DATASPACE
ALTER ANY MESSAGE TYPE
ALTER ANY CONTRACT
ALTER ANY SERVICE
ALTER ANY REMOTE SERVICE BINDING
ALTER ANY ROUTE
ALTER ANY FULLTEXT CATALOG
ALTER ANY SYMMETRIC KEY
ALTER ANY ASYMMETRIC KEY
ALTER ANY CERTIFICATE
SELECT
INSERT
UPDATE
DELETE
REFERENCES
EXECUTE
ALTER ANY DATABASE DDL TRIGGER
ALTER ANY DATABASE EVENT NOTIFICATION
ALTER ANY DATABASE AUDIT
VIEW DATABASE STATE
VIEW DEFINITION
TAKE OWNERSHIP
ALTER
CONTROL

it's ok, I can create data tables but when I do it through jdbc with this configuration: 

@Bean(name="dataSource")
public DataSource dataSource() throws SQLException {
    DriverManagerDataSource dataSource = new DriverManagerDataSource();
    dataSource.setDriverClassName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
    dataSource.setUrl("jdbc:sqlserver://myserver\\myinstance:1111;databaseName=mydatabase");
    dataSource.setUsername("xyz");
    dataSource.setPassword("letmein");
    return dataSource;
}

I get this list of perrmissions: 

CONNECT
SELECT
INSERT
UPDATE
DELETE
EXECUTE

and can't create tables. Does anybody have an idea whas can be the reason?

stakowerflol
  • 979
  • 1
  • 11
  • 20
  • Add to your code SELECT USER, SYSTEM_USER and see under what user the code is actually executed – sepupic Oct 31 '18 at 09:07
  • how to do that? `SELECT a.*, b.user, b.system_user FROM fn_my_permissions a join ??? on ??? (NULL, 'DATABASE')` – stakowerflol Oct 31 '18 at 09:08
  • Why do you want to use any join? Just execute this select as any other select – sepupic Oct 31 '18 at 09:09
  • so run `SELECT * FROM fn_my_permissions (NULL, 'DATABASE')` ? When I runt this I get only 3 collumns: entity_name, subbentity_name and permission_name. – stakowerflol Oct 31 '18 at 09:11
  • so run `SELECT USER, SYSTEM_USER` instead of running `SELECT * FROM fn_my_permissions (NULL, 'DATABASE')` – sepupic Oct 31 '18 at 09:12
  • there is my windows account. How? I log with `myyser` and get `mywindowsuser` – stakowerflol Oct 31 '18 at 09:33
  • You specified integratedSecurity=true that means you want to pass in Windows account of the person who executes your code – sepupic Oct 31 '18 at 09:35

1 Answers1

0

OK I solved it according to this solution:

no sqljdbc_auth in java.library.path

I just need to add integratedSecurity=true to the data source url and "copy the sqljdbc_auth.dll to C:\Program Files\Java\jre_Version\bin"

stakowerflol
  • 979
  • 1
  • 11
  • 20