0

I have a user that is used by a program called TicketMonitor, which is supposed to use an user-defined CLR aggregate function in a SELECT, however, it appears to not have the permissions to do so.

pyodbc.ProgrammingError: ('42000', "[42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Die dbo-Spalte oder die benutzerdefinierte Funktion bzw. das benutzerdefinierte Aggregat 'dbo.STR_AGG' wurde nicht gefunden, oder der Name ist mehrdeutig. (4121) (SQLExecDirectW); [42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Anweisung(en) konnte(n) nicht vorbereitet werden. (8180)")

Which roughly translates to command not found. However when I take the exact same statement and put it into the Management Studio application and run it as Admin, it does work, so that leads me to user rights.

When I tried granting execute permission to TicketMonitor, it could not find the user, maybe because it is a Database user and the function is global?

Improve this question
7
  • The fact that the functoin is called dbo.STR_AGG strongly implies your function is in a database, on the dbo schema. Is the function in a different database perhaps? If so, the login that the user is linked to will need a user created in the other database and given permission to use the function. Commented Nov 13, 2018 at 14:50
  • I think you are probably trying to grant execute permission to the wrong username. Remember, the "login" that is used to connect to the database might not carry the same name as the "user" in the database mapped to that login... How did you find the "TicketMonitor" user name? Commented Nov 13, 2018 at 14:56
  • @Larnu Yes, the function appears attached to another Database, as the program's user only appears in the Database where the Data is located and there it is listed with name "TicketMonitor" and type "Benutzer" (User), however I know it is not a Windows User. I don't think I ever specified a Database for the function, but the Assembly appears under "master". Commented Nov 13, 2018 at 15:35
  • The user will be linked to a login, what is the login? Commented Nov 13, 2018 at 15:36
  • If you didn't specify a database when you created it, then you would have created it in whatever database you were connected to (appears master here). Should the function be in master though? Commented Nov 13, 2018 at 15:37
Related questions
2
SQL CLR function and OleDb permissions
4
TSQL - Executing CLR Permission
2
CLR function calls a remote SQL Server
Load 7 more related questions Show fewer related questions

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.