SQL Server Performance

Execute permission on sp

Discussion in 'SQL Server 2005 General Developer Questions' started by rotsey, Aug 29, 2007.

  1. rotsey New Member

    I have a .Net 2.0 windows app that connects to a SQL ExpressDB.
    I have 2 Clients using it fine. It accesses stored procs toupdate data.
    The clients use integrated security.
    data source='SQLSERVERSQLEXPRESS';initial catalog=tbrPData;integrated security=SSPI
    The problem is on the third client
    I getExsecute perimission denied on sp message.
    It also uses integrated security.
    How do I find out what user this client is accessing the DB with so I look at sp permissions???
  2. ghemant Moderator

    ensure that the domain user connecting to server has proper privilege to access the database and their objects....also you need to post the error message so that you can get good answer.
  3. rotsey New Member

    How do I check what domain user the client is connecting as?????
  4. satya Moderator

    Have you checked permission for this 3rd user on that SP?
    If they are able to connect to SQL instance then it could be permission issue on the database object.
  5. rotsey New Member

    As post sasy they all connect using windows security
    so all are the same user connection to DB
    so sp permissions are the same
  6. rotsey New Member

    What I was trying to say in previous post is that I
    use the same connection string for all clients.
    So I expected that they all were connecting to the DB
    as the same user.
    Isn't that the case when you use intergated security??????
    Shouldn't the permissions be same then for that user?
  7. Adriaan New Member

    "Integrated security" means that the user authentication is not done by SQL Server, but by the Windows domain. Within SQL Server, you then assign permissions to Windows domain users and/or domain groups.
    Alternatively, you can use SQL Server logins, which have nothing to do with the domain accounts.
  8. Jack Vamvas Member

    use sql server authentication, and check to see that user has execute permissions on the sp
  9. rotsey New Member

    ok thats all fine
    But I ask again if they all are using the same integrated security
    aren't they all using the same DB user and have the same permissions.??
    Because all the clients work and I did not have to set permissions for them
  10. martins New Member

    The answer to your question is no. If your connection string does not specify a username and a password, then Windows Authentication will be used. This means that there should be a login in SQL Server for each of their domain accounts (or whatever they use to log into Windows).
    My suggestion would be to check your SQL logins first and see if there are any diferences between the users you mention. If you still do not get joy out of this it would be best to run a trace while your specific user is accessing the system to see exactly who they are accessing the server as and what they are executing when the error is thrown.
  11. Adriaan New Member

    [quote user="rotsey"] ok thats all fine
    But I ask again if they all are using the same integrated security
    aren't they all using the same DB user and have the same permissions.??
    Because all the clients work and I did not have to set permissions for them[/quote]There is no such thing as "the same integrated security" - the connection initialized from the client application either uses integrated security, or it does not. Since you have "integrated security = SSIP" on your connect string, the application is connecting with integrated security.
    This means you're dealing with the credentials of the Windows account under which the application is running. If you have a client application running on a desktop, the credentials will probably be those of the Windows user. If it is a web application, it will be the credentials for the account under which the web service is running on the web server.
    You will find that SQL Server identifies Windows accounts and/or groups (domain and/or local) who have access to databases, under specific server and/or database roles.
    Finally, on the properties for the sp, you can see that permissions can be assigned to specific Windows accounts and/or groups, and/or to specific server and/or database roles. A permission can be granted or denied, directly to an account, or to a (server or database) role. A permission can also be left "ungranted".
    The difference between "not granted" and "denied" is that a denied permission will overrule a permission that is granted directly to the account, or through another role to which the account belongs. As long as the ungranted permission is not denied one way, it can still be granted another way.
    The db_owner and system_administrator roles automatically have full permissions on any object.
  12. rotsey New Member

    well it must be a windows group because the other clients
    work fine
    So how do I find out what windows group they are??
    In the activity monitor it show the individual users like this
    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?
  13. ghemant Moderator

    You may create a role and map all that users to newly created role and then assign proper permission to that particular role
    or in another word create a windows group on your AD and map/add all those user to windows group (who needs to access your database) and then grant then access to your sql server and your database and grant relavent permisson i.e. db_databreader is a fixed database role
    suggest to refer http://www.developer.com/tech/article.php/721441 which will clear your doubts on windows authentication model and how it works
  14. satya Moderator

    Can you exactly specify what is the error on that machine?
    Also check whether similar levels of MDAC & Service pack levels are installed between these machines.

Share This Page