Windows Services Groups

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Hari Seldon - 22 Sep 2006 13:14 GMT

I just installed SQL Server 2005 and am configuring it prior to migrating
some SQL Server 2000 databases from another server to it. I have two domain
accounts that I plan to use as the logon accounts for SQL Server services.

I noticed that the 2005 installation created several Windows groups on the
server(SQLServer2005MSSQLUser$. . ., SQLServer2005SQLAGentUser$. . ., etc.),
added my service accounts to those groups, created SQL Server logins for the
groups and added them to the sysadmin server role. The problem is that this
gives anyone with administrator rights on the server the ability to add
themselves to one of the Windows groups thereby giving them sysadmin rights
in SQL Server. I have extremely sensitive data in these databases and can't
let the network administrators have access to it.

Can I delete the logins for those Windows groups in SQL Server, add my
service accounts as SQL Server logins and then add them to the sysadmin
server role the way I had it set up in SQL Server 2000 or will that break
things?

Thanks!

Reply to this Message

DB2DOTNETCZAR - 23 Sep 2006 18:20 GMT

Hi Hari,

In the first place, I would recommend that you do not delete any logins that
you already have created.

To make sure that your network admins don't have access to SQL Server, simply
revoke login privileges to their accounts.

As it was in the SQ: Server 2000 world, one would use the

sp_revokelogin or sp_denylogin to the respective Windows Domain\User

The link below should be handy :)

http://doc.ddart.net/mssql/sql70/sp_da-di_21.htm

Hope this helps :)

Signature

Best Regards

Anil Mahadev

http://anilm001.myfreewebs.net/index2.php

http://www.db2india.org

Reply to this Message

Hari Seldon - 25 Sep 2006 13:35 GMT

Thanks. I hadn't thought about explicitly denying them login rights based on
their Windows logins. There are too many to do it individually, but I'm sure
they use Windows groups to give themselves access rights. I can probably
track down those group names and deny them login rights through their domain
admin group membership.

I tried to access the link you provided, but it keeps timing out on me.
That could be our network, but I thought I'd mention it and see if you could
verify it for me.

Thanks,
Hari

> Hi Hari,
>
[quoted text clipped - 13 lines]
>
> Hope this helps :)

Reply to this Message

DB2DOTNETCZAR - 25 Sep 2006 16:57 GMT

Hi Hari,

Sure what you can do , is make a list of all windows domains that belong to
the Network/Systems Group.

Deny them access to your database.

Make sure that that you have an X marked on the Domain users to prevent
unauthorized access.

And try to explore the new Securables feature provided in SQL Server 2005 :).

And BTW,

I tried the link and it works :)

Reply to this Message

DB2DOTNETCZAR - 25 Sep 2006 16:59 GMT

Hari,

I have copied the content for your convenience.

************************************************************************************************************************
sp_denylogin (T-SQL)

Prevents a Microsoft® Windows NT® user or group from connecting to Microsoft
SQL Server™.
Syntax

sp_denylogin [@loginame =] 'login'
Arguments

[@loginame =] 'login'
Is the name of the Windows NT user or group. login is sysname, with no
default. If the Windows NT user or group does not exist in SQL Server, it is
automatically added.

Return Code Values

0 (success) or 1 (failure)
Remarks

sp_denylogin can be used only with Windows NT accounts in the form Domain\
User, for example London\Joeb. sp_denylogin cannot be used with SQL Server
logins added with sp_addlogin.

Use sp_grantlogin to reverse the effects of sp_denylogin and allow the user
to connect.

sp_denylogin cannot be executed within a user-defined transaction.
Permissions

Only members of the securityadmin or sysadmin fixed server roles can execute
sp_denylogin.
Examples

This example prevents user Corporate\GeorgeW from logging in to SQL Server.

EXEC sp_denylogin 'Corporate\GeorgeW'

Or

EXEC sp_denylogin [Corporate\GeorgeW]

*************************************************************************************************************

Hope this helps :)

Signature

Best Regards

Anil Mahadev

http://anilm001.myfreewebs.net/index2.php

http://www.db2india.org

Reply to this Message

Hari Seldon - 25 Sep 2006 18:16 GMT

Thanks! I really appreciate the tips.

Hari

> Hari,
>
[quoted text clipped - 47 lines]
>
> Hope this helps :)

Reply to this Message

DB2DOTNETCZAR - 25 Sep 2006 18:52 GMT

Hi Hari,

Glad to be of HELP Hari,

Whats the use of knowledge when it cannot be shared to the people who need it

:).

B in touch ;)

Signature

Best Regards

Anil Mahadev

http://anilm001.myfreewebs.net/index2.php

http://www.db2india.org

Reply to this Message