February 19, 2017 at 7:37 am
I like to think I have a decent understanding of database ownership (server principals) and the db_owner fs role that would be comprised of users, but I wanted to triple check with all of you to see if what I want to do with a couple of databases makes sense or if there's good reasons not to change them to the way I'm thinking.
I'll start with my goal. What I'm wanting to do is change the owner of both databases to 'sa'
The two databases are on the same SQL Server instance (2016 Standard) and they're the only two databases on it.
The first database is currently owned by a local SQL Server login -- let's call it "genericlogin".
The second database is currently owned by a domain account -- let's call it "somedomain\genericlogin"
Both logins are mapped to users of the same name in their respective databases.
Both logins have the sysadmin server role (per the vendor).
Both databases use only the dbo schema
We don't use any explicit DENYs anywhere (which I think comes into play with an owner of a database vs db_owner role)
So, here are my concerns:
1) I really don't like how the second database is owned by a domain account and therefore tied to AD. I would at least want to change that to a local SQL Server login (i.e., "genericlogin").
2) As I mentioned previously, more so than just any local SQL Server login, I think the databases should be owned by "sa" for security and consistency with other databases.
Can I make these changes without fear that "genericlogin" (and "somedomain\genericlogin") would have fewer permissions net (and therefore potentially break the app)? I would think the fact that the logins as they stand right now are sysadmins, that it shouldn't affect them, but perhaps I'm missing a scenario that would cause a problem.
What do you think? I'm trying to get the database/logins/users configured in a way that makes sense to me from a database perspective without veering away from vendor-recommended settings and documentation (which who knows--could break the application and/or cause more difficulty if I were to need support).
Thanks in advance,
Mike
Mike Scalise, PMP
https://www.michaelscalise.com
February 19, 2017 at 9:28 am
You can change the database's ownership without an worry, but I have to admit that I think that you applicative users got way to many permissions. There is no reason that an applicative user will be a member in sysadmin server roles. Those users should get the permissions to read and update data and run stored procedures. They should not get permission to create and drop objects, users and other databases as they have now.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
February 20, 2017 at 3:38 am
Mike Scalise - Sunday, February 19, 2017 7:37 AMI like to think I have a decent understanding of database ownership (server principals) and the db_owner fs role that would be comprised of users, but I wanted to triple check with all of you to see if what I want to do with a couple of databases makes sense or if there's good reasons not to change them to the way I'm thinking.I'll start with my goal. What I'm wanting to do is change the owner of both databases to 'sa'
The two databases are on the same SQL Server instance (2016 Standard) and they're the only two databases on it.
The first database is currently owned by a local SQL Server login -- let's call it "genericlogin".
The second database is currently owned by a domain account -- let's call it "somedomain\genericlogin"Both logins are mapped to users of the same name in their respective databases.
Both logins have the sysadmin server role (per the vendor).
Both databases use only the dbo schema
We don't use any explicit DENYs anywhere (which I think comes into play with an owner of a database vs db_owner role)So, here are my concerns:
1) I really don't like how the second database is owned by a domain account and therefore tied to AD. I would at least want to change that to a local SQL Server login (i.e., "genericlogin").
2) As I mentioned previously, more so than just any local SQL Server login, I think the databases should be owned by "sa" for security and consistency with other databases.
Can I make these changes without fear that "genericlogin" (and "somedomain\genericlogin") would have fewer permissions net (and therefore potentially break the app)? I would think the fact that the logins as they stand right now are sysadmins, that it shouldn't affect them, but perhaps I'm missing a scenario that would cause a problem.
What do you think? I'm trying to get the database/logins/users configured in a way that makes sense to me from a database perspective without veering away from vendor-recommended settings and documentation (which who knows--could break the application and/or cause more difficulty if I were to need support).
Thanks in advance,
Mike
you cannot have a login mapped into a database via a database user and also have the same login set as the owner of the database, it will fail if you try to do so.
The login marked as the database owner maps into the database via the local database user dbo.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
February 20, 2017 at 4:10 am
Having sa own all databases isn't a good security practice. Have a look at this article. We go with number 5 in the Possibilities section here. Adi is right about your application users being sysadmin - you ought to challenge the vendor on why that is a requirement. Maybe it was only necessary so that the database could be created during installation?
John
February 20, 2017 at 6:27 am
John Mitchell-245523 - Monday, February 20, 2017 4:10 AMMaybe it was only necessary so that the database could be created during installation?John
Even then it's not usually necessary, there is a server role to allow logins to create databases
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
February 20, 2017 at 11:51 am
Adi Cohn-120898 - Sunday, February 19, 2017 9:28 AMYou can change the database's ownership without an worry, but I have to admit that I think that you applicative users got way to many permissions. There is no reason that an applicative user will be a member in sysadmin server roles. Those users should get the permissions to read and update data and run stored procedures. They should not get permission to create and drop objects, users and other databases as they have now.Adi
+1....
Something I have all too often experienced is that during the development phase, developers that have SYSADMIN rights make instance level changes in the development environment that for one reason or another can't be implemented in production. That leads to problems I can tell you......
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply