database owners, users, roles, security
-
I need some clarifying on security in sql server. Up until now, all of our databases have been owned by dbo. I just read an article from a few years back that recommend this is the way to go. here is a forum that discusses the article:
http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=29&messageid=1309&p=4
Ok, now, my problem is as such. We have clients that are moving away from dbo, and creating their own users, with similar dbo rights. I don't fully understnad how this works.
One thing that confuses me is, in the SQL Server Login Properties screen, for each database, there is the option for USER. What is this? I can type anything I want in there, after permitting that login to have permission to that database. Then when I go to that database, I was able to create tables (being that I had that permission), as that user, that I had just made up out of the blue. Why can I type anything I want over there? What does it mean? I can type dbo there as well.
I think I need some help with understanding basic sql server security. It seems rather complicated. We cannot just tell clients to make everything dbo, as they will not like that.
Any help would be appreciated.
-
Once a login has been mapped to dbo in your database, no other logins can be mapped to dbo in same database.
User can be added to dbo database role. After that, the user will be able to create objects that belong to dbo by creating the object with dbo prefix. If user do not specific dbo prefix, the object will be created and owned by that user.
-
Quote:
Once a login has been mapped to dbo in your database, no other logins can be mapped to dbo in same database.
I'm really not sure what you are referring to over here? Where is it that a user is MAPPED to dbo.
I need more clarification on exactly what DBO is?? I know DBO has server role of system administrator. But I can give that to anyone, as long as I'm logged in as someone wiht system administrator rights. Doesn't this inherintly map them to DBO???
-
DBO doesn't have sysadmin fixed server role . objects created by members of the sysadmin fixed server role (or by the dbo user) belong to dbo.
-
I still haven't gotten a GOOD explanation as to what dbo is. It is not a user. It is not a role. What is it??
-
The dbo is a user that has implied permissions to perform all activities in the database. Any member of the sysadmin fixed server role who uses a database is mapped to the special user inside each database called dbo. Also, any object created by any member of the sysadmin fixed server role belongs to dbo automatically.
-
dbo is the default schema SQLserver has implemented.
If a user (lets say User0 )performs "select * from mytable" SQLserver will first search an object named User0.mytable. if not found, it will then search an object dbo.mytable.
Now schema = userid execept for dbo. So you cannot create objects "owned" by a user who is not defined for the db.
In Yukon, they finaly implemented the schema concept.
exec sp_changedbowner @loginame = 'user1' ,@map = 'true'
This way you can see user1 as databaseowner in EM when you select the taskpad view at database level.
Now all objects user1 creates by default will be dbo-objects. (dbo.objectname)
All users that are member of the db-role db_owner can also create objects.
(let use User2) exec sp_addrolemember N'db_owner', N'User2'
Now if User2 wants the objects he creates to be dbo-objects, he will have to specify dbo at creation time.
So create table dbo.my_db_owner_role_table
in stead of create table my_db_owner_role_table (which will result in object User2.my_db_owner_role_table ).
There are some articles at http://www.sqlservercentral.com regarding.
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
Actually, SQL server will search for <owner>.<object> where <owner> is the current user. If it doesn't find an object matching, it drops back to dbo.<object>. Unless, of course, it's a stored procedure that begins with sp_ in which case it searches for it as master.dbo.<sp_ name> first. Prior to Yukon schema and user aren't separated hence the issues.
Logins are what grant access to SQL Server. Logins are mapped into databases as user. Within every database there are the potential for two special user accounts: dbo and guest.
Guest can be turned off on user databases and is only required on master and tempdb (thought it's on for msdb by default as well). The guest account is what a login uses if no explicit permissions have been granted into the database for said login. Northwind and pubs are two examples where this is true.
The dbo user = the database owner. Whoever owns the database is automatically mapped in as dbo. In addition, members of the sysadmin fixed server role map in as dbo unless they are explicitly mapped in with a user account. The dbo user bypasses all security checks.
K. Brian Kelley
@kbriankelley -
I dont' feel that these answers really answer what I'm getting at:
Quote:
Ok, now, my problem is as such. We have clients that are moving away from dbo, and creating their own users, with similar dbo rights. I don't fully understnad how this works.
One thing that confuses me is, in the SQL Server Login Properties screen, for each database, there is the option for USER. What is this? I can type anything I want in there, after permitting that login to have permission to that database. Then when I go to that database, I was able to create tables (being that I had that permission), as that user, that I had just made up out of the blue. Why can I type anything I want over there? What does it mean? I can type dbo there as well.
1) From what I understand, as long as anyone is a dbowner. If they create a table on the database that they are the dbowner of, the table becomes .dbo. Can there be multiple dbowners? if 2 people create tables as dbowners in the same database, will all of the tables be .dbo.?
2) I still don't understand, the Sql Server Login Properties Screen. Noone has addressed this question yet. What is the option for USER? I can type anything I want in there? Why is htat? (see above for full jist of the question)
-
1) Yes, multiple people can map into dbo. Also, there is a fixed database role called db_owner. Multiple users can be a member of that role.
When creating objects, those mapped into dbo will create objects (tables, stored procedures, etc.) owned by dbo unless they specify otherwise. Those who are merely members of db_owner will create objects owned by themselves if they don't specify an owner. A db_owner or dbo can specify the owner of an object on creation (as can a member of the db_ddladmin fixed datbase role) like so:
CREATE TABLE SomeUser.SomeTable (TableID int)
2) The reason you can enter anything is you define how the login maps in to the database, meaning you specify the name of the "user" the login is associated with. When you are doing this, you are granting the login the ability to access the database. It can do so as whatever user name you give it. This can be a bit confusing, so generally most folks keep the user name the same as the login name.
K. Brian Kelley
@kbriankelley -
How does one get mapped into dbo? By giving them system administrative rights only?
If I were to enter dbo into the "User" part of the login maps, would that mean anything? Or does it just mean I chose "dbo" (as apposed to Fred or Bob) as the name to access the database with? Even if the user is not a dbo, I can still type dbo over there (and confuse myself even more in doing so, but this is what hte system has doen for sa, which is part of what confused me in the first place)
Viewing 11 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply