Builtin\Administrators cannot login in to SQL Server

 

If you add a windows login to administrators group and even though administrators group is part of SQL Server login you may not be able to login in to SQL Server using the windows login.

Similarly

1. You remove a windows login from a windows group and assume windows group is part of SQL server login had deny on certain objects in database.

2. Now you add the windows login explicitly to SQL Server logins and grant permissions on objects which had deny for windows group. Still the login will not be able to access the objects which have deny for the group and may raise 229  similar to one below

{The SELECT permission was denied on the object ”, database mssqlsystemresource’, schema ‘sys’. (Microsoft SQL Server, Error: 229) }

 

This can happen when SQL is creating the logintoken from LSACache.  (LSA Cache is not refreshed after the Admin2 is added to Administrators group)

1. Disable LSACache on the machine. Steps are included in http://support.microsoft.com/kb/946358.
2. Restart the machine.
3. Connect using the problematic login and try again.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s