SQL Server Performance

sp_executesql permission issue

Discussion in 'SQL Server 2005 General DBA Questions' started by LaFilipina, Aug 8, 2008.

  1. LaFilipina New Member

    Hi All,I am very new to SQL 2005 and we have just recently upgraded on of our servers from 2000 to 2005. As we are doing application testing, we are getting this error:
    {"The EXECUTE permission was denied on the object 'sp_executesql', database 'mssqlsystemresource', schema 'sys'." }
    Here's the background of the problem:

    1. User executing the statement is USER1
    2. Database is MYDB
    3. Problem statement:
    Declare @cmd nvarchar(500)
    Set @cmd = 'SELECT * FROM mytable WHERE id ='+char(39)+'22010-123456'+char(39)+';'
    print @cmd
    exec sp_executesql @cmd
    4. USER1 has db_datareader, db_datawriter and execute_role on database MYDB
    5. I tried explictly granting execute permission to USER1 on sp_executesql located on master.. .but still getting the same error above. I also tried to explicitly grant select permission on mytalbe to USER1 but no avail.
    What am I missing? Looking forward to hearing from you guys!
    Thanks,
    LaFlipina
  2. amitadmin New Member

    Hi Paul,
    Please try the folowing steps:
    1. Create Role ProcExec
    2. Grant Execute to ProcExec
    3. Add User1 in ProcExce Role as a member
    I believe the above steps will resolve this issue or Give to user as a db_owner permission.
    Please reply if you found any solution.
  3. LaFilipina New Member

    Hi Amit,
    Thanks, for the reply. We have other SQL2k5 servers, so I tried to replicate the scenario in there and surprisingly, I didn't get the same error. I tried to compare settings and permissions between the two SQL Servers and I found one difference. In master database, the public role has a denied permission on sp_executesql on the server where I am encountering the problem.
    So I just deselected the deny checkbos and selected grant on the execute checkbox for execute permission. This is the setting anyways on the other SQL 2K5. I assume that this is the default settings.
    What are your thoughts on this? Is this the best practice?
    Thanks again,
    La Filipina.
  4. satya Moderator

    Due to the trustworthy settings such permissions on the SQL are set to DENY by default, in this case you can execute:
    select object_name(major_id) as object,
    user_name(grantee_principal_id) as grantee,
    user_name(grantor_principal_id) as grantor,
    permission_name,
    state_desc
    from sys.database_permissions
    where major_id = object_id('Users')
    and class = 1
    This script will provide the information on granted permissions on the database. If there are permissions that are denied on the table, check what is the principlal to whom the permission is denied (the grantee).
  5. LaFilipina New Member

    Hi Satya,

    It's kind of odd because out of our 4 SQL Server 2005 instances, only one instance seems to have public execute permission as denied on sp_executesql. So I am not sure if that is indeed the default setting.
    If I am going to keep the denied permission for public user on sp_executesql, I am not sure how to securely deal with our application users that actually need execute permission on sp_executesql via stored procedures and .net core components.
    I don't want to grant access on master database to individual application users just to give them execute access on sp_executesql. Any suggestion on this?
    Thanks,
    LaFilipina
  6. satya Moderator

    Do you have specific users to grant or do you manage with groups on SQL access?
  7. LaFilipina New Member

    we have specific users to grant.
  8. satya Moderator

    If those users can only access or used within the applicatin to execute this code, then you could build a script to GRANT relevant permissions to them.
  9. LaFilipina New Member

    So that means, If I have like 50 application users from different databases within the same SQL instance, I would need to grant all of them an access to master database and an execute permission to sp_executesql?
  10. satya Moderator

  11. LaFilipina New Member

    Thanks a lot, Satya! That article helps a lot.

Share This Page