April 29, 2014 at 2:56 am
Hi
On a DB role I created i am getting ALTER permission denied on Sequences, I am not getting anything like that in the permissions articles on various websites.
GRANT EXECUTE, SELECT,DELETE, INSERT,UPDATE, REFERENCES, VIEW DEFINITION, CREATE SEQUENCE
ON SCHEMA ::dbo
TO [User]
Go
April 29, 2014 at 3:16 am
Just some info, the issue is that I need to grant Alter permission to sequences, at this stage it looks like the only way is to grant alter on each sequence individually. I need another way as we have a huge number of sequences and more get added frequently
April 29, 2014 at 5:50 am
For now it seems the solution is to create a Cursor which updates the user profile. Please, if there is some other way, respond. Here is the code I used.
DECLARE @name VARCHAR(100)
DECLARE @CMDEXEC1 nvarchar(2000)
DECLARE Role CURSOR FOR
select name from sys.sequences
OPEN Role
FETCH NEXT FROM Role INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
SET @CMDEXEC1 = 'GRANT ALTER ON [' + @name + '] TO User;'
--- SELECT @CMDEXEC1
EXEC(@CMDEXEC1)
FETCH NEXT FROM Role INTO @name
END
CLOSE Role
DEALLOCATE Role
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply