SQL Server Management Studio will sometimes show a lock next to a stored procedure, there are several reasons why this might happen.

The procedure is encrypted

The procedure is a CLR stored procedure

The user doesn’t have the view definition permission for a stored proc

Let’s take a look at how this all works.

I will first create this database with two stored procedures, one is encrypted, the other one is not

CREATE DATABASE bla
GO

USE bla
GO

CREATE PROCEDURE prTest
AS
SELECT GETDATE()
GO

CREATE PROCEDURE prTestEncrypted WITH ENCRYPTION
AS
SELECT GETDATE()
GO

Now when I navigate to the Stored Procedures folder I see the following

As you can see there is a lock on the encrypted stored procedure

Now, let’s create a new user with data reader and writer permissions

USE [master]
GO
CREATE LOGIN [test] WITH PASSWORD=N'test', DEFAULT_DATABASE=[bla], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
USE [bla]
GO
CREATE USER [test] FOR LOGIN [test]
GO
USE [bla]
GO
EXEC sp_addrolemember N'db_datareader', N'test'
GO
USE [bla]
GO
EXEC sp_addrolemember N'db_datawriter', N'test'
GO

When I now login as that user I don’t see anything under the stored procedures folder.

In order to see the procedures, the user has to have execute permissions.

If I give execute permissions to the stored procedures for the user like this, the user should see them.

GRANT EXECUTE ON prTestEncrypted TO test
GRANT EXECUTE ON prTest TO test

The user will now see both procs with a lock on them

If I grant view definition to the user….

GRANT VIEW DEFINITION ON prTestEncrypted TO test
GRANT VIEW DEFINITION ON prTest TO test

The user will now see the lock only on the encrypted stored procedures, just like if he/she was a db owner

I think there should be different lock icons in SSMS, this way you will now if the proc is encrypted or that you don’t have view definition permissions…what do you think?

*** Remember, if you have a SQL related question, try our Microsoft SQL Server Programming forum or our Microsoft SQL Server Admin forum