Altering multiple objects schema

Hi,

I need to change the schema of the stored procedures of several databases.

Is there a way to put the alter schema statement within a loop that automaticaly processes all the stored procedures in a given database ?

thank you

Probably your best option is to use a cursor. You can find more information about them in BOL (http://msdn2.microsoft.com/en-us/library/ms180169.aspx)

-Raul Garcia

SDE/T

SQL Server Engine

|||

You can also try doing something like this. If NEWSCHEMA is the schema you want to transfer all the procedures to the following query should help

declare @.querystring nvarchar(MAX)

set @.querystring=''

select @.querystring=@.querystring+' ALTER SCHEMA NEWSCHEMA TRANSFER ' + schema_name(schema_id) + '.' + name from sys.procedures

exec(@.querystring)

Either way, you will have to use dynamic sql.

Altering column with index

Hello there
I need to alter the collation of collumns on my databases.
It failes on columns that connected to indexes
What should i do to alter them in this case?You'll need to drop the indexes/constraints and recreate after changing the
collation.
Hope this helps.
Dan Guzman
SQL Server MVP
"Roy Goldhammer" <roy@.hotmail.com> wrote in message
news:%237WdGTuOGHA.3896@.TK2MSFTNGP15.phx.gbl...
> Hello there
> I need to alter the collation of collumns on my databases.
> It failes on columns that connected to indexes
> What should i do to alter them in this case?
>
>|||Drop the indexes (and any constraints) first, then alter the column and
restore the indexes (and constraints).
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Roy Goldhammer" <roy@.hotmail.com> wrote in message
news:%237WdGTuOGHA.3896@.TK2MSFTNGP15.phx.gbl...
Hello there
I need to alter the collation of collumns on my databases.
It failes on columns that connected to indexes
What should i do to alter them in this case?|||Whell Tom
It seems to be very agly to do that.
When i do it with the enterprise Manager i don't do all this stuff
Are you sure i need to do all of that?
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:ev8nGguOGHA.2704@.TK2MSFTNGP15.phx.gbl...
> Drop the indexes (and any constraints) first, then alter the column and
> restore the indexes (and constraints).
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> .
> "Roy Goldhammer" <roy@.hotmail.com> wrote in message
> news:%237WdGTuOGHA.3896@.TK2MSFTNGP15.phx.gbl...
> Hello there
> I need to alter the collation of collumns on my databases.
> It failes on columns that connected to indexes
> What should i do to alter them in this case?
>
>|||
Roy Goldhammer wrote:

>Whell Tom
>It seems to be very agly to do that.
>When i do it with the enterprise Manager i don't do all this stuff
>
Enterprise Manager probably does it for you behind the scenes. While
EM doesn't do everything in the best way, you could generate the change
script from EM and use it as a template for creating your own script. If
an index exists on a varchar column, and then the collation is changed,
the index must be rebuilt. There's no way around it, since a change in
collation may change the ordering of the values, which is what the
index is there to implement.
Steve Kass
Drew University

>Are you sure i need to do all of that?
>"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
>news:ev8nGguOGHA.2704@.TK2MSFTNGP15.phx.gbl...
>
>
>

ALTER SP if exists in all databases

Hi,
I am trying to alter an SP in all my dB's. I am trying to loop through all
the databases ,however my script always returns false when it checks if the
SP exists (even though the SP exists)...for some reason it appears to be
still in master db even though I change db inside the cursor.
use master
declare @.AlterText = "ALTER PROCEDURE ..." --SP
declare @.dbName
declare c1 cursor for
select [name] from sysdatabases
open c1
fetch c1 into @.dbname
while(@.@.fetch_status = 0)
begin
exec('use ' + @.dbname)
if Exists(select name from sysobjects where name = 'sp_mysp' )
begin
print @.dbname
--exec (@.AlterText)
end
else
begin
print 'SP does not exist in '+@.dbname
end
fetch next from c1 into @.dbname
end
close c1
deallocate c1the scope of exec is only till the execution of that command.
"Mike" wrote:

> Hi,
> I am trying to alter an SP in all my dB's. I am trying to loop through all
> the databases ,however my script always returns false when it checks if th
e
> SP exists (even though the SP exists)...for some reason it appears to be
> still in master db even though I change db inside the cursor.
> use master
> declare @.AlterText = "ALTER PROCEDURE ..." --SP
> declare @.dbName
> declare c1 cursor for
> select [name] from sysdatabases
> open c1
> fetch c1 into @.dbname
> while(@.@.fetch_status = 0)
> begin
> exec('use ' + @.dbname)
> if Exists(select name from sysobjects where name = 'sp_mysp' )
> begin
> print @.dbname
> --exec (@.AlterText)
> end
> else
> begin
> print 'SP does not exist in '+@.dbname
> end
> fetch next from c1 into @.dbname
> end
> close c1
> deallocate c1|||Mike,
You only change the db for the exec() statement.
The current database after the exec() statement
is still master, since the USE result doesn't affect
the current database of the exec statement's caller.
A good way to do this kind of thing is to generate
all the statements you need to run by a single
query, then inspect the results and copy them by
hand and re-run them as a batch. For example,
here, you would run the output of
select
replace(replace('
use $db
goo
alter proc abc (
@.a int
) as
...
goo
','$db',quotename(name)),'goo','go')
from sysdatabases
The quotename() function protects against SQL injection
attacks from maliciously-named databases intended to cause
damage when scripts like yours are run.
You could select these strings with a cursor and execute them
also.
Be warned, however, that if your procedure does have the
name sp_something, you may run into surprises, because there
are some special name resolution rules for procedures whose
names begin with sp_. That prefix should not be used for
user-defined stored procedures.
Steve Kass
Drew Univeristy
Mike wrote:

>Hi,
>I am trying to alter an SP in all my dB's. I am trying to loop through all
>the databases ,however my script always returns false when it checks if the
>SP exists (even though the SP exists)...for some reason it appears to be
>still in master db even though I change db inside the cursor.
>use master
>declare @.AlterText = "ALTER PROCEDURE ..." --SP
>declare @.dbName
>declare c1 cursor for
>select [name] from sysdatabases
>open c1
>fetch c1 into @.dbname
>while(@.@.fetch_status = 0)
>begin
>exec('use ' + @.dbname)
>if Exists(select name from sysobjects where name = 'sp_mysp' )
> begin
> print @.dbname
> --exec (@.AlterText)
> end
>else
> begin
> print 'SP does not exist in '+@.dbname
> end
>fetch next from c1 into @.dbname
>end
>close c1
>deallocate c1
>|||By the way. Functionality you try to achieve with the script can be done by
using the following.
sp_msforeachdb 'use ? if Exists(select name from sysobjects where name =
''sp_mysp'' ) begin print ''?'' end'
Hope this helps.
--
"Omnibuzz" wrote:
> the scope of exec is only till the execution of that command.
> --
>
>
> "Mike" wrote:
>

Alter Database across linked server

We have approximately 30 servers with about 700 databases.
We are running SQL Server 2005, SP1, on Windows 2003
We need to set Quoted Identifiers ON, as the default setting on all databases.
Linked servers are set up on all servers.
I have created a statement using dynamic sql to loop through all servers and
databases to set QUOTED_IDENTIFIER ON.
This is a sample output of a statement to be executed:
ALTER DATABASE SQL02.AdventureWorks
SET QUOTED_IDENTIFIER OFF
When the statement is executed I get an error:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '.'.
To verify my linked server is set up properly, I run the following
successfully:
select * from SQL02.AdventureWorks.Person.Address
Is it possible to run an alter database command across a linked server, and
if so, how do you let SQL Server know which database server is to be used?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200701/1"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:6c17852baa692@.uwe...
> We have approximately 30 servers with about 700 databases.
> We are running SQL Server 2005, SP1, on Windows 2003
> We need to set Quoted Identifiers ON, as the default setting on all
> databases.
>
> Linked servers are set up on all servers.
> I have created a statement using dynamic sql to loop through all servers
> and
> databases to set QUOTED_IDENTIFIER ON.
> This is a sample output of a statement to be executed:
> ALTER DATABASE SQL02.AdventureWorks
> SET QUOTED_IDENTIFIER OFF
> When the statement is executed I get an error:
> Msg 102, Level 15, State 1, Line 1
> Incorrect syntax near '.'.
> To verify my linked server is set up properly, I run the following
> successfully:
> select * from SQL02.AdventureWorks.Person.Address
> Is it possible to run an alter database command across a linked server,
> and
> if so, how do you let SQL Server know which database server is to be used?
>
Yes. In 2005 you can execute arbitrary batches, including stored procedures
and DDL, at remote servers with the EXEC ... AT statement.
eg:
exec ( '
ALTER DATABASE AdventureWorks SET QUOTED_IDENTIFIER OFF
' ) at SQL02
David

Alter Database across linked server

We have approximately 30 servers with about 700 databases.
We are running SQL Server 2005, SP1, on Windows 2003
We need to set Quoted Identifiers ON, as the default setting on all database
s.
Linked servers are set up on all servers.
I have created a statement using dynamic sql to loop through all servers and
databases to set QUOTED_IDENTIFIER ON.
This is a sample output of a statement to be executed:
ALTER DATABASE SQL02.AdventureWorks
SET QUOTED_IDENTIFIER OFF
When the statement is executed I get an error:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '.'.
To verify my linked server is set up properly, I run the following
successfully:
select * from SQL02.AdventureWorks.Person.Address
Is it possible to run an alter database command across a linked server, and
if so, how do you let SQL Server know which database server is to be used?
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200701/1"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:6c17852baa692@.uwe...
> We have approximately 30 servers with about 700 databases.
> We are running SQL Server 2005, SP1, on Windows 2003
> We need to set Quoted Identifiers ON, as the default setting on all
> databases.
>
> Linked servers are set up on all servers.
> I have created a statement using dynamic sql to loop through all servers
> and
> databases to set QUOTED_IDENTIFIER ON.
> This is a sample output of a statement to be executed:
> ALTER DATABASE SQL02.AdventureWorks
> SET QUOTED_IDENTIFIER OFF
> When the statement is executed I get an error:
> Msg 102, Level 15, State 1, Line 1
> Incorrect syntax near '.'.
> To verify my linked server is set up properly, I run the following
> successfully:
> select * from SQL02.AdventureWorks.Person.Address
> Is it possible to run an alter database command across a linked server,
> and
> if so, how do you let SQL Server know which database server is to be used?
>
Yes. In 2005 you can execute arbitrary batches, including stored procedures
and DDL, at remote servers with the EXEC ... AT statement.
eg:
exec ( '
ALTER DATABASE AdventureWorks SET QUOTED_IDENTIFIER OFF
' ) at SQL02
David

Alter Database across linked server

We have approximately 30 servers with about 700 databases.
We are running SQL Server 2005, SP1, on Windows 2003
We need to set Quoted Identifiers ON, as the default setting on all databases.
Linked servers are set up on all servers.
I have created a statement using dynamic sql to loop through all servers and
databases to set QUOTED_IDENTIFIER ON.
This is a sample output of a statement to be executed:
ALTER DATABASE SQL02.AdventureWorks
SET QUOTED_IDENTIFIER OFF
When the statement is executed I get an error:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '.'.
To verify my linked server is set up properly, I run the following
successfully:
select * from SQL02.AdventureWorks.Person.Address
Is it possible to run an alter database command across a linked server, and
if so, how do you let SQL Server know which database server is to be used?
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums.aspx/sql-server/200701/1
"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:6c17852baa692@.uwe...
> We have approximately 30 servers with about 700 databases.
> We are running SQL Server 2005, SP1, on Windows 2003
> We need to set Quoted Identifiers ON, as the default setting on all
> databases.
>
> Linked servers are set up on all servers.
> I have created a statement using dynamic sql to loop through all servers
> and
> databases to set QUOTED_IDENTIFIER ON.
> This is a sample output of a statement to be executed:
> ALTER DATABASE SQL02.AdventureWorks
> SET QUOTED_IDENTIFIER OFF
> When the statement is executed I get an error:
> Msg 102, Level 15, State 1, Line 1
> Incorrect syntax near '.'.
> To verify my linked server is set up properly, I run the following
> successfully:
> select * from SQL02.AdventureWorks.Person.Address
> Is it possible to run an alter database command across a linked server,
> and
> if so, how do you let SQL Server know which database server is to be used?
>
Yes. In 2005 you can execute arbitrary batches, including stored procedures
and DDL, at remote servers with the EXEC ... AT statement.
eg:
exec ( '
ALTER DATABASE AdventureWorks SET QUOTED_IDENTIFIER OFF
' ) at SQL02
David

Allowing users to schedule jobs in Management Studio

How do I grant a non sysdba user who has bulkadmin and dbcreator rights to schedule jobs on databases they've created?

The user is a developer and we dont want to give him sysdba rights.

try if this works

use the Execute AS command which is used to impersonate any user

havent done much of a research on that one but u can easily get stuff on BOL

|||http://www.sql-server-performance.com/faq/sqlviewfaq.aspx?topicid=12&faqid=137 for your information to accomplish the task.

Allow users to see logins (SQL2005)

Hello,
With SQL2000, the dbo's were able to see all the logins, so that they could
add them as users to their databases. We just starting setting up SQL
Server 2005 and it came to our attention that they no longer have this
ability. What rights do dbo's need to have in order to view all logins?
Thanks,
sck10
Hi,
To let we better understand your issue, could you please tell me more on
this issue so that I can reproduce your issue? You may also mail me
(changliw@.microsoft.com) a screenshot of your issue.
As far as I know, dbo is a database role, while login is for the SQL Server
instance. If you want to see all the logins of a SQL Server, the login
account should be a member of sysadmin. Even in SQL Server 2000, one
database owner cannot run sp_helplogins to see all the logins unless he is
a sysadmin. You can log on your SQL Server instance with a system
administrator and assign the login account with the server role sysadmin,
then try again.
Hope this helps! Please feel free to let us know if you have any other
questions or concerns.
Happy New Year!
Charles Wang
Microsoft Online Community Support
================================================== ====
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
================================================== ====
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== ====
|||Hi Charles,
I sent an email with the snapshots.
I am using Enterprise Mgr 2005 to connect to both a SQL2000 database and a
SQL2005 database. I am the dbo for databases on both machines. With
SQL2000, when I add a user to my database, I can see all the users in the
database. With SQL2005, I can only see myself and the sa.
Thanks again,
sck10
"Charles Wang[MSFT]" <changliw@.online.microsoft.com> wrote in message
news:p5CQ9BHMHHA.2304@.TK2MSFTNGHUB02.phx.gbl...
> Hi,
> To let we better understand your issue, could you please tell me more on
> this issue so that I can reproduce your issue? You may also mail me
> (changliw@.microsoft.com) a screenshot of your issue.
> As far as I know, dbo is a database role, while login is for the SQL
> Server
> instance. If you want to see all the logins of a SQL Server, the login
> account should be a member of sysadmin. Even in SQL Server 2000, one
> database owner cannot run sp_helplogins to see all the logins unless he is
> a sysadmin. You can log on your SQL Server instance with a system
> administrator and assign the login account with the server role sysadmin,
> then try again.
> Hope this helps! Please feel free to let us know if you have any other
> questions or concerns.
> Happy New Year!
> Charles Wang
> Microsoft Online Community Support
> ================================================== ====
> When responding to posts, please "Reply to Group" via
> your newsreader so that others may learn and benefit
> from this issue.
> ================================================== ====
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> ================================================== ====
>
|||Hi Charles
> As far as I know, dbo is a database role,
I have been always thinking that 'dbo' is 'privileged' user not a database
role.
"Charles Wang[MSFT]" <changliw@.online.microsoft.com> wrote in message
news:p5CQ9BHMHHA.2304@.TK2MSFTNGHUB02.phx.gbl...
> Hi,
> To let we better understand your issue, could you please tell me more on
> this issue so that I can reproduce your issue? You may also mail me
> (changliw@.microsoft.com) a screenshot of your issue.
> As far as I know, dbo is a database role, while login is for the SQL
> Server
> instance. If you want to see all the logins of a SQL Server, the login
> account should be a member of sysadmin. Even in SQL Server 2000, one
> database owner cannot run sp_helplogins to see all the logins unless he is
> a sysadmin. You can log on your SQL Server instance with a system
> administrator and assign the login account with the server role sysadmin,
> then try again.
> Hope this helps! Please feel free to let us know if you have any other
> questions or concerns.
> Happy New Year!
> Charles Wang
> Microsoft Online Community Support
> ================================================== ====
> When responding to posts, please "Reply to Group" via
> your newsreader so that others may learn and benefit
> from this issue.
> ================================================== ====
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> ================================================== ====
>
|||
Uri Dimant wrote:[vbcol=seagreen]
>Hi Charles
>I have been always thinking that 'dbo' is 'privileged' user not a database
>role.
>[quoted text clipped - 26 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums.aspx/sql-server/200701/1
|||Hi Charles
dbo is NOT a database role, it is a privileged database user. There is a
database role called db_owner, of which the dbo user is always a member.
It's hard enough understanding logins, users and roles, we need to be really
careful to use these terms correctly.
Thanks
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"Charles Wang[MSFT]" <changliw@.online.microsoft.com> wrote in message
news:p5CQ9BHMHHA.2304@.TK2MSFTNGHUB02.phx.gbl...
> Hi,
> To let we better understand your issue, could you please tell me more on
> this issue so that I can reproduce your issue? You may also mail me
> (changliw@.microsoft.com) a screenshot of your issue.
> As far as I know, dbo is a database role, while login is for the SQL
> Server
> instance. If you want to see all the logins of a SQL Server, the login
> account should be a member of sysadmin. Even in SQL Server 2000, one
> database owner cannot run sp_helplogins to see all the logins unless he is
> a sysadmin. You can log on your SQL Server instance with a system
> administrator and assign the login account with the server role sysadmin,
> then try again.
> Hope this helps! Please feel free to let us know if you have any other
> questions or concerns.
> Happy New Year!
> Charles Wang
> Microsoft Online Community Support
> ================================================== ====
> When responding to posts, please "Reply to Group" via
> your newsreader so that others may learn and benefit
> from this issue.
> ================================================== ====
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> ================================================== ====
>
|||Hi Kalen,
Thanks for your pointing it out!
I am sorry for using that wrong words and I appologize for that.
Your explanation is meaningful here. I will pay attention to this from now
on!
Thank you!
Charles Wang
Microsoft Online Community Support
|||Hi sck10,
I had sent you an email for this issue.
I reproduced your issue now but I think that the behavior of showing logins
of SQL Server 2000 is not reasonable. I need to consult the SQL Server 2005
product team for this issue and I will let you know their replies as soon
as possible.
If you have any other questions or concerns, please feel free to contact
us. It is always our pleasure to be of assistance.
Sincerely yours,
Charles Wang
Microsoft Online Community Support
|||Hi Steven,
I got the response from SQL team. The reason is as following:
This is because of security restrictions to view the metadata in SQL Server
2005. A user can only see metadata that the user either owns or on which
the user has been granted some permission. This policy prevents users with
minimal privileges from viewing metadata for all objects in an instance of
SQL Server 2005.
However if you do not want this security, the following statement can be
used to override metadata-visibility limitations at the instance level. All
metadata in the instance will be visible to the granted user. Doing so
would allow the user to see all other logins not only logins but all
metadata which is not a recommended practice.
GRANT VIEW ANY DEFINITION TO <USERNAME>
The <username> has to be replaced by the actual user who needs this. This
statement has to be executed by sysadmin.
Hope this helps!
Please feel free to let me know if you have any other questions or concerns.
Best regards,
Charles Wang
Microsoft Online Community Support
|||You might also be interested in this article on Metadata Security that I
wrote for TechNet Magazine:
http://www.microsoft.com/technet/technetmag/issues/2006/01/ProtectMetaData/?rss=y
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"Charles Wang[MSFT]" <changliw@.online.microsoft.com> wrote in message
news:NngjapFNHHA.2304@.TK2MSFTNGHUB02.phx.gbl...
> Hi Steven,
> I got the response from SQL team. The reason is as following:
> This is because of security restrictions to view the metadata in SQL
> Server
> 2005. A user can only see metadata that the user either owns or on which
> the user has been granted some permission. This policy prevents users with
> minimal privileges from viewing metadata for all objects in an instance of
> SQL Server 2005.
> However if you do not want this security, the following statement can be
> used to override metadata-visibility limitations at the instance level.
> All
> metadata in the instance will be visible to the granted user. Doing so
> would allow the user to see all other logins not only logins but all
> metadata which is not a recommended practice.
> GRANT VIEW ANY DEFINITION TO <USERNAME>
> The <username> has to be replaced by the actual user who needs this. This
> statement has to be executed by sysadmin.
> Hope this helps!
>
> Please feel free to let me know if you have any other questions or
> concerns.
>
> Best regards,
> Charles Wang
> Microsoft Online Community Support
>

Allow users to see logins (SQL2005)

Hello,
With SQL2000, the dbo's were able to see all the logins, so that they could
add them as users to their databases. We just starting setting up SQL
Server 2005 and it came to our attention that they no longer have this
ability. What rights do dbo's need to have in order to view all logins?
Thanks,
sck10Hi,
To let we better understand your issue, could you please tell me more on
this issue so that I can reproduce your issue? You may also mail me
(changliw@.microsoft.com) a screenshot of your issue.
As far as I know, dbo is a database role, while login is for the SQL Server
instance. If you want to see all the logins of a SQL Server, the login
account should be a member of sysadmin. Even in SQL Server 2000, one
database owner cannot run sp_helplogins to see all the logins unless he is
a sysadmin. You can log on your SQL Server instance with a system
administrator and assign the login account with the server role sysadmin,
then try again.
Hope this helps! Please feel free to let us know if you have any other
questions or concerns.
Happy New Year!
Charles Wang
Microsoft Online Community Support
======================================================When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================|||Hi Charles,
I sent an email with the snapshots.
I am using Enterprise Mgr 2005 to connect to both a SQL2000 database and a
SQL2005 database. I am the dbo for databases on both machines. With
SQL2000, when I add a user to my database, I can see all the users in the
database. With SQL2005, I can only see myself and the sa.
Thanks again,
sck10
"Charles Wang[MSFT]" <changliw@.online.microsoft.com> wrote in message
news:p5CQ9BHMHHA.2304@.TK2MSFTNGHUB02.phx.gbl...
> Hi,
> To let we better understand your issue, could you please tell me more on
> this issue so that I can reproduce your issue? You may also mail me
> (changliw@.microsoft.com) a screenshot of your issue.
> As far as I know, dbo is a database role, while login is for the SQL
> Server
> instance. If you want to see all the logins of a SQL Server, the login
> account should be a member of sysadmin. Even in SQL Server 2000, one
> database owner cannot run sp_helplogins to see all the logins unless he is
> a sysadmin. You can log on your SQL Server instance with a system
> administrator and assign the login account with the server role sysadmin,
> then try again.
> Hope this helps! Please feel free to let us know if you have any other
> questions or concerns.
> Happy New Year!
> Charles Wang
> Microsoft Online Community Support
> ======================================================> When responding to posts, please "Reply to Group" via
> your newsreader so that others may learn and benefit
> from this issue.
> ======================================================> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> ======================================================>|||Hi Charles
> As far as I know, dbo is a database role,
I have been always thinking that 'dbo' is 'privileged' user not a database
role.
"Charles Wang[MSFT]" <changliw@.online.microsoft.com> wrote in message
news:p5CQ9BHMHHA.2304@.TK2MSFTNGHUB02.phx.gbl...
> Hi,
> To let we better understand your issue, could you please tell me more on
> this issue so that I can reproduce your issue? You may also mail me
> (changliw@.microsoft.com) a screenshot of your issue.
> As far as I know, dbo is a database role, while login is for the SQL
> Server
> instance. If you want to see all the logins of a SQL Server, the login
> account should be a member of sysadmin. Even in SQL Server 2000, one
> database owner cannot run sp_helplogins to see all the logins unless he is
> a sysadmin. You can log on your SQL Server instance with a system
> administrator and assign the login account with the server role sysadmin,
> then try again.
> Hope this helps! Please feel free to let us know if you have any other
> questions or concerns.
> Happy New Year!
> Charles Wang
> Microsoft Online Community Support
> ======================================================> When responding to posts, please "Reply to Group" via
> your newsreader so that others may learn and benefit
> from this issue.
> ======================================================> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> ======================================================>|||:(
Uri Dimant wrote:
>Hi Charles
>> As far as I know, dbo is a database role,
>I have been always thinking that 'dbo' is 'privileged' user not a database
>role.
>> Hi,
>> To let we better understand your issue, could you please tell me more on
>[quoted text clipped - 26 lines]
>> rights.
>> ======================================================--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200701/1|||Hi Charles
dbo is NOT a database role, it is a privileged database user. There is a
database role called db_owner, of which the dbo user is always a member.
It's hard enough understanding logins, users and roles, we need to be really
careful to use these terms correctly.
Thanks
--
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"Charles Wang[MSFT]" <changliw@.online.microsoft.com> wrote in message
news:p5CQ9BHMHHA.2304@.TK2MSFTNGHUB02.phx.gbl...
> Hi,
> To let we better understand your issue, could you please tell me more on
> this issue so that I can reproduce your issue? You may also mail me
> (changliw@.microsoft.com) a screenshot of your issue.
> As far as I know, dbo is a database role, while login is for the SQL
> Server
> instance. If you want to see all the logins of a SQL Server, the login
> account should be a member of sysadmin. Even in SQL Server 2000, one
> database owner cannot run sp_helplogins to see all the logins unless he is
> a sysadmin. You can log on your SQL Server instance with a system
> administrator and assign the login account with the server role sysadmin,
> then try again.
> Hope this helps! Please feel free to let us know if you have any other
> questions or concerns.
> Happy New Year!
> Charles Wang
> Microsoft Online Community Support
> ======================================================> When responding to posts, please "Reply to Group" via
> your newsreader so that others may learn and benefit
> from this issue.
> ======================================================> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> ======================================================>|||Hi Kalen,
Thanks for your pointing it out!
I am sorry for using that wrong words and I appologize for that.
Your explanation is meaningful here. I will pay attention to this from now
on!
Thank you!
Charles Wang
Microsoft Online Community Support|||Hi sck10,
I had sent you an email for this issue.
I reproduced your issue now but I think that the behavior of showing logins
of SQL Server 2000 is not reasonable. I need to consult the SQL Server 2005
product team for this issue and I will let you know their replies as soon
as possible.
If you have any other questions or concerns, please feel free to contact
us. It is always our pleasure to be of assistance.
Sincerely yours,
Charles Wang
Microsoft Online Community Support|||Hi Steven,
I got the response from SQL team. The reason is as following:
This is because of security restrictions to view the metadata in SQL Server
2005. A user can only see metadata that the user either owns or on which
the user has been granted some permission. This policy prevents users with
minimal privileges from viewing metadata for all objects in an instance of
SQL Server 2005.
However if you do not want this security, the following statement can be
used to override metadata-visibility limitations at the instance level. All
metadata in the instance will be visible to the granted user. Doing so
would allow the user to see all other logins not only logins but all
metadata which is not a recommended practice.
GRANT VIEW ANY DEFINITION TO <USERNAME>
The <username> has to be replaced by the actual user who needs this. This
statement has to be executed by sysadmin.
Hope this helps!
Please feel free to let me know if you have any other questions or concerns.
Best regards,
Charles Wang
Microsoft Online Community Support|||You might also be interested in this article on Metadata Security that I
wrote for TechNet Magazine:
http://www.microsoft.com/technet/technetmag/issues/2006/01/ProtectMetaData/?rss=y
--
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"Charles Wang[MSFT]" <changliw@.online.microsoft.com> wrote in message
news:NngjapFNHHA.2304@.TK2MSFTNGHUB02.phx.gbl...
> Hi Steven,
> I got the response from SQL team. The reason is as following:
> This is because of security restrictions to view the metadata in SQL
> Server
> 2005. A user can only see metadata that the user either owns or on which
> the user has been granted some permission. This policy prevents users with
> minimal privileges from viewing metadata for all objects in an instance of
> SQL Server 2005.
> However if you do not want this security, the following statement can be
> used to override metadata-visibility limitations at the instance level.
> All
> metadata in the instance will be visible to the granted user. Doing so
> would allow the user to see all other logins not only logins but all
> metadata which is not a recommended practice.
> GRANT VIEW ANY DEFINITION TO <USERNAME>
> The <username> has to be replaced by the actual user who needs this. This
> statement has to be executed by sysadmin.
> Hope this helps!
>
> Please feel free to let me know if you have any other questions or
> concerns.
>
> Best regards,
> Charles Wang
> Microsoft Online Community Support
>

Allow users to see logins (SQL2005)

Hello,
With SQL2000, the dbo's were able to see all the logins, so that they could
add them as users to their databases. We just starting setting up SQL
Server 2005 and it came to our attention that they no longer have this
ability. What rights do dbo's need to have in order to view all logins?
Thanks,
sck10Hi,
To let we better understand your issue, could you please tell me more on
this issue so that I can reproduce your issue? You may also mail me
(changliw@.microsoft.com) a screenshot of your issue.
As far as I know, dbo is a database role, while login is for the SQL Server
instance. If you want to see all the logins of a SQL Server, the login
account should be a member of sysadmin. Even in SQL Server 2000, one
database owner cannot run sp_helplogins to see all the logins unless he is
a sysadmin. You can log on your SQL Server instance with a system
administrator and assign the login account with the server role sysadmin,
then try again.
Hope this helps! Please feel free to let us know if you have any other
questions or concerns.
Happy New Year!
Charles Wang
Microsoft Online Community Support
========================================
==============
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
========================================
==============
This posting is provided "AS IS" with no warranties, and confers no rights.
========================================
==============|||Hi Charles,
I sent an email with the snapshots.
I am using Enterprise Mgr 2005 to connect to both a SQL2000 database and a
SQL2005 database. I am the dbo for databases on both machines. With
SQL2000, when I add a user to my database, I can see all the users in the
database. With SQL2005, I can only see myself and the sa.
Thanks again,
sck10
"Charles Wang[MSFT]" <changliw@.online.microsoft.com> wrote in message
news:p5CQ9BHMHHA.2304@.TK2MSFTNGHUB02.phx.gbl...
> Hi,
> To let we better understand your issue, could you please tell me more on
> this issue so that I can reproduce your issue? You may also mail me
> (changliw@.microsoft.com) a screenshot of your issue.
> As far as I know, dbo is a database role, while login is for the SQL
> Server
> instance. If you want to see all the logins of a SQL Server, the login
> account should be a member of sysadmin. Even in SQL Server 2000, one
> database owner cannot run sp_helplogins to see all the logins unless he is
> a sysadmin. You can log on your SQL Server instance with a system
> administrator and assign the login account with the server role sysadmin,
> then try again.
> Hope this helps! Please feel free to let us know if you have any other
> questions or concerns.
> Happy New Year!
> Charles Wang
> Microsoft Online Community Support
> ========================================
==============
> When responding to posts, please "Reply to Group" via
> your newsreader so that others may learn and benefit
> from this issue.
> ========================================
==============
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> ========================================
==============
>|||Hi Charles
> As far as I know, dbo is a database role,
I have been always thinking that 'dbo' is 'privileged' user not a database
role.
"Charles Wang[MSFT]" <changliw@.online.microsoft.com> wrote in message
news:p5CQ9BHMHHA.2304@.TK2MSFTNGHUB02.phx.gbl...
> Hi,
> To let we better understand your issue, could you please tell me more on
> this issue so that I can reproduce your issue? You may also mail me
> (changliw@.microsoft.com) a screenshot of your issue.
> As far as I know, dbo is a database role, while login is for the SQL
> Server
> instance. If you want to see all the logins of a SQL Server, the login
> account should be a member of sysadmin. Even in SQL Server 2000, one
> database owner cannot run sp_helplogins to see all the logins unless he is
> a sysadmin. You can log on your SQL Server instance with a system
> administrator and assign the login account with the server role sysadmin,
> then try again.
> Hope this helps! Please feel free to let us know if you have any other
> questions or concerns.
> Happy New Year!
> Charles Wang
> Microsoft Online Community Support
> ========================================
==============
> When responding to posts, please "Reply to Group" via
> your newsreader so that others may learn and benefit
> from this issue.
> ========================================
==============
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> ========================================
==============
>|||
Uri Dimant wrote:[vbcol=seagreen]
>Hi Charles
>I have been always thinking that 'dbo' is 'privileged' user not a database
>role.
>
>[quoted text clipped - 26 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200701/1|||Hi Charles
dbo is NOT a database role, it is a privileged database user. There is a
database role called db_owner, of which the dbo user is always a member.
It's hard enough understanding logins, users and roles, we need to be really
careful to use these terms correctly.
Thanks
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"Charles Wang[MSFT]" <changliw@.online.microsoft.com> wrote in message
news:p5CQ9BHMHHA.2304@.TK2MSFTNGHUB02.phx.gbl...
> Hi,
> To let we better understand your issue, could you please tell me more on
> this issue so that I can reproduce your issue? You may also mail me
> (changliw@.microsoft.com) a screenshot of your issue.
> As far as I know, dbo is a database role, while login is for the SQL
> Server
> instance. If you want to see all the logins of a SQL Server, the login
> account should be a member of sysadmin. Even in SQL Server 2000, one
> database owner cannot run sp_helplogins to see all the logins unless he is
> a sysadmin. You can log on your SQL Server instance with a system
> administrator and assign the login account with the server role sysadmin,
> then try again.
> Hope this helps! Please feel free to let us know if you have any other
> questions or concerns.
> Happy New Year!
> Charles Wang
> Microsoft Online Community Support
> ========================================
==============
> When responding to posts, please "Reply to Group" via
> your newsreader so that others may learn and benefit
> from this issue.
> ========================================
==============
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> ========================================
==============
>|||Hi Kalen,
Thanks for your pointing it out!
I am sorry for using that wrong words and I appologize for that.
Your explanation is meaningful here. I will pay attention to this from now
on!
Thank you!
Charles Wang
Microsoft Online Community Support|||Hi sck10,
I had sent you an email for this issue.
I reproduced your issue now but I think that the behavior of showing logins
of SQL Server 2000 is not reasonable. I need to consult the SQL Server 2005
product team for this issue and I will let you know their replies as soon
as possible.
If you have any other questions or concerns, please feel free to contact
us. It is always our pleasure to be of assistance.
Sincerely yours,
Charles Wang
Microsoft Online Community Support|||Hi Steven,
I got the response from SQL team. The reason is as following:
This is because of security restrictions to view the metadata in SQL Server
2005. A user can only see metadata that the user either owns or on which
the user has been granted some permission. This policy prevents users with
minimal privileges from viewing metadata for all objects in an instance of
SQL Server 2005.
However if you do not want this security, the following statement can be
used to override metadata-visibility limitations at the instance level. All
metadata in the instance will be visible to the granted user. Doing so
would allow the user to see all other logins not only logins but all
metadata which is not a recommended practice.
GRANT VIEW ANY DEFINITION TO <USERNAME>
The <username> has to be replaced by the actual user who needs this. This
statement has to be executed by sysadmin.
Hope this helps!
Please feel free to let me know if you have any other questions or concerns.
Best regards,
Charles Wang
Microsoft Online Community Support|||You might also be interested in this article on Metadata Security that I
wrote for technet Magazine:
http://www.microsoft.com/technet/te...p://sqlblog.com
"Charles Wang[MSFT]" <changliw@.online.microsoft.com> wrote in message
news:NngjapFNHHA.2304@.TK2MSFTNGHUB02.phx.gbl...
> Hi Steven,
> I got the response from SQL team. The reason is as following:
> This is because of security restrictions to view the metadata in SQL
> Server
> 2005. A user can only see metadata that the user either owns or on which
> the user has been granted some permission. This policy prevents users with
> minimal privileges from viewing metadata for all objects in an instance of
> SQL Server 2005.
> However if you do not want this security, the following statement can be
> used to override metadata-visibility limitations at the instance level.
> All
> metadata in the instance will be visible to the granted user. Doing so
> would allow the user to see all other logins not only logins but all
> metadata which is not a recommended practice.
> GRANT VIEW ANY DEFINITION TO <USERNAME>
> The <username> has to be replaced by the actual user who needs this. This
> statement has to be executed by sysadmin.
> Hope this helps!
>
> Please feel free to let me know if you have any other questions or
> concerns.
>
> Best regards,
> Charles Wang
> Microsoft Online Community Support
>

All my databases are missing

In EM that is, in QA if I use:

use master
select * from sysdatabases

I get:
(6 row(s) affected)

Server: Msg 220, Level 16, State 1, Line 1
Arithmetic overflow error for data type smallint, value = 42840.Trev@.Work (no.email@.please) writes:
> In EM that is, in QA if I use:
> use master
> select * from sysdatabases
> I get:
> (6 row(s) affected)
> Server: Msg 220, Level 16, State 1, Line 1
> Arithmetic overflow error for data type smallint, value = 42840.

Oops! I assume that this is the message that you get in Enterprise
Manager?

If you don't have SP3 installed, try to install it. The bug may have been
fixed.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland Sommarskog wrote:
> Trev@.Work (no.email@.please) writes:
>>In EM that is, in QA if I use:
>>
>>use master
>>select * from sysdatabases
>>
>>I get:
>>(6 row(s) affected)
>>
>>Server: Msg 220, Level 16, State 1, Line 1
>>Arithmetic overflow error for data type smallint, value = 42840.
>
> Oops! I assume that this is the message that you get in Enterprise
> Manager?
> If you don't have SP3 installed, try to install it. The bug may have been
> fixed.

No EM said nothing, just didn't list anything. There was nothing under
management either and the backups hadn't run.

I restarted the service and the databases re-appeared. There are some
that I had taken offline, these are now marked as offline/suspect.

SP3a is already installed. I wonder if there's a bug with taking
databases offline?

If I query sysdatabases in QA, it was OK until I included the version
column. The offline dbs had quite high numbers here, now showing 0 in
that column, most are showing 539. I don't know the significance of this
number.|||Trev@.Work (no.email@.please) writes:
> If I query sysdatabases in QA, it was OK until I included the version
> column. The offline dbs had quite high numbers here, now showing 0 in
> that column, most are showing 539. I don't know the significance of this
> number.

That's a version number for the database format. Anything but 539 sounds
highly suspcious.

What happens if you try to bring these databases online?

If these databases are production data, and you don't have a clean backup,
I think you need to open a case with Microsoft. Something appears to be
broken.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland Sommarskog wrote:
> Trev@.Work (no.email@.please) writes:
>>If I query sysdatabases in QA, it was OK until I included the version
>>column. The offline dbs had quite high numbers here, now showing 0 in
>>that column, most are showing 539. I don't know the significance of this
>>number.
>
> That's a version number for the database format. Anything but 539 sounds
> highly suspcious.
> What happens if you try to bring these databases online?
> If these databases are production data, and you don't have a clean backup,
> I think you need to open a case with Microsoft. Something appears to be
> broken.

Hi Erland, thanks for responding.

I brought all the databases online, all now show 539 for the version
except one called "WebCat", this was never taken offline as it's used on
a daily basis, this one shows null :-\.

All databases are backed up daily on a schedule, the webcat one doesn't
matter if it loses data as it's re-created every night anyway (it's just
a catalogue of files on a particular web server). I might just drop that
database and re-create it.|||Stranger still,

Taking a database offline now sets version to null, I can't however take
"WebCat" offline as it says it's in use (it isn't according to process
info), this is the one where version is already null.|||Trev@.Work (no.email@.please) writes:
> Taking a database offline now sets version to null, I can't however take
> "WebCat" offline as it says it's in use (it isn't according to process
> info), this is the one where version is already null.

I have no idea what is going on with WebCat. I guess the reason that you
see NULL for the offline databases, is because this number is derived by
actually querying the database file itself, so if the database is offline,
the number cannot gotten hold off.

I checked a little further and found that version is in fact a
computed column:

version AS (convert(smallint,databaseproperty(name,'version') ))

At least here we see the source for the conversion error you had.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||I have had this issue several times, and discovered a post from Dan
Carollo in 2002
that led me to the fix.

http://groups-beta.google.com/group...728290bf2776908

Perhaps the version for WebCat got corrupted and if you run the command
in QA to bring it back online, it might fix that corruption.

alter database WebCat
set online

That is what I was able to do and Enterprise Manager works again. In
the past, I have just had to reinstall SQL replacing the Master
database which takes forever!

Michelle