Windows Authentication with EM
-
I am trying to implement Windows Authentication; for that purpose I created a login using a Windows group, assigned it process administrators and database creators server roles. I also assigned it db_owner database role. Here is the problem, when connecting to the SQL server using Windows Authentication and creating a new object (a table, sp) the owner of this new object is the person that created the object, not dbo. This causes issues when somebody else tries to use those new objects. How can these new objects be owned by dbo? Any ideas
Carlos
-
You can always use sp_changeobjectowner on them.
-
Thanks, I tried that but is there any way I can tell EM or Query Analyzer to assign dbo by default? It would become very complicated to try to find out real time when objects are being created. sps are not an issue since you can always use dbo.sp
Carlos
-
Logins assigned the system administrators (sysadmin) server role will automatically create objects belonging to dbo (look under dbo in BoL).
David
If it ain't broke, don't fix it...
-
In SQL2000 there are 2 ways to get new objects automatically created with the owner dbo.
1) If the account creating the object is a member of the sysadmin SQL Server role, all objects created by that user are automatically given the owner dbo.
2) If the account owning a database is also mapped to the dbo user in the database, then objects created by that account in that database are automatically given the owner dbo. As a database can have only 1 owner, then only 1 account per database can be set up in this manner.
Example: In our shop, the account MAIN\REMEDY is the owner of our Remedy database, and is mapped to dbo within the database. The Remedy application occasionally creates tables, and never specfies an owner on the Create, but expects the table to be called dbo.table on a Select. Because we have set up ownership in this way, the Remedy application is happy, and our security people are happy because MAIN\REMEDY priviliges are limited to the Remedy database only.
Note that adding a user to the Database Owner role in a database does not mean that objects they create have the owner dbo.
In SQL2005, you can grant any user the right to use a specific schema, and set the dbo schema to be the default for any number of users. In this way in SQL2005, a large number of accounts can have minimal privileges but still ensure that any objects they are allowed to create have the owner dbo.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
-
REMEDY ? As in Help Desk Tracking ?
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
-
Would it be too much to ask for your database owners/object creators to specify that the object should be owned by dbo (e.g. create table dbo.tablename... rather than just create table)?
Identifying and then correcting the ownership of the objects isn't particularly difficult unless somehow your users manage to create multiple objects of the same name with different owners (e.g. dbo.testable, usera.testtable, userb.testtable) - then things can get interesting, which one is the right one?
I've seen some pretty frustrated folks trying to figure out why they're getting different answers to the "same" query (e.g. select * from testtable) when there are multiple objects in the database with the same name but different owners.
In a perfect world your users/developers could be convinced to consistently specify objectowner.objectname at all times. Maybe make it part of the qa/code review process?
Joe
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply