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 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy