Unable to add Users Group to Security Logins [SQL Server 2005]

[StevenK]

We have recently had SQL Server 2005 set up for us on a new server for a client site.

When this was looked at at the end of last week it was seen that the 'DOMAIN-NAME\PROGRAM-NAME Users' group was listed in the 'Security' - 'Logins' option through SQL Server Management Studio.

Looking at this again today we find only 'sa' and 'BUILTIN\Administrators'.
The applications as set up at the end of last week still continue to work - so we have no security issue with this.

However when I try and re-add the 'DOMAIN-NAME\PROGRAM-NAME Users' group this gives the following message:
"Create failed for Login 'DOMAIN-NAME\PROGRAM-NAME Users'. (Microsoft.SqlServer.Smo)

Additional information
An exception occurred while executing a Transact-SQL statement or batch.
(Microsoft.SqlServer.ConnectionInfo)

User does not have permission to perform this action. (Microsoft SQL Server, Error: 15247)"


Why would this occur?
And how can we correct this to enable us to re-add this group as previously seen?

Any advice would be greatly appreciated.
Thanks in advance.

 

[MissTipps]

Is the account you're logging onto the server as a member of the Administrators group on the server?

MissTipps

CISSP, CEH, CEI, MCT, MCDBA, MCSE 2K3, CTT+, ECSA, Security+

 

[StevenK]

Yes. We're logging on to the server console (and SQL Server Management Studio) as the 'DOMAIN-NAME\Administrator' profile.

So I would have assumed this would give us sufficient rights - but doesn't appear to.

Steve

 

[MissTipps]

And you have, presumably, checked that BUILTIN\Administrators is a member of the System Administrators role?

Hmm, you could try connecting to the server as 'sa' to remove the Windows layer from things and see if you can creaet the login that way.

MissTipps

CISSP, CEH, CEI, MCT, MCDBA, MCSE 2K3, CTT+, ECSA, Security+

 

[StevenK]

I was told that the SQL Server 2005 installation was done with only Windows Authentication.
Would that mean that the usage of the 'sa' profile is still valid?

Thanks so far.
Steve

 

[MissTipps]

Ah, thats going to be a little more tricky then. If you navigate to the Security folder in Management Studio and then to Server Roles and double click 'sysadmin' do you see BUILTIN\Administrators there?

MissTipps

CISSP, CEH, CEI, MCT, MCDBA, MCSE 2K3, CTT+, ECSA, Security+

 

[StevenK]

No - all I see listed in the 'sysadmin' group is the 'sa' user profile.

One thing worth noting is that if I select one of the application databases - that I'd previously set the 'DOMAIN-NAME\PROGRAM-NAME Users' group to have access to - I can see this group ('DOMAIN-NAME\PROGRAM-NAME Users') listed in the 'Security' - 'Users' section of the selected database(s).

So this suggests that the previous entries are still held - and are still in effect - allowing this group access to these databases.

But this same group can't be seen at the upper level (oustide the scope of selected databases).

Does this provide any further clues?
Thanks again.
Steve

 

[StevenK]

Can anyone shed any light on how I can get the security issue resolved here?

I cannot see the 'SQL Server Agent' via the SQL Server Management Studio tool either - I don't know whether this is also down to the same issue.

Any thoughts would be greatly appreciated.
Thanks in advance.
Steve