Ms SQL Server: Check to See If a User Can Execute a Stored Procedure

MS SQL Server: Check to see if a user can execute a stored procedure

fn_my_permissions and HAS_PERMS_BY_NAME

Finding stored procedures having execute permission

Use HAS_PERMS_BY_NAME:

select name, 
    has_perms_by_name(name, 'OBJECT', 'EXECUTE') as has_execute,
    has_perms_by_name(name, 'OBJECT', 'VIEW DEFINITION') as has_view_definition
from sys.procedures

Check if stored procedure is running

You might query sys.dm_exec_requests which will provide sesion_ID, waittime and futher rows of interest and CROSS APPLY sys.dm_exec_sql_text filtering your query with the SQL for your procedure.

Select * from
(
SELECT * FROM sys.dm_exec_requests 
where sql_handle is not null
) a 
CROSS APPLY  sys.dm_exec_sql_text(a.sql_handle) t 
where t.text like 'CREATE PROCEDURE dbo.sp_sleeping_beauty%'

How to find all users with execute rights on a stored procedure in SQL Server

Below query observed from How Can we find user have Execute Right on SP will help you get the required information (Not tested .. so minor tweak may be required).

SELECT
s.name AS SchemaName,
o.name AS ObjectName,
dp.name AS PrincipalName,
dperm.type AS PermissionType,
dperm.permission_name AS PermissionName,
dperm.state AS PermissionState,
dperm.state_desc AS PermissionStateDescription
FROM sys.objects o
INNER JOIN sys.schemas s on o.schema_id = s.schema_id
INNER JOIN sys.database_permissions dperm ON o.object_id = dperm.major_id
INNER JOIN sys.database_principals dp 
ON dperm.grantee_principal_id = dp.principal_id
WHERE
dperm.class = 1 --object or column
AND
dperm.type = 'EX'
AND 
dp.name = 'Specific_username'
AND
o.name = 'specific_object_name'

Find out who is executing the stored procedure from within said procedure

To achieve this you can use functions like

• SUSER_NAME() Function (it returns the login identification name of the user) MSDN article
• CURRENT_USER() (it returns the name of the current user) MSDN article
• USER_NAME() (it returns a database user name from a specified identification number) MSDN article
• ORIGINAL_LOGIN() (it Returns the name of the login that connected to the instance of SQL Server.) MSDN article

Example:

CREATE PROCEDURE
AS 
BEGIN
    DECLARE @executor 
    SELECT @executor = SUSERNAME()
    ...
END

You can read more in this very useful articles:

• Functions That Return User Names and User IDs

• Difference between ORIGINAL_LOGIN and SUSER_NAME

How do I determine if I have execute permissions on a DB programatically?

SQL 2005 and on you can check any permission with HAS_PERM_BY_NAME:

SELECT HAS_PERMS_BY_NAME('sp_foo', 'OBJECT', 'EXECUTE');

A reliable way to verify T-SQL stored procedures

You can choose different ways. First of all SQL SERVER 2008 supports dependencies which exist in DB inclusive dependencies of STORED PROCEDURE (see http://msdn.microsoft.com/en-us/library/bb677214%28v=SQL.100%29.aspx, http://msdn.microsoft.com/en-us/library/ms345449.aspx and http://msdn.microsoft.com/en-us/library/cc879246.aspx). You can use sys.sql_expression_dependencies and sys.dm_sql_referenced_entities to see and verify there.

But the most simple way to do verification of all STORED PROCEDURE is following:

1. export all STORED PROCEDURE
2. drop old existing STORED PROCEDURE
3. import just exported STORED PROCEDURE.

If you upgrade DB the existing Stored Procedure will be not verified, but if you create a new one, the procedure will be verified. So after exporting and exporting of all Stored Procedure you receive all existing error reported.

You can also see and export the code of a Stored Procedure with a code like following

SELECT definition
FROM sys.sql_modules
WHERE object_id = (OBJECT_ID(N'spMyStoredProcedure'))

UPDATED: To see objects (like tables and views) referenced by Stored Procedure spMyStoredProcedure you can use following:

SELECT OBJECT_NAME(referencing_id) AS referencing_entity_name 
    ,referenced_server_name AS server_name
    ,referenced_database_name AS database_name
    ,referenced_schema_name AS schema_name
    , referenced_entity_name
FROM sys.sql_expression_dependencies 
WHERE referencing_id = OBJECT_ID(N'spMyStoredProcedure');

UPDATED 2: In the comment to my answer Martin Smith suggested to use sys.sp_refreshsqlmodule instead of recreating a Stored Procedure. So with the code

SELECT 'EXEC sys.sp_refreshsqlmodule ''' + OBJECT_SCHEMA_NAME(object_id) +
              '.' + name + '''' FROM sys.objects WHERE type in (N'P', N'PC')

one receive a script, which can be used for verifying of Stored Procedure dependencies. The output will look like following (example with AdventureWorks2008):

EXEC sys.sp_refreshsqlmodule 'dbo.uspGetManagerEmployees'
EXEC sys.sp_refreshsqlmodule 'dbo.uspGetWhereUsedProductID'
EXEC sys.sp_refreshsqlmodule 'dbo.uspPrintError'
EXEC sys.sp_refreshsqlmodule 'HumanResources.uspUpdateEmployeeHireInfo'
EXEC sys.sp_refreshsqlmodule 'dbo.uspLogError'
EXEC sys.sp_refreshsqlmodule 'HumanResources.uspUpdateEmployeeLogin'
EXEC sys.sp_refreshsqlmodule 'HumanResources.uspUpdateEmployeePersonalInfo'
EXEC sys.sp_refreshsqlmodule 'dbo.uspSearchCandidateResumes'
EXEC sys.sp_refreshsqlmodule 'dbo.uspGetBillOfMaterials'
EXEC sys.sp_refreshsqlmodule 'dbo.uspGetEmployeeManagers'

---

Related Topics