DBs, sizes etc

Anyone here with a ready to go sqlscript that lists all db's, files, sizes, owner etc? I guess it's a combination of sp_databases, sp_helpdb and sp_helpdb [db].There are scripts out there that will do this for you.

Be aware of how they report db sizings etc.|||www.sqlservercentral.com has all kinds of them. Registration is free. You can go to the scripts area and download to your heart's content.

Dbowner For The Model Database

How do I change the Database owner for a MODEL database?
When I open the properties for a Model database, it shows "UNKNOWN" as the database owner, and I can't run the command 'sp_changedbowner 'sa' as it's not allowed to run on master, model, and tempdb.
Any help?why would you want to, it's used as the template for future databases created on the server.|||Because I cannot run the sp_helpdb command.

dbo/owner

Can anyone think of a way to make this work...
We are restructuring our IT organization such that we will have schema
owners (can change tables and structure in a database) and t-sql/proc
writers (can only create procs and set permissions on those procs). However,
we want to use dbo as the owner of the procs (without assigning the
t-sql/proc writers to the db_owner role).The only way the users can create procedures with dbo as the owner is to be
a member of the db_owner role (or sysadmin).
However, you can use the sp_changeobjectowner procedure to change the owner
of the procedures after they are created. This will require a little more
administrative effort.
Rand
This posting is provided "as is" with no warranties and confers no rights.|||Michelle,
Have you tried making the t-sql/proc writers members of the db_ddladmin role
and having them create the proc with dbo as the owner. They will not be abl
e to execute the proc, just create it.
create procedure dbo.sptest
as
select * from authors
go

dbo qualified name error w Access 2003

I am the owner of a database with other users. I have given all users
db_owner role. My server is MSDE 2000.
I use the following code to import a spreadsheet.
docmd.transferspreadsheet acImport, acSpreadsheetTypeExcel97,
"dbo.DL_Funding_Data", "theFilename.xls", True
When I use an adp from Access 2000, this code executes successfully for all
users. When I use an adp from access 2003, this code gets error 3078 and
will only work if I remove the "dbo." from the name of the table.
thanks for any help,
Jerry
As I recall (and it's been a few years) the syntax required for
specifying SQL Server objects changed between A2000 and 2003. In 2000
you had to use the full syntax, ownername.objectname and 2003 is more
lenient. Since many people didn't bother implementing security
correctly, things ended up being owned by dbo, which is *not*
synonymous with db_owner, but is mapped to sysadmin. Something you
definitely don't want users being. FWIW, I definitely wouldn't use
DoCmd for DDL or DML, ever. It's just not a robust way of handling
things.
One tool you should use is SQL Profiler. Creating a trace allows you
to see the calls between Access and SQL Server, and is indispensable
for troubleshooting these things.
--Mary
On Mon, 22 Aug 2005 15:17:03 -0700, JerryWendell
<JerryWendell@.discussions.microsoft.com> wrote:

>I am the owner of a database with other users. I have given all users
>db_owner role. My server is MSDE 2000.
>I use the following code to import a spreadsheet.
>docmd.transferspreadsheet acImport, acSpreadsheetTypeExcel97,
>"dbo.DL_Funding_Data", "theFilename.xls", True
>When I use an adp from Access 2000, this code executes successfully for all
>users. When I use an adp from access 2003, this code gets error 3078 and
>will only work if I remove the "dbo." from the name of the table.
>thanks for any help,
>Jerry
|||Thanks for the recommendations Mary. I'll look into removing DoCmd and
obtaining the SQL Profiler.
I'm waiting to get my users all at Access 2003 to minimize the version
problems.
Thanks!
"Mary Chipman [MSFT]" wrote:

> As I recall (and it's been a few years) the syntax required for
> specifying SQL Server objects changed between A2000 and 2003. In 2000
> you had to use the full syntax, ownername.objectname and 2003 is more
> lenient. Since many people didn't bother implementing security
> correctly, things ended up being owned by dbo, which is *not*
> synonymous with db_owner, but is mapped to sysadmin. Something you
> definitely don't want users being. FWIW, I definitely wouldn't use
> DoCmd for DDL or DML, ever. It's just not a robust way of handling
> things.
> One tool you should use is SQL Profiler. Creating a trace allows you
> to see the calls between Access and SQL Server, and is indispensable
> for troubleshooting these things.
> --Mary
> On Mon, 22 Aug 2005 15:17:03 -0700, JerryWendell
> <JerryWendell@.discussions.microsoft.com> wrote:
>
>

dbo owner

We are using SQL Express and I have a problem with dbo.

On my machine if I logon to SQL Express using windows authentication

and create a new database I automatically have db_owner role membership

on the new database.

On a colleagues machine, if he logs onto his SQL Express using windows authentication

and creates a new database he does NOT have db_owner role membership

on the new database.

How come?

I have checked pretty much everythng - windows built in admins, SQL express sysadmin roles

service pack versions but I can not find any difference in setup.

What should I do to his machine/SQL Express setup so he automatically has db_owner role membership

for every newly created database?

Thanks

Charlie

Hi, this sounds strange. The database creator should have db_owner role memebership by default. Which ower is assigned to the new database ? Is it dbo ? Is your Account you are using for the creation of the database part of the sysadmin role ? Then you probably already have dbo Owner rights through the group memerbership.

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

I agree with Jens. This sounds strange, but most likely you get dbo access via sysadmin membership. I would first recommend trying the following query:

SELECT is_rolemember( 'db_owner' )

Members of db_owner as well as DBO should return 1 on the previous query.

I would also suggest trying the following query:

select name, type, usage from sys.login_token

order by type, usage, name

select name, type, usage from sys.user_token

order by type, usage, name

You should be able to see the login token as well as the user token on the current DB.

If the login token has an entry for sysadmin(sysadmin, SERVER ROLE, GRANT OR DENY) , then you should automatically be mapped to DBO on any DB. If you are not sysadmin, then pay attention to the user token.

Please let us know the results.

-Raul Garcia

SDE/T

SQL Server Engine

dbo owner

Hi Everyone,
I'm still with this problem and now I have little bit of time to find the
solution.
After any user import data to a table I want to have that table as dbo
owner.
I dont want to give my users full administrator permisssions.
I try to setup in each database access as db_owner, db_accesmin,
db_dlladmin but no luck
I know I can change the owner later with sp_changeobjectowner but sometimes
I run crazy with more than three users at the same time.
How can I fix this issue?
Tks
JFBCould you be more specific?
Do you use qualified object names? (two-part names: owner.object_name)
ML|||Yeah, a sample table, user, and a query you are trying to run in a script
would really make this easier.
Otherwise the best answer (as usual) is 42. Sadly we still don't know the
question to that answer either :)
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"ML" <ML@.discussions.microsoft.com> wrote in message
news:77548CF2-DE05-4C93-A509-4BDF04EF49C8@.microsoft.com...
> Could you be more specific?
> Do you use qualified object names? (two-part names: owner.object_name)
>
> ML|||Hi
If the structure of the data file is fixed, then you can create a DTS
package and schedule it as a job to import the data. This will mean that the
user does not have to "manually" import the data. You table structure (and
owner) can then be fixed. If you wish to manipulate the data it may be
worthwhile loading the data into a set of holding tables, where you can
subsequently work with that data and load it into your destination tables.
For more information on DTS look up DTS in books online and at l]
For example if you wish to process several files you can loop through all
the files in a given directory using the example code at
[url]http://www.sqldts.com/default.aspx?246" target="_blank">www.sqldts.com.[/ur
l]...efault.aspx?246
John
"JFB" wrote:

> Hi Everyone,
> I'm still with this problem and now I have little bit of time to find the
> solution.
> After any user import data to a table I want to have that table as dbo
> owner.
> I dont want to give my users full administrator permisssions.
> I try to setup in each database access as db_owner, db_accesmin,
> db_dlladmin but no luck
> I know I can change the owner later with sp_changeobjectowner but sometime
s
> I run crazy with more than three users at the same time.
> How can I fix this issue?
> Tks
> JFB
>
>|||Tks for you answer, I have DTS packages in my enviroment.
What I need is to fix this dbo when the user import data from a text file
manually
Tks
JFB
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:1364EA0B-E2AA-416F-B34F-275CA4B57CC7@.microsoft.com...
> Hi
> If the structure of the data file is fixed, then you can create a DTS
> package and schedule it as a job to import the data. This will mean that
> the
> user does not have to "manually" import the data. You table structure
> (and
> owner) can then be fixed. If you wish to manipulate the data it may be
> worthwhile loading the data into a set of holding tables, where you can
> subsequently work with that data and load it into your destination tables.
> For more information on DTS look up DTS in books online and at
> www.sqldts.com.
> For example if you wish to process several files you can loop through all
> the files in a given directory using the example code at
> http://www.sqldts.com/default.aspx?246
>
> John
> "JFB" wrote:
>|||Hi
It is not clear how/why you allow users to import data manually.
John
"JFB" wrote:

> Tks for you answer, I have DTS packages in my enviroment.
> What I need is to fix this dbo when the user import data from a text file
> manually
> Tks
> JFB
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:1364EA0B-E2AA-416F-B34F-275CA4B57CC7@.microsoft.com...
>
>|||Well each applicator developer has sql enterprise manager to conect to our
sql server with permissions on the database that they are working on it.
Base on bad experiences I don't want they to have full admin permissions.
But they need to import data manually for different purpose.
(analize data, test sp's, test vb app, ...)
Tks
JFB
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:CD8D19DB-CF63-4410-965D-11D31A092965@.microsoft.com...
> Hi
> It is not clear how/why you allow users to import data manually.
> John
>
> "JFB" wrote:
>|||What I need is to fix this dbo when the users(developers) import data from a
text file manually
Tks
JFB
"Louis Davidson" <dr_dontspamme_sql@.hotmail.com> wrote in message
news:ebjR8RVnFHA.1948@.TK2MSFTNGP12.phx.gbl...
> Yeah, a sample table, user, and a query you are trying to run in a script
> would really make this easier.
> Otherwise the best answer (as usual) is 42. Sadly we still don't know the
> question to that answer either :)
> --
> ----
--
> Louis Davidson - http://spaces.msn.com/members/drsql/
> SQL Server MVP
>
> "ML" <ML@.discussions.microsoft.com> wrote in message
> news:77548CF2-DE05-4C93-A509-4BDF04EF49C8@.microsoft.com...
>|||Hi
I have never come across the situation where a programmer has needed to
create tables for testing. If they have test data it should already be
in the destination table format; if this is from a previous version of
the database, then the data is re-exported after an upgrade; if this a
formal import process then the table definitions/process should be
formalised and the dbo owner in place.
If they do need to carry out an activity that may impact other users
then you could give them their own database and make them dbo.
John

dbo Owner

Dear All,
How to setting certain user so when they create stored procedure, the
stored procedure belongs to dbo.
Thanks & Regards
Yap MichaelHi
Add the user to dbo_owner database role. If the user is a member of sysadmin
role so any objects he/she will be created with dbo. prefix. On the other
hand if the user is a member of dbo_owner database role asnd not a sysadmin
,so she/he will need to specify dbo with creation a object
User Alex
--
CREATE TABLE dbo.MyTable (col INT NO NULL)
--
CREATE TABLE MyTable (col INT NO NULL)
I will see Alex.MytTable ( because he does not specify the DBO user name)
"Yap Michael" <yapmichael2000@.gmail.com> wrote in message
news:%238pEs5%23SGHA.5924@.TK2MSFTNGP09.phx.gbl...
> Dear All,
> How to setting certain user so when they create stored procedure, the
> stored procedure belongs to dbo.
> Thanks & Regards
> Yap Michael

dbo Cannot Add Users in SQL DB

I'm using SQL 2000 and I'm the database owner. I have been running tests with stored procedures to add logins, revoke logins, add db access, revoke db access etc.

Now I can't seem to add users to the database. I've tried using 'dbo' and 'domain\my_username' but I'm denied permission to run any stored procedures to try and add myself to the db_accessadmin or sysadmin roles.

I've tried the New Database User dialog and choose <new> under login name. I browse for the user name on my domain, click OK, and I get the message 'You must be logged in as 'sa' or a member of sysadmin or securityadmin to perform this operation.'

I thought the dbo always had full permissions on his database. This doesn't make sense to me. :confused:

The only thing I can figure is my permissions have been revoked on the master db.

Can anyone help?A DBO has full permissions on his own database, but adding a login is a server level task. You need to have the administrator add new logins for you, or be added to the securityadmin server role. You can, of course, add other logins that already exist as users in your database. You just can't create new logins.|||Oh and I'm using NT authentication.

I forgot to add that I have always been able to create logins until now by browsing for the user on our network. I was also able to create databases on the SQL Server before now. Now the option to create a new db is grayed out on the menu.

I can remove users (except in master) and delete databases but can't add users or create databases anymore.

There's no way anyone else would have changed my permissions. It had to have something to do with the stored procedures I ran yesterday. So it seems if I could revoke my permissions then there would be some way for me to re-grant them.

Would the 'sa' account be the admin for the server? I assume that would be the person that installed the SQL Server Application on our physical server. Would the only way to get my permissions back be to have him login under the 'sa' account and grant me permissions again?|||Not necessarily, but you may be lucky if the server was setup for Mixed Atuhentication. You may be out of luck if it is for Windows Authentication only. But even this can be fixed if you all were doing master backup. In this case restoring master to the time before you "ran those stored procedures" would fix the problem.

dbo

Is it possible to change the owner of a table?check,BOL for,

EXEC sp_changeobjectowner

Dbo

What happens if I am not the database owner and I create a table without using the word "dbo" ??
Thanks.The table will fall under your ownership. The table may not be visible or useable by other logins. If there is another table by the same name belonging to the dbo, then code executed by you will use your table while code executed by the dbo or other logins will use the dbo-owned table.

Its best to avoid this unless for some reason you need to store private data in the database.|||To avoid this, I simply do something that came over from Sybase:

sp_addalias 'username', dbo

The problem is is that it is not visible in EM, only if you select it from sysusers in the database.

Sorry, read the question wrong, nevermind...|||I know there may be a need to have different owners for different objects...

1). I have just never found one
2). I've seen 3rd party vendors do this, when a simple different table name would have been better
3). It makes maintanence a nightmare
4). It does force me to write bullet proof utilities