mssqltips logo

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

By:   |   Updated: 2014-04-09   |   Comments   |   Related: More > 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


Last Updated: 2014-04-09


get scripts

next tip button



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

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.






download

























get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools