| Author |
Topic |
|
dbthj
Posting Yak Master
143 Posts |
 Posted - 2013-07-23 : 16:22:28
|
| of the 150 SQL instances I administer, many are inherited or have databases created by an application installer. So a lot of the databases are owned by non-sa id's. I would like to change all of the owners to sa. Is there ever a downside to doing this? I can't think of one, can you? |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-07-24 : 02:28:26
|
| using sa account will give application with unrestricted priviledges. This is not recommended,. Better to add a separate id which application can use and give it only require permission to avoid any security issues. sa account is also disabled by default as a security measure in recent versionssa account is first point of focus for hackers who try to intrude into the system.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
 |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-07-24 : 08:25:51
|
quote:
Originally posted by visakh16
using sa account will give application with unrestricted priviledges. This is not recommended,. Better to add a separate id which application can use and give it only require permission to avoid any security issues. sa account is also disabled by default as a security measure in recent versionssa account is first point of focus for hackers who try to intrude into the system.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs
While all the points you are making are very valid and recommended best practices, if the implied recommendation to the OP is that the ownership of the databases should not be transferred to sa, that is the wrong recommendation. There is every reason to make sa the owner of the databases, and none that I know of to not do so. Not having an ordinary user be the owner of a database (with the additional privileges that comes along with it), ability to move databases between servers (because sa always exists and always has the same SID) etc. are some that I can think of of the top of my head to have sa as the owner of databases.If there are some reasons why sa should not be the owner of all databases, I would like to learn what those are. |
|
|
|
jeffw8713
Aged Yak Warrior
819 Posts |
Posted - 2013-07-29 : 14:16:10
|
| The downside is that the owner of the database will not be a user of the database. If that owner is also the user logging into SQL Server and you change the database owner - that login will no longer have rights to the database.If the owner of the database is the SQL Server service account, then you can change it with no problems.To avoid any issues, you need to review what permissions are required for the non-sa owner and make sure you set that up after changing the owner. The easiest method is to grant that user access as db_owner - but that is generally too high of privilege for application accounts. |
|
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-07-29 : 15:16:46
|
| Jeff, I concur with all of what you said. I was viewing a regular login not being the owner of the database as a positive thing to be celebrated rather than the other way around. Of course that login that lost the owner status would need to be examined and appropriate permissions granted to it. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-07-30 : 01:56:23
|
quote:
Originally posted by James K
quote:
Originally posted by visakh16
using sa account will give application with unrestricted priviledges. This is not recommended,. Better to add a separate id which application can use and give it only require permission to avoid any security issues. sa account is also disabled by default as a security measure in recent versionssa account is first point of focus for hackers who try to intrude into the system.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs
While all the points you are making are very valid and recommended best practices, if the implied recommendation to the OP is that the ownership of the databases should not be transferred to sa, that is the wrong recommendation. There is every reason to make sa the owner of the databases, and none that I know of to not do so. Not having an ordinary user be the owner of a database (with the additional privileges that comes along with it), ability to move databases between servers (because sa always exists and always has the same SID) etc. are some that I can think of of the top of my head to have sa as the owner of databases.If there are some reasons why sa should not be the owner of all databases, I would like to learn what those are.
My point was more regarding usage of sa as account for application connectivity. I was not challenging on the usage of sa as owner of the database.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
dbthj
Posting Yak Master
143 Posts |
Posted - 2013-07-30 : 09:37:34
|
| I have over 700 databases created by a non-sa id. Many of these databases and id's are a mystery to me. So I don't know what privileges the owner of the database needs. Clearly, the id has the ability to do what he wants in the database even though the ownership of the database did not create a user id for the owner and grant it any rights. What would be your advice? Create a user for the owner, add it to the db_owner role and then change the database ownership to sa (or some other sysadmin id)? |
|
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-07-30 : 10:06:15
|
There are two or three routes you can take:1. Leave everything as it is. Why break it if it ain't broke. The disadvantages of course are the baggage that comes with a regular user owning the database.2. Systematically change each database by 1. looking at which login owns the database 2. adding the corresponding user to db_owner role on the database 3. changing the database owner to sa 3. Systematically change each database by 1. looking at which login owns the database 2. examining the permissions that the corresponding user really needs on the database 3. explicitly granting that user the required privileges 4. changing the database owner to sa. I would think that #2 is the safest and easiest. Even so, I would do this very carefully i.e., test it under controlled environment, do the changes only in small manageable batches, give sufficient notice to the users/owners to make sure that they notify you of any unexpected behavior, consult the vendors if you have vendor databases etc. In other words, something that should be approached with fear and caution.I also want to tell you that I have never done anything on the scale you are envisioning. My current environment is very small with a handful of servers and only a few databases. So I don't have any practical experiences to relate. Someone who regularly manages a large number of servers (I am thinking of people like Tara Kizer) would have more insights and suggestions. |
|
|
|
dbthj
Posting Yak Master
143 Posts |
Posted - 2013-07-30 : 10:53:16
|
| Many of the databases in question were created by a domain id (or sql id) that installed or created an application.In some cases these were id's for people who left the company. Given the fact that it ain'tbroke and it is not even possible to figure out what permissions are granted in many cases, perhaps I shouldn't fix it. |
|
|
|
jeffw8713
Aged Yak Warrior
819 Posts |
Posted - 2013-07-30 : 13:25:22
|
| Just one clarification to the steps outlined by James: you have to change the owner before you can add that user to the database. If you attempt to add the user that is already the owner it will fail.If the database is owned by a domain account - and that individual is no longer part of the organization, you should be able to change that will no impact. The only place where you will run into problems is if there are services, application pools, etc... running under that domain account on the application/web servers. |
|
|
|
|
Downside to sa database ownership???