April 26, 2010 at 12:16 pm
Hello All,
What are the fundamental differences between grant CONTROL SERVER and impersonate on login::sa
Which is safer and are there best practices concerning this specific area?
Thanks!
April 26, 2010 at 8:47 pm
Safer is to grant CONTROL SERVER. Here's why. CONTROL SERVER means an explicit DENY will be honored. CONTROL SERVER also means that any operation is undertaken with the identity of the login. So you'll have a good trail of who did what. sa is a member of the sysadmin fixed server role. Membes of that role bypass security checks. Explicit DENYs are not honored. Also, it may be recorded that sa performed the operation, and that doesn't tell you who actually did it.
Best practices is to use Windows logins through the use of a Windows group. Grant the Windows group either CONTROL SERVER or membership in the sysadmin fixed server role. Before SQL Server 2005, there wasn't CONTROL SERVER, so access was granted by making a login (such as the Windows group) a member of sysadmin. This practice hasn't changed even though there is more granular permission available now.
K. Brian Kelley
@kbriankelley
April 27, 2010 at 10:18 am
This is great information, thank you!
May 24, 2011 at 9:33 am
It looks like the SysAdmin role allows the login to "impersonate" DBO for all databases. I noticed that when I run username() function for a login with the sysadmin role, the function returns DBO. When I run this same function for a login with control server privileges, the function returns the login name.
May 24, 2011 at 10:00 am
mlundblad (5/24/2011)
It looks like the SysAdmin role allows the login to "impersonate" DBO for all databases. I noticed that when I run username() function for a login with the sysadmin role, the function returns DBO. When I run this same function for a login with control server privileges, the function returns the login name.
Yes, this is true, a member of sysadmin will map into the user dbo. In the case of someone with "just" CONTROL SERVER, you will see a user corresponding to their login.
K. Brian Kelley
@kbriankelley
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply