XML FeedsSQL 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
Denis has been working with SQL Server since version 6.5. Although he worked as an ASP/JSP/ColdFusion developer before the dot com bust, he has been working exclusively as a database developer/architect since 2002. In addition to English, Denis is also fluent in Croatian and Dutch, but he can curse in many other languages and dialects (just ask the SQL optimizer) He lives in Princeton, NJ with his wife and three kids.
Interesting, I did not know this..thanks 
I actually ran into this today as well. I saw the lock and figured out after some research that these procs were part of a Service Broker Queue Activation. Would this fall under the CLR pieces you described above?
LTD Social Sitings
Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.