SQL Server & BI Blog by Andreas Wolter - Latest Comments https://www.insidesql.org/blogs/andreaswolter/?disp=comments en-EU http://backend.userland.com/rss 60 andreaswolter [Member] in response to: CONTROL SERVER vs. sysadmin/sa: permissions, system procedures, DBCC, automatic schema creation and privilege escalation - caveats Mon, 15 Oct 2018 08:30:50 +0000 [Member] c8394@https://www.insidesql.org/blogs/ Thank you, S.E. Btw: this Blog is actually "closed". I have moved to http://andreas-wolter.com/en/blog/ Andreas https://www.insidesql.org/blogs/andreaswolter/2013/08/control-server-vs-sysadmin-sa-permissions-privilege-escalation-caveats#c8394 S.E. [Visitor] in response to: CONTROL SERVER vs. sysadmin/sa: permissions, system procedures, DBCC, automatic schema creation and privilege escalation - caveats Mon, 15 Oct 2018 07:37:11 +0000 S.E. [Visitor] c8393@https://www.insidesql.org/blogs/ The SQL Agent problem has been resolved with SQL Server 2016. When creating/modifying a job with/to a different owner, you receive the following messages, respectively: <p> Only a member of the sysadmin server role can <em><strong>add</strong></em> a job for a different owner with @owner_login_name. (Microsoft SQL Server, Error: 14515) </p> <p> Only a system administrator can <em><strong>reassign</strong></em> ownership of a job. (Microsoft SQL Server, Error: 14242) </p> I tested this with the GUI but as far as I checked the code, the check is present inside the called SPs. Only a member of the sysadmin server role can add a job for a different owner with @owner_login_name. (Microsoft SQL Server, Error: 14515)

Only a system administrator can reassign ownership of a job. (Microsoft SQL Server, Error: 14242)

I tested this with the GUI but as far as I checked the code, the check is present inside the called SPs.]]> https://www.insidesql.org/blogs/andreaswolter/2013/08/control-server-vs-sysadmin-sa-permissions-privilege-escalation-caveats#c8393 andreaswolter [Member] in response to: SQL Server Database Ownership: survey results & recommendations Thu, 30 Nov 2017 20:25:08 +0000 [Member] c8392@https://www.insidesql.org/blogs/ Hi Michael, I saw that. And only today I found time to do the repro - successfully, indeed. See my complete answer at: http://andreas-wolter.com/en/sql-server-database-ownership-survey-results-recommendations https://www.insidesql.org/blogs/andreaswolter/2014/06/sql-server-database-ownership-survey-results-recommendations#c8392 Michael [Visitor] in response to: SQL Server Database Ownership: survey results & recommendations Thu, 30 Nov 2017 10:01:39 +0000 Michael [Visitor] c8391@https://www.insidesql.org/blogs/ Hi Andreas, I have reposted my post on your new blog and would be interested in your findings. https://www.insidesql.org/blogs/andreaswolter/2014/06/sql-server-database-ownership-survey-results-recommendations#c8391 andreaswolter [Member] in response to: SQL Server Database Ownership: survey results & recommendations Sun, 12 Nov 2017 19:10:10 +0000 [Member] c8390@https://www.insidesql.org/blogs/ Hello Michael. First of all: My blog is now officially at http://andreas-wolter.com/en/blog/ You are welcome to post or even re-post your comment there. As of now to me this is very odd. Maybe I am missing something, but this is what you did: Create WindowsLogin (disabled in Domain) Alter database set authorization = thatLogin ->Login disappeared I have never seen that, but I will try a repro and get back to you, preferably on my new blog. Login disappeared I have never seen that, but I will try a repro and get back to you, preferably on my new blog.]]> https://www.insidesql.org/blogs/andreaswolter/2014/06/sql-server-database-ownership-survey-results-recommendations#c8390 Michael [Visitor] in response to: SQL Server Database Ownership: survey results & recommendations Wed, 08 Nov 2017 15:38:30 +0000 Michael [Visitor] c8389@https://www.insidesql.org/blogs/ Hi Andreas, thank you for a brilliant article. Can I ask, in your opinion, can you see an issue with the following scenario. We have created a domain account which is a member of Domain Users. It has Password Never Expires, User Cannot Change Password and Account is Disabled. I have created a new database with my SysAdmin account (which made me the owner) then changed the database owner with ALTER AUTHORIZATION ON DATABASE::[database_name] TO [new_Domain_account]; Now the owner of my new database is the Domain account that is actually disabled and when I check Logins on the SQL Instance it doesn't exist there..! Am I missing something as this seems to be secure but I can't believe that I haven't found any sort of reference to this approach on the Internet..! Thanks, Michael https://www.insidesql.org/blogs/andreaswolter/2014/06/sql-server-database-ownership-survey-results-recommendations#c8389 andreaswolter [Member] in response to: SQL Server Database Ownership: survey results & recommendations Tue, 07 Feb 2017 23:09:57 +0000 [Member] c8385@https://www.insidesql.org/blogs/ PS: Here the link, just stumbled over it: http://markread.net/2014/05/04/how-to-change-the-owner-of-a-secondary-replica-database-to-sa/ https://www.insidesql.org/blogs/andreaswolter/2014/06/sql-server-database-ownership-survey-results-recommendations#c8385 Marcus [Visitor] in response to: Schema-design for SQL Server: recommendations for Schema-design with security in mind Thu, 27 Oct 2016 11:28:22 +0000 Marcus [Visitor] c8363@https://www.insidesql.org/blogs/ Hallo Andreas, vielen Dank für diesen sehr interessanten hilfreichen Artikel. Grüße Marcus https://www.insidesql.org/blogs/andreaswolter/2016/02/schema-design-for-sql-server-recommendations-for-schema-design-with-security-in-mind#c8363