| Author |
Topic |
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
 Posted - 2004-08-25 : 01:43:27
|
Hi All,I've tried this before, denying the builtin administrators from sql server coz I wanted to prevent other administrators not concerned with the sql services. Then, all sql services stopped.  Question: how do I deny some of the builtin administrators except for the account I'm using for the sql services?thanks in advance... |
|
|
mr_mist
Grunnio
1870 Posts |
Posted - 2004-08-25 : 03:01:59
|
| Add the service account(s) (or its own NT group) to your SQL Server logins as a system admin.Delete the Builtin/Administrators group from the SQL logins.Job done.-------Moo. :) |
 |
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2004-08-25 : 03:20:58
|
| Hi Moo,thanks for the prompt reply.That's what I did which actually forced me to reinstall before. Good thing the server isn't yet into prod back then.What do you think is the problem? Is this odd? |
|
|
|
mr_mist
Grunnio
1870 Posts |
Posted - 2004-08-25 : 03:40:21
|
| That is, indeed, odd.Did you restart the machine after changing the accounts?-------Moo. :) |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2004-08-25 : 03:45:34
|
Yes, which actually started the problem, since the setting was refreshed.So, i had no choice but to retain the builtin administrators and make sure no one is added to that group except for the netadmin and account for sql services.quote:
Originally posted by mr_mist
That is, indeed, odd.Did you restart the machine after changing the accounts?-------Moo. :)
|
|
|
|
mr_mist
Grunnio
1870 Posts |
Posted - 2004-08-25 : 03:48:55
|
quote:
Originally posted by jen
Yes, which actually started the problem, since the setting was refreshed.So, i had no choice but to retain the builtin administrators and make sure no one is added to that group except for the netadmin and account for sql services.
...Of course, all your Domain Admins belong to that group de facto, so that doesn't solve your problem.I can't understand why the server would not work. When you added the sql account to the logins, did you click "deny" on the builtin/admins group, or just delete the group?-------Moo. :) |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2004-08-25 : 03:56:23
|
| Here's what I did:1. added the sql service account to the local administrators on the machine2. added that account to sql server logins with sa privilege3. denied the builtin administratorsHmm.. it just hit me, I should have deleted it right instead of denying? Coz deny is an explicit command which overrides all other permissions? |
|
|
|
mr_mist
Grunnio
1870 Posts |
Posted - 2004-08-25 : 03:58:35
|
| I am thinking so, because it works fine if you just delete the group.-------Moo. :) |
|
|
|
mr_mist
Grunnio
1870 Posts |
Posted - 2004-08-25 : 04:02:07
|
Indeed BOL says ..quote:
When a permission is denied from a SQL Server user or Windows NT user account, the specified security_account is the only account affected by the permission. If a permission is denied from a SQL Server role or a Windows NT group, the permission affects all users in the current database who are members of the group or role, regardless of the permissions that have been granted to the members of the group or role. If there are permission conflicts between a group or role and its members, the most restrictive permission (DENY) takes precedence
-------Moo. :) |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2004-08-25 : 04:07:58
|
| Thanks Moo,you just made my day... |
|
|
|
mr_mist
Grunnio
1870 Posts |
Posted - 2004-08-25 : 04:13:48
|
| That's quite alright.I really must change my sig. Moo is a word I like rather than my name ;)-------Moo. :) |
|
|
|
|
builtin administrators