SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 SQL Server Administration (2008)
 provide full access to only views starting with vX
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sreenu9f
Yak Posting Veteran

72 Posts

Posted - 09/16/2013 :  11:00:03  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3568 Posts

Posted - 09/16/2013 :  11:37:28  Show Profile  Reply with Quote
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.
Go to Top of Page

sreenu9f
Yak Posting Veteran

72 Posts

Posted - 09/16/2013 :  12:01:24  Show Profile  Reply with Quote
Thanks James; can you please elaborate this with an example. I cannot understand what you have mentioned.
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3568 Posts

Posted - 09/16/2013 :  15:15:39  Show Profile  Reply with Quote
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.
Go to Top of Page

sreenu9f
Yak Posting Veteran

72 Posts

Posted - 09/16/2013 :  15:52:07  Show Profile  Reply with Quote
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
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3568 Posts

Posted - 09/16/2013 :  16:10:17  Show Profile  Reply with Quote
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;
Go to Top of Page

Bustaz Kool
Flowing Fount of Yak Knowledge

USA
1645 Posts

Posted - 09/17/2013 :  18:25:10  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000