Permissions on views

Question

Post reply

Permissions on views

Swagger187

Valued Member

Points: 59
More actions
November 25, 2011 at 4:05 am

#260593

Hello,
The company I work for are running SQL Server 2005. We have some views setup but they keep losing there permissions. So we are having to assign permissions time after time which as you can probably imagine is not ideal.
Is there anybody who can help with this and maybe help me understand why this is a) happening in the first place and b) a potential fix/work-a-round.
Many thanks,

Viewing 9 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

spaghettidba SSC Guru Points: 105732 More actions · Answer 1

Permissions don't "get lost", but may be revoked by something:

1) Explicit REVOKE commands

2) Drop / re-create view

Personally, I would investigate the drop/re-create as the most likely cause, then I would modify the create statement to include the GRANT commands needed or change it to an ALTER VIEW.

Hope this helps

Gianluca

-- Gianluca Sartori

Swagger187 Valued Member Points: 59 More actions · Answer 2

Ok brilliant thanks for your reply. I will have a look into this and report back.

Swagger187 Valued Member Points: 59 More actions · Answer 3

Gianluca Sartori (11/25/2011)
Permissions don't "get lost", but may be revoked by something:
1) Explicit REVOKE commands
2) Drop / re-create view
Personally, I would investigate the drop/re-create as the most likely cause, then I would modify the create statement to include the GRANT commands needed or change it to an ALTER VIEW.
Hope this helps
Gianluca

Gianluca,

I have spent some time looking into this and myself and my manager both don't think this is the cause tho we could be wrong. Another thing I should mention is we created a command so that every saturday the server rebuilds the indexes.

Could this be an issue?

Many thanks,

SpringTownDBA SSCertifiable Points: 6917 More actions · Answer 4

Swagger187 (11/30/2011)
Gianluca Sartori (11/25/2011)
Permissions don't "get lost", but may be revoked by something:
1) Explicit REVOKE commands
2) Drop / re-create view
Personally, I would investigate the drop/re-create as the most likely cause, then I would modify the create statement to include the GRANT commands needed or change it to an ALTER VIEW.
Hope this helps
Gianluca
Gianluca,
I have spent some time looking into this and myself and my manager both don't think this is the cause tho we could be wrong. Another thing I should mention is we created a command so that every saturday the server rebuilds the indexes.
Could this be an issue?
Many thanks,

No, rebuilding indexes will have no effect on views.

I recommend creating a database trigger to capture DDL events and log them to a table. This will make it obvious what is going on.

good luck.

Swagger187 Valued Member Points: 59 More actions · Answer 5

SpringTownDBA (11/30/2011)
Swagger187 (11/30/2011)
Gianluca Sartori (11/25/2011)
Permissions don't "get lost", but may be revoked by something:
1) Explicit REVOKE commands
2) Drop / re-create view
Personally, I would investigate the drop/re-create as the most likely cause, then I would modify the create statement to include the GRANT commands needed or change it to an ALTER VIEW.
Hope this helps
Gianluca
Gianluca,
I have spent some time looking into this and myself and my manager both don't think this is the cause tho we could be wrong. Another thing I should mention is we created a command so that every saturday the server rebuilds the indexes.
Could this be an issue?
Many thanks,
No, rebuilding indexes will have no effect on views.
I recommend creating a database trigger to capture DDL events and log them to a table. This will make it obvious what is going on.
good luck.

Thanks for the speedy reply. I will do this and see what happens.

Thanks,

Suresh B. SSC-Insane Points: 22986 More actions · Answer 6

Check the create_date in the sys.views.

If it is newer than the date the view was deployed, it means the view was DROPped and reCREATEd.

MMartin1 One Orange Chip Points: 27851 More actions · Answer 7

You can also start a Profiler trace to get more information with events on that view object. There are a couple of event categories that may be worthwhile to look into, Security Audit and Objects. Specifically, there is an event class under security audit titled "Audit Database Scope GDR Event." See if this yields any clues.

----------------------------------------------------

Swagger187 Valued Member Points: 59 More actions · Answer 8

Morning all,

I have looked into doing what you guys have recommended but being a novice in SQL I haven't been able to do any of them 🙁 embarressing (creating tables and creating simple triggers to sit on tables is as much as I can do).

I have however enable the public permissions on the views and that seems to have worked where as the Builtin users permissions have been revoked again. I will now start learning how SQL works so if these public permissions share the same fate as the builtin then I am in a position to be able to carry out some of the things you guys have recommended.

Many thanks,

Swagger