Hello, everyone!
The question is: Can I alter the collation of a database
inside which there is a schema bound view, under SQL
Server 2000?
If you could help me with it, I'd ve extremely thankful.
We've got a product which is commercialized
internationally.
In order to do so, we have a basic database, and we alter
its collation to fit the target market.
Lately, we added a materialized view, and the ALTER
DATABASE COLLATE... command stopped working.
This is part of the script:
SELECT DATABASEPROPERTYEX('db', 'Collation') --
'SQL_Latin1_General_CP1255_CI_AS'
GO
DROP VIEW [DBO].[MATERIAL_VIEW_001]
GO
CREATE VIEW [DBO].[MATERIAL_VIEW_001]
WITH SCHEMABINDING
AS
SELECT DV.[DEPARTMENT], -- char(10)
DV.[FILENUM], -- tinyint
DV.[FIELDNUM], -- smallint
DV.[FROM_DATE], -- datetime
DV.[VALUE], -- varchar(1024), usually <
20.
EI.[DEP_INF] -- tinyint
FROM [DBO].[DEPVAL] DV WITH (NOLOCK)
JOIN [DBO].[EMPINF] EI WITH (NOLOCK)
ON EI.FILENUM = DV.FILENUM -- tinyint
AND EI.FIELDNUM = DV.FIELDNUM -- smallint
WHERE EI.DEP_INF > 0 -- tinyint
GO
CREATE UNIQUE CLUSTERED INDEX PK_MATERIAL_VIEW_001
ON DBO.[MATERIAL_VIEW_001]
( [DEPARTMENT], [FILENUM], [FIELDNUM], [FROM_DATE],
[DEP_INF], [VALUE] )
GO
-- drop index lv_depval_inf.pk_MATERIAL_VIEW_001 -- This
doesn't help.
ALTER DATABASE DB COLLATE French_CI_AS
/*
Server: Msg 5075, Level 16, State 1, Line 1
The object 'MATERIAL_VIEW_001' is dependent on database
collation.
Server: Msg 5072, Level 16, State 1, Line 1
ALTER DATABASE failed. The default collation of
database 'db' cannot be set to French_CI_AS.
*/
Can you help me with this?
Thanks in advance,
PabloHi
You don't mention if you change the column collations? e.g
http://tinyurl.com/91sg
I would also expect you to check what collation the system is set to:
Select SERVERPROPERTY(N'Collation')
I would expect the way around this is to drop and re-create the view after
you have changed the collation.
John
"Pablo Aliskevicius" wrote:
> Hello, everyone!
> The question is: Can I alter the collation of a database
> inside which there is a schema bound view, under SQL
> Server 2000?
> If you could help me with it, I'd ve extremely thankful.
> We've got a product which is commercialized
> internationally.
> In order to do so, we have a basic database, and we alter
> its collation to fit the target market.
> Lately, we added a materialized view, and the ALTER
> DATABASE COLLATE... command stopped working.
> This is part of the script:
> SELECT DATABASEPROPERTYEX('db', 'Collation') --
> 'SQL_Latin1_General_CP1255_CI_AS'
> GO
> DROP VIEW [DBO].[MATERIAL_VIEW_001]
> GO
> CREATE VIEW [DBO].[MATERIAL_VIEW_001]
> WITH SCHEMABINDING
> AS
> SELECT DV.[DEPARTMENT], -- char(10)
> DV.[FILENUM], -- tinyint
> DV.[FIELDNUM], -- smallint
> DV.[FROM_DATE], -- datetime
> DV.[VALUE], -- varchar(1024), usually <
> 20.
> EI.[DEP_INF] -- tinyint
> FROM [DBO].[DEPVAL] DV WITH (NOLOCK)
> JOIN [DBO].[EMPINF] EI WITH (NOLOCK)
> ON EI.FILENUM = DV.FILENUM -- tinyint
> AND EI.FIELDNUM = DV.FIELDNUM -- smallint
> WHERE EI.DEP_INF > 0 -- tinyint
> GO
> CREATE UNIQUE CLUSTERED INDEX PK_MATERIAL_VIEW_001
> ON DBO.[MATERIAL_VIEW_001]
> ( [DEPARTMENT], [FILENUM], [FIELDNUM], [FROM_DATE],
> [DEP_INF], [VALUE] )
> GO
> -- drop index lv_depval_inf.pk_MATERIAL_VIEW_001 -- This
> doesn't help.
> ALTER DATABASE DB COLLATE French_CI_AS
> /*
> Server: Msg 5075, Level 16, State 1, Line 1
> The object 'MATERIAL_VIEW_001' is dependent on database
> collation.
> Server: Msg 5072, Level 16, State 1, Line 1
> ALTER DATABASE failed. The default collation of
> database 'db' cannot be set to French_CI_AS.
> */
> Can you help me with this?
>
> Thanks in advance,
> Pablo
>|||Thank you, John, for your answer.
Maybe I wasn't clear enough: the application I'm
supporting has dozens of installations in at least ten
countries (that I know of) spanning three continents (or
four, if you count South America and North America as
two). Two more countries will be added in the next few
months. As a result, the server collation can be just
about any.
The software works on top of SQL Server. Since the
software is alive, the database keeps changing: fields
are added to existing tables, new tables and procedures
are added from time to time, new views appear from time
to time. We have already dozens of tables, and over 300
procedures.
As a result, an automatic upgrade program was written,
which compares the production database at the client's
site, with a 'last model' database. This 'last model'
exists in one place only, with one collation only.
In order to compare the databases, the 'model' database
must assume the 'target' database's collation.
Since there are a LOT of objects, dropping and recreating
objects can be done only as a last resource. A way to
execute ALTER DATABASE even when a schema-bound view
exists would be the best possible solution. After that, I
run a script quite like the one described in the URL you
mention - but the DATABASE_DEFAULT is UNKNOWN until run
time.
Thanks again,
Pablo.
>--Original Message--
>Hi
>You don't mention if you change the column collations?
e.g
>http://tinyurl.com/91sg
>I would also expect you to check what collation the
system is set to:
>Select SERVERPROPERTY(N'Collation')
>I would expect the way around this is to drop and re-
create the view after
>you have changed the collation.
>John
>
Showing posts with label views. Show all posts
Showing posts with label views. Show all posts
Wednesday, March 7, 2012
Friday, February 24, 2012
almost newbie question:recover view
SQL 2000 sp2
One of my users accidentally deleted one of her views. Using BackupExec 8.6
I restored the database to another SQL server. After restoring, I used the
Import DATA wizard to attempt to move the view from the recovered database
to the "live" database.
I select "copy objects & data between SQL server dbs."
On the next page I uncheck Copy all objects and click on Select Objects. I
then use the following screen to select the view I wish to recover.
I run it and it says it completed successfully. However, when I look at the
views available, the one that supposedly transferred is not there.
Can views be restored or would it be just as easy to recreate it using the
recovered view as a guide?In the restored database select the view and choose All Tasks>Generate
Script. Run the resulting script agains the database it was dropped from.
Have you refreshed the views folder to see if the new one is there ?
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"pdchris" <paulc@.mmcwm.com> wrote in message
news:%23%23BycRB8DHA.2168@.TK2MSFTNGP12.phx.gbl...
> SQL 2000 sp2
> One of my users accidentally deleted one of her views. Using BackupExec
8.6
> I restored the database to another SQL server. After restoring, I used
the
> Import DATA wizard to attempt to move the view from the recovered database
> to the "live" database.
> I select "copy objects & data between SQL server dbs."
> On the next page I uncheck Copy all objects and click on Select Objects.
I
> then use the following screen to select the view I wish to recover.
> I run it and it says it completed successfully. However, when I look at
the
> views available, the one that supposedly transferred is not there.
> Can views be restored or would it be just as easy to recreate it using the
> recovered view as a guide?
>|||Great, thanks!
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
news:u5y2KXB8DHA.3288@.TK2MSFTNGP11.phx.gbl...
> In the restored database select the view and choose All Tasks>Generate
> Script. Run the resulting script agains the database it was dropped from.
> Have you refreshed the views folder to see if the new one is there ?
> --
> HTH
> Jasper Smith (SQL Server MVP)
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
>
> "pdchris" <paulc@.mmcwm.com> wrote in message
> news:%23%23BycRB8DHA.2168@.TK2MSFTNGP12.phx.gbl...
> > SQL 2000 sp2
> > One of my users accidentally deleted one of her views. Using BackupExec
> 8.6
> > I restored the database to another SQL server. After restoring, I used
> the
> > Import DATA wizard to attempt to move the view from the recovered
database
> > to the "live" database.
> > I select "copy objects & data between SQL server dbs."
> > On the next page I uncheck Copy all objects and click on Select Objects.
> I
> > then use the following screen to select the view I wish to recover.
> > I run it and it says it completed successfully. However, when I look at
> the
> > views available, the one that supposedly transferred is not there.
> > Can views be restored or would it be just as easy to recreate it using
the
> > recovered view as a guide?
> >
> >
>
One of my users accidentally deleted one of her views. Using BackupExec 8.6
I restored the database to another SQL server. After restoring, I used the
Import DATA wizard to attempt to move the view from the recovered database
to the "live" database.
I select "copy objects & data between SQL server dbs."
On the next page I uncheck Copy all objects and click on Select Objects. I
then use the following screen to select the view I wish to recover.
I run it and it says it completed successfully. However, when I look at the
views available, the one that supposedly transferred is not there.
Can views be restored or would it be just as easy to recreate it using the
recovered view as a guide?In the restored database select the view and choose All Tasks>Generate
Script. Run the resulting script agains the database it was dropped from.
Have you refreshed the views folder to see if the new one is there ?
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"pdchris" <paulc@.mmcwm.com> wrote in message
news:%23%23BycRB8DHA.2168@.TK2MSFTNGP12.phx.gbl...
> SQL 2000 sp2
> One of my users accidentally deleted one of her views. Using BackupExec
8.6
> I restored the database to another SQL server. After restoring, I used
the
> Import DATA wizard to attempt to move the view from the recovered database
> to the "live" database.
> I select "copy objects & data between SQL server dbs."
> On the next page I uncheck Copy all objects and click on Select Objects.
I
> then use the following screen to select the view I wish to recover.
> I run it and it says it completed successfully. However, when I look at
the
> views available, the one that supposedly transferred is not there.
> Can views be restored or would it be just as easy to recreate it using the
> recovered view as a guide?
>|||Great, thanks!
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
news:u5y2KXB8DHA.3288@.TK2MSFTNGP11.phx.gbl...
> In the restored database select the view and choose All Tasks>Generate
> Script. Run the resulting script agains the database it was dropped from.
> Have you refreshed the views folder to see if the new one is there ?
> --
> HTH
> Jasper Smith (SQL Server MVP)
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
>
> "pdchris" <paulc@.mmcwm.com> wrote in message
> news:%23%23BycRB8DHA.2168@.TK2MSFTNGP12.phx.gbl...
> > SQL 2000 sp2
> > One of my users accidentally deleted one of her views. Using BackupExec
> 8.6
> > I restored the database to another SQL server. After restoring, I used
> the
> > Import DATA wizard to attempt to move the view from the recovered
database
> > to the "live" database.
> > I select "copy objects & data between SQL server dbs."
> > On the next page I uncheck Copy all objects and click on Select Objects.
> I
> > then use the following screen to select the view I wish to recover.
> > I run it and it says it completed successfully. However, when I look at
> the
> > views available, the one that supposedly transferred is not there.
> > Can views be restored or would it be just as easy to recreate it using
the
> > recovered view as a guide?
> >
> >
>
almost newbie question:recover view
SQL 2000 sp2
One of my users accidentally deleted one of her views. Using BackupExec 8.6
I restored the database to another SQL server. After restoring, I used the
Import DATA wizard to attempt to move the view from the recovered database
to the "live" database.
I select "copy objects & data between SQL server dbs."
On the next page I uncheck Copy all objects and click on Select Objects. I
then use the following screen to select the view I wish to recover.
I run it and it says it completed successfully. However, when I look at the
views available, the one that supposedly transferred is not there.
Can views be restored or would it be just as easy to recreate it using the
recovered view as a guide?In the restored database select the view and choose All Tasks>Generate
Script. Run the resulting script agains the database it was dropped from.
Have you refreshed the views folder to see if the new one is there ?
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"pdchris" <paulc@.mmcwm.com> wrote in message
news:%23%23BycRB8DHA.2168@.TK2MSFTNGP12.phx.gbl...
> SQL 2000 sp2
> One of my users accidentally deleted one of her views. Using BackupExec
8.6
> I restored the database to another SQL server. After restoring, I used
the
> Import DATA wizard to attempt to move the view from the recovered database
> to the "live" database.
> I select "copy objects & data between SQL server dbs."
> On the next page I uncheck Copy all objects and click on Select Objects.
I
> then use the following screen to select the view I wish to recover.
> I run it and it says it completed successfully. However, when I look at
the
> views available, the one that supposedly transferred is not there.
> Can views be restored or would it be just as easy to recreate it using the
> recovered view as a guide?
>|||Great, thanks!
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
news:u5y2KXB8DHA.3288@.TK2MSFTNGP11.phx.gbl...
> In the restored database select the view and choose All Tasks>Generate
> Script. Run the resulting script agains the database it was dropped from.
> Have you refreshed the views folder to see if the new one is there ?
> --
> HTH
> Jasper Smith (SQL Server MVP)
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
>
> "pdchris" <paulc@.mmcwm.com> wrote in message
> news:%23%23BycRB8DHA.2168@.TK2MSFTNGP12.phx.gbl...
> 8.6
> the
database
> I
> the
the
>
One of my users accidentally deleted one of her views. Using BackupExec 8.6
I restored the database to another SQL server. After restoring, I used the
Import DATA wizard to attempt to move the view from the recovered database
to the "live" database.
I select "copy objects & data between SQL server dbs."
On the next page I uncheck Copy all objects and click on Select Objects. I
then use the following screen to select the view I wish to recover.
I run it and it says it completed successfully. However, when I look at the
views available, the one that supposedly transferred is not there.
Can views be restored or would it be just as easy to recreate it using the
recovered view as a guide?In the restored database select the view and choose All Tasks>Generate
Script. Run the resulting script agains the database it was dropped from.
Have you refreshed the views folder to see if the new one is there ?
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"pdchris" <paulc@.mmcwm.com> wrote in message
news:%23%23BycRB8DHA.2168@.TK2MSFTNGP12.phx.gbl...
> SQL 2000 sp2
> One of my users accidentally deleted one of her views. Using BackupExec
8.6
> I restored the database to another SQL server. After restoring, I used
the
> Import DATA wizard to attempt to move the view from the recovered database
> to the "live" database.
> I select "copy objects & data between SQL server dbs."
> On the next page I uncheck Copy all objects and click on Select Objects.
I
> then use the following screen to select the view I wish to recover.
> I run it and it says it completed successfully. However, when I look at
the
> views available, the one that supposedly transferred is not there.
> Can views be restored or would it be just as easy to recreate it using the
> recovered view as a guide?
>|||Great, thanks!
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
news:u5y2KXB8DHA.3288@.TK2MSFTNGP11.phx.gbl...
> In the restored database select the view and choose All Tasks>Generate
> Script. Run the resulting script agains the database it was dropped from.
> Have you refreshed the views folder to see if the new one is there ?
> --
> HTH
> Jasper Smith (SQL Server MVP)
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
>
> "pdchris" <paulc@.mmcwm.com> wrote in message
> news:%23%23BycRB8DHA.2168@.TK2MSFTNGP12.phx.gbl...
> 8.6
> the
database
> I
> the
the
>
Monday, February 13, 2012
Allow a user to alter views.
Hi everyone,
Is it possible to allow a plain user(not a member of any roles) to alter
views created by dbo ?
Regards.What version are you using?
Take a look at GRANT ALTER VIEW command in the BOL
"Sezgin Rafet" <anonymous@.newsgroup.com> wrote in message
news:OHNQs%23eaGHA.5088@.TK2MSFTNGP03.phx.gbl...
> Hi everyone,
> Is it possible to allow a plain user(not a member of any roles) to alter
> views created by dbo ?
> Regards.
>|||What version are you using? To create/modify dbo-owned objects in SQL 2000,
a user needs to be either:
1) a sysadmin role member
2) the database owner
3) a member of the db_owner role
4) a member of the db_ddladmin role
Why do your 'pain users' need to modify dbo-owned views? Perhaps there is
an alternative.
Hope this helps.
Dan Guzman
SQL Server MVP
"Sezgin Rafet" <anonymous@.newsgroup.com> wrote in message
news:OHNQs%23eaGHA.5088@.TK2MSFTNGP03.phx.gbl...
> Hi everyone,
> Is it possible to allow a plain user(not a member of any roles) to alter
> views created by dbo ?
> Regards.
>|||"Sezgin Rafet" <anonymous@.newsgroup.com> wrote in message
news:OHNQs%23eaGHA.5088@.TK2MSFTNGP03.phx.gbl...
> Hi everyone,
> Is it possible to allow a plain user(not a member of any roles) to alter
> views created by dbo ?
>
You realize that this will allow the user to SELECT and possibly UPDATE and
DELETE _every_ table owned by dbo.
David|||Thanks for the replies. We are using SQL Server 2000.
We have 2 databases - one is the live one and the other is for development.
One of the departments uses views for generating reports. The SQL Login they
use is a member of the db_owner role in the development db , so they create
and modify views as required. The same SQL Login is a plain user(member of
public only) in the live database.After creating/altering views in the
development db they need to apply the changes to the live db. It is not
happening very often. They can send the script to me and I can execute it in
the live db. As an alternative we can write a small application to connect
to the live db with sufficient user credentials(hard coded) and execute the
script.
Best Regards.
"Sezgin Rafet" <anonymous@.newsgroup.com> wrote in message
news:OHNQs%23eaGHA.5088@.TK2MSFTNGP03.phx.gbl...
> Hi everyone,
> Is it possible to allow a plain user(not a member of any roles) to alter
> views created by dbo ?
> Regards.
>|||> They can send the script to me and I can execute it in the live db. As an
> alternative we can write a small application to connect to the live db
> with sufficient user credentials(hard coded) and execute the script.
The app solution is probably best as long as you can justify the development
effort and there is no additional value with DBA involvement, like reviewing
the queries. Be sure to implement an application security technique to
ensure only authorized users can run it. One method is to first connect to
the live db using normal user credentials and then verify that the user
exists in an AuthorizedUsers table.
Hope this helps.
Dan Guzman
SQL Server MVP
"Sezgin Rafet" <anonymous@.newsgroup.com> wrote in message
news:uif4Wk1aGHA.5000@.TK2MSFTNGP05.phx.gbl...
> Thanks for the replies. We are using SQL Server 2000.
> We have 2 databases - one is the live one and the other is for
> development. One of the departments uses views for generating reports. The
> SQL Login they use is a member of the db_owner role in the development db
> , so they create and modify views as required. The same SQL Login is a
> plain user(member of public only) in the live database.After
> creating/altering views in the development db they need to apply the
> changes to the live db. It is not happening very often. They can send the
> script to me and I can execute it in the live db. As an alternative we can
> write a small application to connect to the live db with sufficient user
> credentials(hard coded) and execute the script.
> Best Regards.
>
> "Sezgin Rafet" <anonymous@.newsgroup.com> wrote in message
> news:OHNQs%23eaGHA.5088@.TK2MSFTNGP03.phx.gbl...
>
Is it possible to allow a plain user(not a member of any roles) to alter
views created by dbo ?
Regards.What version are you using?
Take a look at GRANT ALTER VIEW command in the BOL
"Sezgin Rafet" <anonymous@.newsgroup.com> wrote in message
news:OHNQs%23eaGHA.5088@.TK2MSFTNGP03.phx.gbl...
> Hi everyone,
> Is it possible to allow a plain user(not a member of any roles) to alter
> views created by dbo ?
> Regards.
>|||What version are you using? To create/modify dbo-owned objects in SQL 2000,
a user needs to be either:
1) a sysadmin role member
2) the database owner
3) a member of the db_owner role
4) a member of the db_ddladmin role
Why do your 'pain users' need to modify dbo-owned views? Perhaps there is
an alternative.
Hope this helps.
Dan Guzman
SQL Server MVP
"Sezgin Rafet" <anonymous@.newsgroup.com> wrote in message
news:OHNQs%23eaGHA.5088@.TK2MSFTNGP03.phx.gbl...
> Hi everyone,
> Is it possible to allow a plain user(not a member of any roles) to alter
> views created by dbo ?
> Regards.
>|||"Sezgin Rafet" <anonymous@.newsgroup.com> wrote in message
news:OHNQs%23eaGHA.5088@.TK2MSFTNGP03.phx.gbl...
> Hi everyone,
> Is it possible to allow a plain user(not a member of any roles) to alter
> views created by dbo ?
>
You realize that this will allow the user to SELECT and possibly UPDATE and
DELETE _every_ table owned by dbo.
David|||Thanks for the replies. We are using SQL Server 2000.
We have 2 databases - one is the live one and the other is for development.
One of the departments uses views for generating reports. The SQL Login they
use is a member of the db_owner role in the development db , so they create
and modify views as required. The same SQL Login is a plain user(member of
public only) in the live database.After creating/altering views in the
development db they need to apply the changes to the live db. It is not
happening very often. They can send the script to me and I can execute it in
the live db. As an alternative we can write a small application to connect
to the live db with sufficient user credentials(hard coded) and execute the
script.
Best Regards.
"Sezgin Rafet" <anonymous@.newsgroup.com> wrote in message
news:OHNQs%23eaGHA.5088@.TK2MSFTNGP03.phx.gbl...
> Hi everyone,
> Is it possible to allow a plain user(not a member of any roles) to alter
> views created by dbo ?
> Regards.
>|||> They can send the script to me and I can execute it in the live db. As an
> alternative we can write a small application to connect to the live db
> with sufficient user credentials(hard coded) and execute the script.
The app solution is probably best as long as you can justify the development
effort and there is no additional value with DBA involvement, like reviewing
the queries. Be sure to implement an application security technique to
ensure only authorized users can run it. One method is to first connect to
the live db using normal user credentials and then verify that the user
exists in an AuthorizedUsers table.
Hope this helps.
Dan Guzman
SQL Server MVP
"Sezgin Rafet" <anonymous@.newsgroup.com> wrote in message
news:uif4Wk1aGHA.5000@.TK2MSFTNGP05.phx.gbl...
> Thanks for the replies. We are using SQL Server 2000.
> We have 2 databases - one is the live one and the other is for
> development. One of the departments uses views for generating reports. The
> SQL Login they use is a member of the db_owner role in the development db
> , so they create and modify views as required. The same SQL Login is a
> plain user(member of public only) in the live database.After
> creating/altering views in the development db they need to apply the
> changes to the live db. It is not happening very often. They can send the
> script to me and I can execute it in the live db. As an alternative we can
> write a small application to connect to the live db with sufficient user
> credentials(hard coded) and execute the script.
> Best Regards.
>
> "Sezgin Rafet" <anonymous@.newsgroup.com> wrote in message
> news:OHNQs%23eaGHA.5088@.TK2MSFTNGP03.phx.gbl...
>
Allow a user to alter views.
Hi everyone,
Is it possible to allow a plain user(not a member of any roles) to alter
views created by dbo ?
Regards.What version are you using?
Take a look at GRANT ALTER VIEW command in the BOL
"Sezgin Rafet" <anonymous@.newsgroup.com> wrote in message
news:OHNQs%23eaGHA.5088@.TK2MSFTNGP03.phx.gbl...
> Hi everyone,
> Is it possible to allow a plain user(not a member of any roles) to alter
> views created by dbo ?
> Regards.
>|||What version are you using? To create/modify dbo-owned objects in SQL 2000,
a user needs to be either:
1) a sysadmin role member
2) the database owner
3) a member of the db_owner role
4) a member of the db_ddladmin role
Why do your 'pain users' need to modify dbo-owned views? Perhaps there is
an alternative.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Sezgin Rafet" <anonymous@.newsgroup.com> wrote in message
news:OHNQs%23eaGHA.5088@.TK2MSFTNGP03.phx.gbl...
> Hi everyone,
> Is it possible to allow a plain user(not a member of any roles) to alter
> views created by dbo ?
> Regards.
>|||"Sezgin Rafet" <anonymous@.newsgroup.com> wrote in message
news:OHNQs%23eaGHA.5088@.TK2MSFTNGP03.phx.gbl...
> Hi everyone,
> Is it possible to allow a plain user(not a member of any roles) to alter
> views created by dbo ?
>
You realize that this will allow the user to SELECT and possibly UPDATE and
DELETE _every_ table owned by dbo.
David|||Thanks for the replies. We are using SQL Server 2000.
We have 2 databases - one is the live one and the other is for development.
One of the departments uses views for generating reports. The SQL Login they
use is a member of the db_owner role in the development db , so they create
and modify views as required. The same SQL Login is a plain user(member of
public only) in the live database.After creating/altering views in the
development db they need to apply the changes to the live db. It is not
happening very often. They can send the script to me and I can execute it in
the live db. As an alternative we can write a small application to connect
to the live db with sufficient user credentials(hard coded) and execute the
script.
Best Regards.
"Sezgin Rafet" <anonymous@.newsgroup.com> wrote in message
news:OHNQs%23eaGHA.5088@.TK2MSFTNGP03.phx.gbl...
> Hi everyone,
> Is it possible to allow a plain user(not a member of any roles) to alter
> views created by dbo ?
> Regards.
>|||> They can send the script to me and I can execute it in the live db. As an
> alternative we can write a small application to connect to the live db
> with sufficient user credentials(hard coded) and execute the script.
The app solution is probably best as long as you can justify the development
effort and there is no additional value with DBA involvement, like reviewing
the queries. Be sure to implement an application security technique to
ensure only authorized users can run it. One method is to first connect to
the live db using normal user credentials and then verify that the user
exists in an AuthorizedUsers table.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Sezgin Rafet" <anonymous@.newsgroup.com> wrote in message
news:uif4Wk1aGHA.5000@.TK2MSFTNGP05.phx.gbl...
> Thanks for the replies. We are using SQL Server 2000.
> We have 2 databases - one is the live one and the other is for
> development. One of the departments uses views for generating reports. The
> SQL Login they use is a member of the db_owner role in the development db
> , so they create and modify views as required. The same SQL Login is a
> plain user(member of public only) in the live database.After
> creating/altering views in the development db they need to apply the
> changes to the live db. It is not happening very often. They can send the
> script to me and I can execute it in the live db. As an alternative we can
> write a small application to connect to the live db with sufficient user
> credentials(hard coded) and execute the script.
> Best Regards.
>
> "Sezgin Rafet" <anonymous@.newsgroup.com> wrote in message
> news:OHNQs%23eaGHA.5088@.TK2MSFTNGP03.phx.gbl...
>> Hi everyone,
>> Is it possible to allow a plain user(not a member of any roles) to alter
>> views created by dbo ?
>> Regards.
>>
>
Is it possible to allow a plain user(not a member of any roles) to alter
views created by dbo ?
Regards.What version are you using?
Take a look at GRANT ALTER VIEW command in the BOL
"Sezgin Rafet" <anonymous@.newsgroup.com> wrote in message
news:OHNQs%23eaGHA.5088@.TK2MSFTNGP03.phx.gbl...
> Hi everyone,
> Is it possible to allow a plain user(not a member of any roles) to alter
> views created by dbo ?
> Regards.
>|||What version are you using? To create/modify dbo-owned objects in SQL 2000,
a user needs to be either:
1) a sysadmin role member
2) the database owner
3) a member of the db_owner role
4) a member of the db_ddladmin role
Why do your 'pain users' need to modify dbo-owned views? Perhaps there is
an alternative.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Sezgin Rafet" <anonymous@.newsgroup.com> wrote in message
news:OHNQs%23eaGHA.5088@.TK2MSFTNGP03.phx.gbl...
> Hi everyone,
> Is it possible to allow a plain user(not a member of any roles) to alter
> views created by dbo ?
> Regards.
>|||"Sezgin Rafet" <anonymous@.newsgroup.com> wrote in message
news:OHNQs%23eaGHA.5088@.TK2MSFTNGP03.phx.gbl...
> Hi everyone,
> Is it possible to allow a plain user(not a member of any roles) to alter
> views created by dbo ?
>
You realize that this will allow the user to SELECT and possibly UPDATE and
DELETE _every_ table owned by dbo.
David|||Thanks for the replies. We are using SQL Server 2000.
We have 2 databases - one is the live one and the other is for development.
One of the departments uses views for generating reports. The SQL Login they
use is a member of the db_owner role in the development db , so they create
and modify views as required. The same SQL Login is a plain user(member of
public only) in the live database.After creating/altering views in the
development db they need to apply the changes to the live db. It is not
happening very often. They can send the script to me and I can execute it in
the live db. As an alternative we can write a small application to connect
to the live db with sufficient user credentials(hard coded) and execute the
script.
Best Regards.
"Sezgin Rafet" <anonymous@.newsgroup.com> wrote in message
news:OHNQs%23eaGHA.5088@.TK2MSFTNGP03.phx.gbl...
> Hi everyone,
> Is it possible to allow a plain user(not a member of any roles) to alter
> views created by dbo ?
> Regards.
>|||> They can send the script to me and I can execute it in the live db. As an
> alternative we can write a small application to connect to the live db
> with sufficient user credentials(hard coded) and execute the script.
The app solution is probably best as long as you can justify the development
effort and there is no additional value with DBA involvement, like reviewing
the queries. Be sure to implement an application security technique to
ensure only authorized users can run it. One method is to first connect to
the live db using normal user credentials and then verify that the user
exists in an AuthorizedUsers table.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Sezgin Rafet" <anonymous@.newsgroup.com> wrote in message
news:uif4Wk1aGHA.5000@.TK2MSFTNGP05.phx.gbl...
> Thanks for the replies. We are using SQL Server 2000.
> We have 2 databases - one is the live one and the other is for
> development. One of the departments uses views for generating reports. The
> SQL Login they use is a member of the db_owner role in the development db
> , so they create and modify views as required. The same SQL Login is a
> plain user(member of public only) in the live database.After
> creating/altering views in the development db they need to apply the
> changes to the live db. It is not happening very often. They can send the
> script to me and I can execute it in the live db. As an alternative we can
> write a small application to connect to the live db with sufficient user
> credentials(hard coded) and execute the script.
> Best Regards.
>
> "Sezgin Rafet" <anonymous@.newsgroup.com> wrote in message
> news:OHNQs%23eaGHA.5088@.TK2MSFTNGP03.phx.gbl...
>> Hi everyone,
>> Is it possible to allow a plain user(not a member of any roles) to alter
>> views created by dbo ?
>> Regards.
>>
>
Sunday, February 12, 2012
All tables and columns referenced by sql objects
I'm trying to find all the tables and columns referenced by the sql objects (stored procs, UDFs, views, etc.) in a particular MSSQL2005 database.
I can join the sys.objects, sys.sql_dependencies and sys.columns tables using object_id, referenced_major_id, referenced_minor_id, and column_id but I find that the more complex procs contain references to tables (sub selects, for example) that do not seem to appear in sys.sql_dependencies.
I can always do string searches on the definition column in sys.sql_modules, but that won't reliably get me column+table combos.
Anyone have any ideas?
Thanks!
I actually wrote a blog on this exact topic recently. Check it out:
http://blogs.claritycon.com/blogs/the_englishman/default.aspx
HTH
|||Well... close but... Your solution involves string searches on the proc definition which may or may not (probably not) distinguish between tab1.tab1Key (PK) and tab2.tab1Key (FK).
Incidentally, check out http://msdn2.microsoft.com/en-us/library/ms187997.aspx for the SQL2005 system views that correspond to the SQL 2000 tables.
Thanks!
|||No, for that you would have to use the following in an exists:
SELECT * FROM sysforeignkeys f join sysobjects obj on obj.id = f.fkeyid
Incidentally, I was told by a SQL server database engine worker that the INFORMATION_SCHEMA schema views are better to use. Check out the following:
SELECT * FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
SELECT * FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS
SELECT * FROM INFORMATION_SCHEMA.COLUMNS
That should do it, in combination with my stored porcedure. Let me know if you need more information.
|||This is unfortunately not so easy to do. You should rely on your source code control to maintain the dependencies since it can be easily broken on the database side by recreating objects in wrong order or dropping some objects etc. There are certain dependencies like foreign key constraints, schema bound objects which are easy to obtain and maintained accurately by the engine. But there are other by name dependencies which are harder to track. For an example of how complex this scenario can be you can take a look at my blog post below which covers the direct dependencies on a column:
http://blogs.msdn.com/sqltips/archive/2005/07/05/435882.aspx
Subscribe to:
Posts (Atom)