-1

I have written a program in VB.NET, which connects to one of our SQL Server. The connection is needed, because on the database are some stored procedure, which have to be execute.

But the problem is, that I can't run any stored procedure. I think the reason of the problem is, that the user doesn't have permissions to run any stored procedure on the database.

I have create the database on the SQL Server without any permission configuration.

The connections string on my program is like: 

Data Source=SQL01;Initial Catalog=DB;Integrated Security=True

My clients, which use my program are domain users.

Now my question: is there a possibility that I can define a this Active Directory user, which should have permission to execute any store procedure on to the database?

Thank you!

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Bk_
  • 99
  • 9
  • If this is a programming problem then post the relevant code and the error message, if it is a server administration problem then you are off-topic here (serverfault.com) – Steve Jan 08 '14 at 10:15
  • So the question is really: How do I impersonate the user that executes a stored procedure? Check http://stackoverflow.com/questions/8439517/connecting-to-a-database-using-windows-authentication-with-different-credentials – Panagiotis Kanavos Jan 08 '14 at 10:16
  • BTW WHY do you want this to run as another user? Integrated Security ensures the connection is made with the client's credentials – Panagiotis Kanavos Jan 08 '14 at 10:19
  • @PanagiotisKanavos Good question. Yes, but where should I define on the database, if I want allow that XYZ client have permisson to run a store procedure? – Bk_ Jan 08 '14 at 10:24

1 Answers1

1
USE myDB;
GO

CREATE ROLE newrole;
GO

GRANT EXECUTE ON dbo.myStoredProc TO newrole ;
GO

-- or if you want to grant execute to an entire schema.
GRANT EXEC ON SCHEMA::schema_name TO role_name

Then add AD group/users to role (you will need to create a login for the AD user/group)

Ref.: GRANT

Mitch Wheat
  • 295,962
  • 43
  • 465
  • 541
  • For those that know SQL Server's security model this is not a bad idea. For those that don't it's rather unclear how this relates to the original question - or why it is preferable to impersonation – Panagiotis Kanavos Jan 08 '14 at 10:18
  • 2
    well in order to implement security correctly it's sort of a pre-requisite to understand SQL Server's security model. Also it is still appropriate/applicable to impersonation. – Mitch Wheat Jan 08 '14 at 10:19
  • @MitchWheat Thank you for your answer. Should I run the last command also? GRANT EXEC ON SCHEMA::schema_name TO role_name? What kind of impact does this command have? – Bk_ Jan 08 '14 at 10:51
  • the last statement is just an example of granting execute to an entire schema. – Mitch Wheat Jan 08 '14 at 12:07
  • @MitchWheat Mhh ah ok.. Now I have execute all the commands without the last one. And now if I run my programm, there is follwoing Error Message» CREATE TABLE permission denied in 'db'. Do you know why? – Bk_ Jan 08 '14 at 14:19