Creating user to own schemas?

I have noticed that some folks create a user to own a schema, with both having the same name (ex: userowner = pfm schema = pfm) and others make dbo the owner for all their schemas. From what I can tell, it doesn't really matter; what does matter is if your database users are granted access to the schema. Since most users only have public rights and dbo has database owner rights, is there any particular reasoning to use one scenario over the other?

Besides better granularity in your DB application management, one very good reason is to have better isolation between different applications. For example, if all the schemas (and therefore all tables and modules by default) are owned by the same principal (i.e. dbo) it would be very difficult to control access to the data on principals who can create/execute modules (i.e. SPs) as ownership chaining will be available for all tables in the database.

-Raul Garcia

SDE/T

SQL Server Engine

|||

Thank you Raul, I agree this does make sense. I just tried to create a user (ampfm) and it failed due to their not being a login named ampfm. However, when I looked at logins, there does not seem to be one for sys or INFORMATION_SCHEMA. How were those users created without also requiring login accounts?

|||

Those are builtin-in principals, and they are not intended for any interactive usage. Because they are built-in and not intended for interactive usage the SIDs are set to NULL. I strongly recommend against reusing these built-in principals in any way other than the supported scenarios. Please consult BOL for more information on them.

If you want to create a user without a login you can use the WITHOUT LOGIN syntax:

CREATE USER [ampfm] WITHOUT LOGIN

go

I hope this information helps.

-Raul Garcia

SDE/T

SQL Server Engine

|||

Thanks again Raul. That is exactly what I needed and it worked great!

Creating Text File from Stored Procedure

Greetings,

I have a sp that dumps text into a textfile but I am having trouble
creating the textfile.

EXEC master.dbo.xp_cmdShell '\\servername\d$\The File\sub\filename.dat'

The directory "The File" has a space in it. I've tried putting the
carat ^ before the space, and putting double quotes...but I keep
getting this error

'\\servername\d$\The' is not recognized as an internal or external
command, operable program or batch file.

If I do EXEC master.dbo.xp_cmdShell '"\\servername\d$\The
File\sub\filename.dat"' I get the same thing.

If I do EXEC master.dbo.xp_cmdShell '""\\servername\d$\The
File\sub\filename.dat""' I get

'"\\servername\d$\The File\sub\filename.dat"'
is not recognized as an internal or external command, operable program
or batch file.

Does anybody see what I am doing wrong?At a quick glance, it looks like filename.dat is simply not an
executable file - what exactly are you trying to do?

Simon|||Hi

Enquote the filename with double quotes should have worked, therefore the
file may not exist or could be still open. If "\\servername\d$\The
File\sub\filename.dat" from a command prompt says the file does not exist
then it probably doesn't or permissions are wrong.

If this is a batch file you may want to use a more appropriate extension.

John

"phantom" <phantomtoe@.yahoo.com> wrote in message
news:1126224325.956647.140030@.g43g2000cwa.googlegr oups.com...
> Greetings,
> I have a sp that dumps text into a textfile but I am having trouble
> creating the textfile.
> EXEC master.dbo.xp_cmdShell '\\servername\d$\The File\sub\filename.dat'
> The directory "The File" has a space in it. I've tried putting the
> carat ^ before the space, and putting double quotes...but I keep
> getting this error
> '\\servername\d$\The' is not recognized as an internal or external
> command, operable program or batch file.
> If I do EXEC master.dbo.xp_cmdShell '"\\servername\d$\The
> File\sub\filename.dat"' I get the same thing.
> If I do EXEC master.dbo.xp_cmdShell '""\\servername\d$\The
> File\sub\filename.dat""' I get
> '"\\servername\d$\The File\sub\filename.dat"'
> is not recognized as an internal or external command, operable program
> or batch file.
> Does anybody see what I am doing wrong?|||phantom (phantomtoe@.yahoo.com) writes:
> '"\\servername\d$\The File\sub\filename.dat"'
> is not recognized as an internal or external command, operable program
> or batch file.

Windows executes files depending on the suffixes. .dat is normally not
tied to any application. Thus, Windows does not know how to run the file.

If the file is a simple command file, the extension should be .BAT.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

creating new users with dbo permissions

I am trying to create a new user for a SQL Server database and use the credentials in an
ASP.NET app.

Problem is dbo permissions are not being applied to the database objects when I set up a
new user (Logins -> New Logins) with SQL Server Authentication, set the default database to
the database I want.

I then get an error message saying that the user has not been granted access to their default
database : DBNAME and therefore will not be able to gain access to their default database.

I'm not sure why this is as I am logged in as sa.

Anyway, when I ignore the error and set-up this new user through the Users section of the
database none of the dbo permissions carry over.

Can anyone help?
Cheers.SQL Server's default authentication is via Windows only and you have to invoke
authentication via SQL Server usernames/passwords by right clicking on the server name, selecting the Security tab and making the change.

Creating databases under MS SQL 2005 Server (SP-2): dbo user role

Good Day,

I am having a problem with creating databases in MS SQL 2005 Server. When I attempt to assign an User Mapping for my SQL user account to the newly created database, the "dbo" user and schema is already assigned. When I try to drop this user in favor of my own, I receive an error message: Cannot alter the user "dbo" (Microsoft SQL Server, Error: 15150). I am connected to my database engine through the "sa" account.

Regards,

Keith

Hi Keith,

The login that creates the database is mapped to a special user called dbo. The dbo is the owner of the database and can not be dropped. The dbo schema is associated with the dbo user so it also can not be dropped.

The sa account will represent itself as dbo in all databases.

to check for yourself: select user_name() when logged in as sa.

Hope this helps,

-Steven Gott

SDE/T

SQL Server

|||

I have a database that must have a unique schema and user account assigned. I want to exchange the default "dbo" user with my own.

Regards,

Keith

|||

Create a new schema and assign the schema owner as whatever user you want to use:

CREATE SCHEMA YourSchemaName AUTHORIZATION YourUserName

If there are already objects in the dbo schema that you need to be in YourSchema, you change the schema with:

ALTER SCHEMA YourSchemaName TRANSFER dbo.ObjectNameToTransfer

You can then assign users the default schema of YourSchemaName. You can change the default schema for exsiting users with:

ALTER USER YourUserName WITH DEFAULT_SCHEMA = YourSchemaName

-Sue

|||

Here is the error that I receive when trying to create a new schema for my database:

Msg 15151, Level 16, State 1, Line 1
Cannot find the user 'cqadmin', because it does not exist or you do not have permission.
Msg 2759, Level 16, State 0, Line 1
CREATE SCHEMA failed due to previous errors.

My user, "cqadmin" has public access of the database in question.

Creating databases under Master

When creating databases is it a good idea to use the master.dbo.sysdatabases database and then create databases under this.

An example I saw for a forum app did this:

IF EXISTS(SELECT name FROMmaster.dbo.sysdatabasesWHERE name= N'EggHeadCafeForum')

DROP DATABASE[EggHeadCafeForum]

GO

CREATE DATABASE[EggHeadCafeForum]ON(NAME= N'EggHeadCafeForum_Data', FILENAME = N'C:\EggHeadCafeForum_Data.MDF' ,SIZE= 5, FILEGROWTH = 10%)LOG ON(NAME= N'EggHeadCafeForum_Log', FILENAME = N'C:\EggHeadCafeForum_Log.LDF' ,SIZE= 1, FILEGROWTH = 10%)

COLLATESQL_Latin1_General_CP1_CI_AS

GO

It would offer one advantage in terms of when having the site hosted all my databases could be moved under the masterdatabase,However I am thinking there may be disadvantages, one being I use theasp.net roles/admin database that isn't created under master

Any feedback would be great.

Cheers

Adam

You cannot create a database "under" master database. All DB's are at the same level. "sysdatabases" is a table located in master db that has a listing of all the databases on the server. So the code "IF EXISTS.." is only checking if the DB already exists by querying against the list and if it does, its dropping it and recreating it.

|||You can connect to master and create the database from there. The database you created however is not UNDER master. There isn't a hierarchy in databases.|||Thanks guys, see what you mean now|||Thanks guys, see what you mean now