SQL Server & BI Blog by Andreas Wolter - Latest Comments

In response to: CONTROL SERVER vs. sysadmin/sa: permissions, system procedures, DBCC, automatic schema creation and privilege escalation - caveats

Thank you, S.E. Btw: this Blog is actually "closed". I have moved to http://andreas-wolter.com/en/blog/ Andreas

In response to: CONTROL SERVER vs. sysadmin/sa: permissions, system procedures, DBCC, automatic schema creation and privilege escalation - caveats

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:

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.

In response to: SQL Server Database Ownership: survey results & recommendations

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

In response to: SQL Server Database Ownership: survey results & recommendations

Hi Andreas, I have reposted my post on your new blog and would be interested in your findings.

In response to: SQL Server Database Ownership: survey results & recommendations

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.

In response to: SQL Server Database Ownership: survey results & recommendations

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

In response to: SQL Server Database Ownership: survey results & recommendations

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/

In response to: Schema-design for SQL Server: recommendations for Schema-design with security in mind

Hallo Andreas, vielen Dank für diesen sehr interessanten hilfreichen Artikel. Grüße Marcus