NOTE: I have checked this question and answer and it did not answer my use case: Permission for querying dbo.sysobjects
I have the following stored procedure to create a sequence if it does not exist:
ALTER PROCEDURE seq.CreateSequenceIfNotExists
@FullSequenceName nvarchar(500)
WITH EXECUTE AS 'SequenceCreator'
AS
BEGIN
SET NOCOUNT ON;
IF (NOT EXISTS (SELECT 1
FROM sys.objects
WHERE object_id = OBJECT_ID(@FullSequenceName) AND type = 'SO'))
BEGIN
DECLARE @SequenceCreateSql NVARCHAR(700)
SET @SequenceCreateSql = CONCAT('CREATE SEQUENCE ', @FullSequenceName, 'START WITH 1 INCREMENT BY 1 NO CACHE')
EXEC sp_executesql @stmnt = @SequenceCreateSql
END
END
GO
The user it runs as (SequenceCreator) has ONLY the following permission:
GRANT CREATE SEQUENCE ON SCHEMA::seq TO SequenceCreator
GO
When this stored procedure runs, it works fine to create the sequence. But when I run it a second time, the query to sys.objects returns no rows and it tries to create a the sequence again (it returns one row when I run it as dbo (as expected)).
I assume that the user SequenceCreator does not have the permissions to query this row in sys.objects.
What are the minimum permissions needed to allow this user to check for the existence of sequences that it creates?
(NOTE: I need it to be the "minimum permissions", because I cannot parameterize the name of the sequence, so it could be hit with a SQL injection attack. Though unlikely in my use case, I need the user to not have any real permissions to exploit)
