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?