0

I have a requirement to give a functionality for a non technical user to run the Agent job / stored procedure. It's just one stored procedure.

I do not want to the user to install SQL Server, nor do I want to give him any additional permissions. Indirect permissions (proxy or running through a server) are fine as long as the user won't be able to see the password.

I believe Osql does this but then I have to type username n password in batch file and that compromises security.

Is there any other way? Can this be achieved with SSRS?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Shay
  • 71
  • 5

3 Answers3

1

To cut through you can script SP execution with Powershell and use integrated security to authenticate user on SQL server. Check this thread how to do that.

Please keep in mind that giving direct access to SQL server is a bad idea from security perspective, so you must limit its permissions with a specific role and grant execution only for that particular procedure.

fenixil
  • 2,106
  • 7
  • 13
  • Ok so does the person who runs need to be given any permission? Should we create that user in SQL server and give access to that one stored proc alone? And how to make running power shell script easier? Because he is non technical. I believe he just wants to double click on a file and boom the stored proc runs. – Shay Aug 28 '19 at 21:37
  • @Shay yes, you need to [create a login](https://docs.microsoft.com/en-us/sql/relational-databases/security/authentication-access/create-a-login?view=sql-server-2017) from windows principal and [grant execution](https://dba.stackexchange.com/questions/19749/how-do-you-grant-execute-permission-for-a-single-stored-procedure) to that SP. You won't be able to run PS script with doubleclick, you need .bat file for that. You can base64 encode you script and use [EncodedCommand](https://blogs.msdn.microsoft.com/timid/2014/03/26/powershell-encodedcommand-and-round-trips/) flag to execute it from bat. – fenixil Aug 29 '19 at 02:04
1

Alternative to @Illia way is to create a simple web-app with (say) .net. It's probably less than 15 mins task for a .net developer to connect to SQL server and provide a button to execute one or more stored procedures. You can manage the access via integrated security of the app specific user id and password and is probably more easy for a non-tech user. See here.

Sukhi
  • 13,261
  • 7
  • 36
  • 53
  • Yes, I know this method. Create an application and call the store proc through a button. But then am not a .net developer. I want to solve this in a DBA, database developer perspective. This was my first thought, but am scared of .net lol. Can I learn this and implement it by myself? (I do have a computer science background and know oops concept but mvc Is new to me) – Shay Aug 28 '19 at 21:20
  • Why not ? It's fairly easy and there are tons of videos on YouTube. Plus, you will find the code readily available on the internet. Good luck ! – Sukhi Aug 29 '19 at 03:47
0

I was finally able to achieve with less overhead using a SSRS report. Created a report to run the Stored proc whenever the report is opened. This way I was able to achieve the security part too. Thanks for the answers everyone.

Shay
  • 71
  • 5