SQL sp execute perimmsion

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Rotsey - 30 Aug 2007 05:01 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

Alberto Poblacion - 30 Aug 2007 07:25 GMT

> The clients use integrated security.
>
[quoted text clipped - 6 lines]
>
> How do I find out what user this client is accessing the DB with???

If you are using integrated security, the user that is used to access the
database is the same one that is logged in to Windows and executes the
program.
If you need to find who the user is from the point of view of the Sql
Server, download from Microsoft "Sql Server Management Studio Express". Run
it and connect to your server, and in Object Explorer expand the node
"Management" and then right-click on "Activity Monitor" and select "View
Processes". You will see all the connections to your server, and one of the
columns shows the user that opened each connection.

Reply to this Message

Marc Gravell - 30 Aug 2007 07:54 GMT

> If you need to find who the user is from the point of view of the
> Sql Server...

or just (from any sql console) exec sp_who / sp_who2 while the client
is connected...

Marc

Reply to this Message

Ignacio Machin ( .NET/ C# MVP ) - 30 Aug 2007 15:48 GMT

Hi,

> Hi,
>
[quoted text clipped - 16 lines]
>
> How do I find out what user this client is accessing the DB with???

Install the management studio, there you will have a profiler that shows you
the actions in the DB, What kind of security you have in your DB?

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,
>
[quoted text clipped - 21 lines]
> Install the management studio, there you will have a profiler that shows
> you the actions in the DB, What kind of security you have in your DB?

Reply to this Message

Alberto Poblacion - 31 Aug 2007 08:57 GMT

> well it must be a windows group because the other clients
> work fine
[quoted text clipped - 11 lines]
> I had to explicitly add execute permissions to Lester/RebeccaC
> but not the others??

This means that the other users were already members of a Windows Group
that was mapped to a Sql Server login with adequate permissions, while
RebeccaC wasn't. The easiest way that this could have happened would be that
the three first users were Administrators, which the default configuration
of Sql Server maps to dbo in every database.
You can examine the Logins in Sql Server to see if any Windows Group
other than Administrators has been granted a Login. If you find such a
group, you can then go back to Windows to see which users are members of
that group.

Reply to this Message

SQL sp execute perimmsion

Free Magazines