sp execute permission

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Rotsey - 30 Aug 2007 05:02 GMT

Hi,

I have a .Net 2.0 windows app that connects to a SQL Express
DB.

I have 2 Clients using it fine. It accesses stored procs to
update data.

The clients use integrated security.

data source='SQLSERVER\SQLEXPRESS';initial catalog=tbrPData;integrated
security=SSPI

The problem is on the third client I get

Exsecute perimission denied on sp message.

It also uses integrated security.

How do I find out what user this client is accessing the DB with???

rotsey

Reply to this Message

Ekrem Önsoy - 30 Aug 2007 09:43 GMT

Hi Rotsey,

As you mentioned you make your users connect to SQL Server in Integrated
Security mode, then your users' windows domain accounts are used for login
to SQL Server as Windows Authentication. If that user is in a different
Domain\Global Group which is restricted to connect to SQL Server or do not
have permission for SQL Server login then he\she cannot connect to SQL
Server.

I suggest you to set up a Windows Global Group and put your .Net 2.0 app
users in to that group and give that group the necessary permissions in SQL
Server login so that they are going to be able to reach resources as they
need. So, you will not bother yourself with individual accounts.

Signature

Ekrem Önsoy

> Hi,
>
[quoted text clipped - 18 lines]
>
> rotsey

Reply to this Message

Rotsey - 31 Aug 2007 07:10 GMT

well it must be a windows group because the other clients
work fine

So how do I find out what windows group there are??

In the activity monitor it show the individual users like this

Lester/Marlene
Lester/Bayu
Lester/Suzanne
Lester/RebeccaC

But I have not setup permissions for them to my sp.
I had to explicitly add execute permissions to Lester/RebeccaC
but not the others??

So how do they work?

> Hi Rotsey,
>
[quoted text clipped - 32 lines]
>>
>> rotsey

Reply to this Message

Ekrem Önsoy - 30 Aug 2007 21:37 GMT

Connect to your SQL Instance from SQL Server Management Studio and go to
Security\Logins and see what users and groups have right to login to your
SQL Server Instance. You can also view their right by observing Logins'
properties.

I again suggest you the following:
Make a list of the users who are supposed to execute your sp and create a
Windows Global Group this these users. And create a Login for this group
from SQL Server Management Studio (SSMS) and set the mapping up for this
group from SSMS.

Check out the current Logins from SSMS and if there is a group (like
Lester\Domain Users) which you do not want them to connect to your SQL
Server or execute your sp then explicitly deny them. So that only a group of
users will be able to use your database or whatever you want the to use.

Signature

Ekrem Önsoy

> well it must be a windows group because the other clients
> work fine
[quoted text clipped - 50 lines]
>>>
>>> rotsey

Reply to this Message