provide full access to only views starting with vX

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SQL Server Administration (2008)

provide full access to only views starting with vX

Author

Topic

sreenu9f
Yak Posting Veteran

73 Posts

Posted - 2013-09-16 : 11:00:03

HI,
I need to set up a user to have full access to only views starting with vXYZ_... please suggest how i can do this.

thanks,

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-09-16 : 11:37:28

quote:
Originally posted by sreenu9f

HI,
I need to set up a user to have full access to only views starting with vXYZ_... please suggest how i can do this.

thanks,

I don't think SQL Server provides a mechanism for security based on wildcards and pattern of an object name. You will have to grant permissions to each view separately. Alternatively, if you have the flexibility to do so, you can create all those views in a separate schema (and nothing else in that new schema) and then grant permissions on that schema.

sreenu9f
Yak Posting Veteran

73 Posts

Posted - 2013-09-16 : 12:01:24

Thanks James; can you please elaborate this with an example. I cannot understand what you have mentioned.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-09-16 : 15:15:39

If you want to give permission to a bunch of views, one at a time, you would do this:

GRANT SELECT ON dbo.MyView1 TO theUserYouWantToGivePermissionTo
GRANT SELECT ON dbo.MyView2 TO theUserYouWantToGivePermissionTo
GRANT SELECT ON dbo.MyView3 TO theUserYouWantToGivePermissionTo

But if you create a new view and want to grant permission to that view, you will need to do the granting on that view.

To create a new schema and transfer the views to that schema and then grant permission to the schema, you would do this.

CREATE SCHEMA TestSchema;
GO

ALTER SCHEMA TestSchema TRANSFER dbo.MyView1;
ALTER SCHEMA TestSchema TRANSFER dbo.MyView2;
ALTER SCHEMA TestSchema TRANSFER dbo.MyView3;
GO

GRANT SELECT ON SCHEMA::TestSchema TO theUserYouWantToGivePermissionTo
GO

If you do it this way, the user will have access to any new view created in the TestSchema will automatically. But you have to be careful with this to make sure that you are not breaking any existing code.

Unless you have a compelling reason, and unless you are sure that it is not going to break existing code, I would prefer the first approach.

sreenu9f
Yak Posting Veteran

73 Posts

Posted - 2013-09-16 : 15:52:07

Thanks James for the detailed explanation.
The issue is i already have views created several hundreds of them already so i guess my only option is 2 now.
any other suggestion is greatly appreciated.

thanks

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-09-16 : 16:10:17

If you already have the views created, probably the first option is easier. Run this query, which will generate the SQL for granting SELECT privileges, copy that to an SSMS query window to run it, and you are done.

SELECT 'grant select on ' + name + ' to TheUserName' FROM sys.views;

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2013-09-17 : 18:25:10

SELECT 'grant select on ' + name + ' to TheUserName' FROM sys.views where name like 'vXYZ[_]%';

=================================================
The cure for anything is salt water -- sweat, tears, or the sea. -Isak Dinesen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources