• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1830
  • Last Modified:

db owner vs sa

is DB owner database role equivalent of SA for just that database?
0
anushahanna
Asked:
anushahanna
  • 4
  • 4
5 Solutions
 
MarblemanCommented:
Not really... sa is system administrator. You can add a user in the Security-Area and right click it -> select Propoerties.

There you'll see that the user can belong to server roles and on the Database-Access tab you can choose the databases that the user can access. Within this access you can put him into a database role (like db_owner) which means that the user is able to create tables and alter the database.
0
 
anushahannaAuthor Commented:
OK. What i meant is, just like sa has all permissions on the server (at the server level, and hence also at database level), db_owner has such privileges at a database level, right?

in other words, whatever a sa can do on a database, a db_owner can do the same in that database, right, in terms of permissions? they will have equal rights at that database, correct?
0
 
QlemoDeveloperCommented:
Yes. But a dbo does not have the often needed overall access privileges to e.g. see the session list, or which locks are on "his" database. A dbo might not be able to change security, or create an database link to another server.
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
QlemoDeveloperCommented:
BTW, sa is an account, while dbo is a role. That's another difference, of course ;-).
0
 
anushahannaAuthor Commented:
Qlemo, that is a good point that sa is a user/login, where as db_owner is something you give to a user/login. thanks.

the doc says db_owners can be do all configuration and maintenance activities on the database
http://msdn.microsoft.com/en-us/library/ms180977%28SQL.90%29.aspx

is there another reference of what exactly is and is not "configuration and maintenance activities on the database"

in your description, can you confirm i am co-relating correctly?
the session list - is that sp_who2?
locks - sp_lock?
not be able to change security - GRANT command?
database link to another server - Linked server?

thanks again.
0
 
QlemoDeveloperCommented:
Yes.
sp_who2 might only display your own, current session.
The Session List (2000) or Activity Monitor (2005 and up) might not show all sessions or sessions at all.
Don't know exactly about sp_lock, but think has similar restrictions.
0
 
anushahannaAuthor Commented:
so db_owner has comprehensive privileges on the database, but not as much as sa, right?

there is no other role more 'powerful' than db_owner, that you can assign, just for a database, is there?
0
 
QlemoDeveloperCommented:
If you ask for DB specific privs - db_owner is toplevel there.
0
 
anushahannaAuthor Commented:
Thanks Qlemo.
0

Featured Post

[Webinar] Improve your customer journey

A positive customer journey is important in attracting and retaining business. To improve this experience, you can use Google Maps APIs to increase checkout conversions, boost user engagement, and optimize order fulfillment. Learn how in this webinar presented by Dito.

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now