How come I can create SQL Server objects but not access these objects

By: K. Brian Kelley   |   Updated: 2014-04-09   |   Comments   |   Related: > Security

Problem

In a SQL Server database I was able to create a stored procedure. However, when I went to execute the stored procedure, SQL Server told me I didn't have permission to do so. Is this a bug or did I do something wrong?

Solution

This is not a bug. Likely you have permission to create stored procedures, but you don't have permission to execute the stored procedure. This is entirely possible if you have the ability to create a stored procedure in a schema you don't own. To see this in action, let's setup a test:

USE master;
GO 
IF EXISTS(SELECT name FROM sys.databases WHERE name = 'TestDB')
DROP DATABASE TestDB;
GO
CREATE DATABASE TestDB;
GO 
IF EXISTS(SELECT name FROM sys.sql_logins WHERE name = 'TestUser')
DROP LOGIN TestUser;
GO 
CREATE LOGIN TestUser WITH PASSWORD = 'S0m3Str0ngP4ssw0rd!';
GO 
USE TestDB;
GO 
CREATE ROLE Object_Creator;
GO 
GRANT ALTER ON SCHEMA::dbo TO Object_Creator;
GRANT CREATE PROC TO Object_Creator;
GO 
CREATE USER TestUser;
GO 
EXEC sp_addrolemember @membername = 'TestUser', @rolename = 'Object_Creator';
GO 

In this particular case we've created a role that has the ability to create stored procedures in the database and the ability to alter the dbo schema. By default, the dbo schema is owned by the database owner. Therefore, the TestUser doesn't own the dbo schema. Alter rights give permissions to create, change, and drop objects within the dbo schema, but it doesn't give SELECT access against tables or views in the dbo schema nor does it give EXECUTE permissions against stored procedures.

We can test this using the following script:

USE TestDB;
GO 
EXECUTE AS LOGIN = 'TestUser';
GO 
IF EXISTS(SELECT name FROM sys.procedures WHERE name = 'TestProc')
DROP PROC dbo.TestProc;
GO
CREATE PROC dbo.TestProc
AS
BEGIN
SELECT 123;
END;
GO 
REVERT;
GO 
EXECUTE AS LOGIN = 'TestUser';
GO 
EXEC dbo.TestProc;
GO 
REVERT;
GO 

If we execute just the CREATE PROC code (as TestUser), we are able to do so successfully:

However, as soon as we attempt to execute the stored procedure as TestUser, we get the permission error:

This is also true if TestUser is a member of the db_ddladmin fixed role. This role has the ability to create, alter, and drop any object in any schema. However, the role does not give permission to access the objects. Run the following script to change the membership for TestUser and then re-execute the test code. You'll see the same result.

USE TestDB;
GO
EXEC sp_droprolemember @membername = 'TestUser', @rolename = 'Object_Creator';
GO 
EXEC sp_addrolemember @membername = 'TestUser', @rolename = 'db_ddladmin';
GO 

Next Steps

• Learn what the various pre-defined database roles are in SQL Server.
• Understand how ownership chaining and schemas work for security.
• Read how to break ownership chaining within the same schema.
• Understand how you can use a DDL trigger to prevent object changes.

About the author

K. Brian Kelley is a SQL Server author and columnist focusing primarily on SQL Server security.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips