Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
ddatta8
Starting Member
7 Posts |
 Posted - 2010-02-01 : 19:59:41
|
| 1)When I add a new user in SQL Server, I have to declare a default database, say DB1, for the user. What rights does the user have for DB1 in this case? Does it include ownership rights?2) What is the difference between schema and database in SQL Server? I thought that schema was for database layout but I found that schemas in SQL server included roles in database creation and server administration? What do we mean by the default schema of dbo?3) What do we mean by server roles like dbcreator? Does this mean such roles apply to creation and modification of any databases in the SQL server?Thanks. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2010-02-01 : 23:28:52
|
| 1. We leave it as master for the default database. The user does not have any permissions in that database except what public is granted.2. Check Books Online for information on schemas. We don't use schemas, except for dbo. To me it seems like Microsoft added it since Oracle has it.3. Check Books Online for what the different database roles are able to do.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog"Let's begin with the premise that everything you've done up until this point is wrong." |
 |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-02-01 : 23:29:07
|
1. default database doesn't grant any rights at all. you need to grant permissions you want database user to have.2. you're confusing schemas, as the term means 2 different things. you're correct that it means database layout. but in this case, it is simply a way to organize database objects and permissions. objects fall under schemas. users are granted permissions on schemas and/or objects3. yes. server roles are shortcuts for granting system-wide permissions.Should read up on all of this in BOL |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-02-02 : 02:31:12
|
| 1. Like Tara we leave Master as the default too. If the user's default database is dropped they can have a problem logging in (a problem if you are the only user on the server!). However, if the user has, say, SysAdmin rights they may accidentally create objects in MASTER which pollutes it ... so other DBAs choose a specific default database [i.e. not "master"] so that any accidents are contained!2. I think this came in with the original stuff from Sybase - back in version 6.0 or somesuch.We have designed databases where the table is owned by "dbo" - so dbo.MyObjects and then had views owned by teh user's login which were more restrictive - e.g. I only see my own objects - so then I have a view called Kristen.MyObjects - and that's what I see when I do "SELECT * FROM MyObjects".But now we don't use that at all, everything we build is owned by "dbo" |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2010-02-02 : 02:37:31
|
Is there anybody at all using schemas actively? I've never seen it except in the Adventureworks databases and personally I think schemas suck...  - LumbagoIf the facts don't fit the theory, change the facts. Albert Einstein |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-02-02 : 09:14:05
|
| I use them.Also, I always change default database from master. :) |
|
|
|
ddatta8
Starting Member
7 Posts |
Posted - 2010-02-02 : 12:24:15
|
| Thanks for all your replies. Does dbo stand for db owner? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
|
|
|
|
default db vs dbowner