March 1, 2011 at 10:16 pm
Hi All,
I need to create more than 30 views at a time, for that I have written some automation script. The problem is, these scripts need to be run from sa login and will be created under some different user. Can we dynamically change the user name is the view?
Like I need to create view under user A. Hence, I have prefixed it A.viewname. If I want to create view under user B, then again I need to change all views with B.viewname.
Can we supply the user name as a parameter?
March 1, 2011 at 10:30 pm
If I read it correctly, you are trying to replicate the 30 views for each schema for those users required. Any special reason for that? Can't you create the view under, san dbo, and the control access to the view only to those users?
March 1, 2011 at 10:33 pm
Hi John,
Thanks for your reply. Yes, we have a special reason for this. And for product requirement, we cant use all views under sa.
🙂 The scenario I have mentioned, I need to fulfill that 🙂
March 2, 2011 at 9:45 am
...and will be created under some different user.
The requirement that schemas be strictly linked to database users of the same name last existed in SQL 2000. Some folks still use 'user' and 'schema' interchangeably so I am mentioning this distinction in case it affects your decision making down the line. Schemas are very different and have a offer of power and flexibility as security and entity containers.
Can we dynamically change the user name is the view?
In a sense, yes, but not in a procedural way. This would *not* work:
DECLARE @schema_name SYSNAME = N'some_schema_name' ;
CREATE VIEW @schema_name.view_name
AS
SELECT Column1
FROM dbo.Table1
GO
However something like this where you create a view using dynamic SQL would work:
DECLARE @sql NVARCHAR(MAX) ;
DECLARE @schema_name SYSNAME ;
SET @schema_name = N'some_schema_name'
SET @sql = N'CREATE VIEW ' + @schema_name + N'.view_name
AS
SELECT Column1
FROM dbo.Table1
GO
'
EXEC(@sql) ;
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply