Admin access to SQL without server/domain admin access

David S

Admin access to SQL without server/domain admin access

by David S » Wed, 08 Jan 2014 13:31:33

We are using Win2k3 R2 with SQL 2000 in a domain environment.

Is it possible to create a domain group to grant admin level and user level access to SQL2000/2005 without giving users server admin or domain admin access

It has always been my impression that to have admin access to SQL that you had to at least had admin level access on the server.

Any clarification would be greatly appreciated.

Thanks!



Sue Hoegemeie

Admin access to SQL without server/domain admin access

by Sue Hoegemeie » Fri, 10 Jan 2014 14:32:34

Part of the problem may be from using a more generic phrase like "admin level access to SQL". What that means to you, I'm not sure. That could be interpreted as administering the server from the windows level or it could mean being a member of the sysadmins server role in SQL Server.

But you don't need to be a member of a windows windows administrator group (local administrator on the server or domain administrator in the domain) to be a member of the sysamin server role in SQL Server. A member of the sysadmin role in SQL Server can add other logins to the sysadmin role - and anything thing else they want to do as members of the sysadmins role bypass security checks and can do anything in SQL Server. This is what it sounds like you are asking about.

I have seen some similar confusion coming from this type of scenario:

You have your SQL Server running under a service account and that account is a member of the local administrators group on the server on which SQL Server is running

You are a member of the sysadmin server role in SQL Server

Some of the command you execute as a member of the sysadmin role will execute under the security context of the service account. Being that the service account is a member of the local administrators group, you are executing some of the commands as if you are a member of the local administrators group on that server.

But that scenario doesn't mean you are a member of the local administrators group or that you need to be a member of the local administrators group.

-Sue