May 20, 2009 at 4:57 pm
Hey yall,
I am so confused it's unreal, I have an AD group called ABC\IT-ABC Production Infastructure Users, I know it's spelled wrong, I didn't create the group haha. It is a universal group in AD, and has 15 members. I added it to my SQL instance and with the database ABCProduction as it's default and gave it access to only one schema called [IT Infrastructure]. It lets the users in the group connect to the sql instance, but gives them a select denied error when they try to select any data from tables in that schema.
So, frustrated I made that group a sysadmin, which makes it work, and decided to move on figuring out what the issue was. I had my AD admins create me another group called ABC\IT-ABC SQL TEST and another user that is ABC\myfirstname.mylastnametest and added this group to the sql instance with EXACTLY the same permissions, and this group WORKS.
I opened every dialog box of every configuration I could think of, AD, both univerral groups, both security groups. SQL login, both AD groups added with ABCProduction as their default database. Under securables they have only public on the database. Both groups have select permission granted on the schema the tables live in, but no other permissions.
Why would this work for one group and not the other, what am I missing?
May 20, 2009 at 6:29 pm
There is the possibility that the AD Account is in more than one AD Group, which can create a problem. You can get the AD groups with this SQL
exec master.dbo.xp_logininfo 'AAUSA\Carl.Federl','all'
They may be some subtle difference in permissions and the below SQL will report the differences.
DECLARE@GranteeFirstsysname
,@GranteeSecond sysname
-- change to the database user names to be compared
SET@GranteeFirst = 'DatabaseMailUserRole'
SET@GranteeSecond = 'TargetServersRole'
IF @GranteeFirst >= @GranteeSecond
BEGIN
RAISERROR ('@GranteeFirst must be less than @GranteeSecond',16,0)
END
IF OBJECT_ID ('tempdb..#Permissions') is not null drop table #Permissions
IF OBJECT_ID ('tempdb..#GranteeCombos') is not null drop table #GranteeCombos
CREATE TABLE #Permissions
(OwnerNamesysname
,ObjectNamesysname
,Granteesysname
,Grantorsysname
,ProtectTypesysname
,ActionCdsysname
,ColumnNamesysname
)
INSERT INTO #Permissions
EXECdbo.sp_helprotect
SELECTDISTINCT
First.Granteeas GranteeFirst
,Second.Granteeas GranteeSecond
INTO#GranteeCombos
FROM#Permissions as First
JOIN#Permissions as Second
on First.Grantee < Second.Grantee
SELECT 'Right held by First but not second'
,#GranteeCombos.GranteeFirst
,#GranteeCombos.GranteeSecond
,#Permissions.OwnerName
,#Permissions.ObjectName
,#Permissions.ProtectType
,#Permissions.ActionCd
,#Permissions.ColumnName
FROM#GranteeCombos
JOIN#Permissions
on #Permissions.Grantee = #GranteeCombos.GranteeFirst
WHERE#GranteeCombos.GranteeFirst= @GranteeFirst
AND#GranteeCombos.GranteeSecond= @GranteeSecond
EXCEPT
SELECT 'Right held by First but not second'
,#GranteeCombos.GranteeFirst
,#GranteeCombos.GranteeSecond
,#Permissions.OwnerName
,#Permissions.ObjectName
,#Permissions.ProtectType
,#Permissions.ActionCd
,#Permissions.ColumnName
FROM#GranteeCombos
JOIN#Permissions
on #Permissions.Grantee = #GranteeCombos.GranteeSecond
WHERE#GranteeCombos.GranteeFirst= @GranteeFirst
AND#GranteeCombos.GranteeSecond= @GranteeSecond
UNION ALL
SELECT 'Right held by Second but not First'
,#GranteeCombos.GranteeFirst
,#GranteeCombos.GranteeSecond
,#Permissions.OwnerName
,#Permissions.ObjectName
,#Permissions.ProtectType
,#Permissions.ActionCd
,#Permissions.ColumnName
FROM#GranteeCombos
JOIN#Permissions
on #Permissions.Grantee = #GranteeCombos.GranteeSecond
WHERE#GranteeCombos.GranteeFirst= @GranteeFirst
AND#GranteeCombos.GranteeSecond= @GranteeSecond
EXCEPT
SELECT 'Right held by Second but not First'
,#GranteeCombos.GranteeFirst
,#GranteeCombos.GranteeSecond
,#Permissions.OwnerName
,#Permissions.ObjectName
,#Permissions.ProtectType
,#Permissions.ActionCd
,#Permissions.ColumnName
FROM#GranteeCombos
JOIN#Permissions
on #Permissions.Grantee = #GranteeCombos.GranteeFirst
WHERE#GranteeCombos.GranteeFirst= @GranteeFirst
AND#GranteeCombos.GranteeSecond= @GranteeSecond
SQL = Scarcely Qualifies as a Language
May 21, 2009 at 8:19 am
Heya, that's a great script, and just as I thought it returns nothing, these two logins are identical in every way. I'm starting to think maybe the ones name is just to long and SQL doesn't like it. I can't think of anything else that would cause this one ad group login not to work when other ones do. I'm seriously at wits end.
Carl Federl (5/20/2009)
There is the possibility that the AD Account is in more than one AD Group, which can create a problem. You can get the AD groups with this SQL
exec master.dbo.xp_logininfo 'AAUSA\Carl.Federl','all'They may be some subtle difference in permissions and the below SQL will report the differences.
DECLARE@GranteeFirstsysname
,@GranteeSecond sysname
-- change to the database user names to be compared
SET@GranteeFirst = 'DatabaseMailUserRole'
SET@GranteeSecond = 'TargetServersRole'
IF @GranteeFirst >= @GranteeSecond
BEGIN
RAISERROR ('@GranteeFirst must be less than @GranteeSecond',16,0)
END
IF OBJECT_ID ('tempdb..#Permissions') is not null drop table #Permissions
IF OBJECT_ID ('tempdb..#GranteeCombos') is not null drop table #GranteeCombos
CREATE TABLE #Permissions
(OwnerNamesysname
,ObjectNamesysname
,Granteesysname
,Grantorsysname
,ProtectTypesysname
,ActionCdsysname
,ColumnNamesysname
)
INSERT INTO #Permissions
EXECdbo.sp_helprotect
SELECTDISTINCT
First.Granteeas GranteeFirst
,Second.Granteeas GranteeSecond
INTO#GranteeCombos
FROM#Permissions as First
JOIN#Permissions as Second
on First.Grantee < Second.Grantee
SELECT 'Right held by First but not second'
,#GranteeCombos.GranteeFirst
,#GranteeCombos.GranteeSecond
,#Permissions.OwnerName
,#Permissions.ObjectName
,#Permissions.ProtectType
,#Permissions.ActionCd
,#Permissions.ColumnName
FROM#GranteeCombos
JOIN#Permissions
on #Permissions.Grantee = #GranteeCombos.GranteeFirst
WHERE#GranteeCombos.GranteeFirst= @GranteeFirst
AND#GranteeCombos.GranteeSecond= @GranteeSecond
EXCEPT
SELECT 'Right held by First but not second'
,#GranteeCombos.GranteeFirst
,#GranteeCombos.GranteeSecond
,#Permissions.OwnerName
,#Permissions.ObjectName
,#Permissions.ProtectType
,#Permissions.ActionCd
,#Permissions.ColumnName
FROM#GranteeCombos
JOIN#Permissions
on #Permissions.Grantee = #GranteeCombos.GranteeSecond
WHERE#GranteeCombos.GranteeFirst= @GranteeFirst
AND#GranteeCombos.GranteeSecond= @GranteeSecond
UNION ALL
SELECT 'Right held by Second but not First'
,#GranteeCombos.GranteeFirst
,#GranteeCombos.GranteeSecond
,#Permissions.OwnerName
,#Permissions.ObjectName
,#Permissions.ProtectType
,#Permissions.ActionCd
,#Permissions.ColumnName
FROM#GranteeCombos
JOIN#Permissions
on #Permissions.Grantee = #GranteeCombos.GranteeSecond
WHERE#GranteeCombos.GranteeFirst= @GranteeFirst
AND#GranteeCombos.GranteeSecond= @GranteeSecond
EXCEPT
SELECT 'Right held by Second but not First'
,#GranteeCombos.GranteeFirst
,#GranteeCombos.GranteeSecond
,#Permissions.OwnerName
,#Permissions.ObjectName
,#Permissions.ProtectType
,#Permissions.ActionCd
,#Permissions.ColumnName
FROM#GranteeCombos
JOIN#Permissions
on #Permissions.Grantee = #GranteeCombos.GranteeFirst
WHERE#GranteeCombos.GranteeFirst= @GranteeFirst
AND#GranteeCombos.GranteeSecond= @GranteeSecond
May 21, 2009 at 9:54 am
Login names are stored as NVARCHAR(128) , so that is probably not the problem.
There could be a problem when the following occurs:
1. An AD Group is created
2. The AD Group is granted SQL Server login rights.
3. The AD Group is renamed in AD
4. A new AD Group is create with the same name as the previous AD Group.
SQL Server keeps the AD Group's SID, so the AD name change does not matter, but AD Group name in SQL Server is not updated, which leads to mass confusion.
Have you tried running sp_validatelogins ?
"with the database ABCProduction as it's default "
Re-check that the login's default database is the desired value. Take the following senario on a new SQL Server:
1. Create database A, which is assigned DBID of 5
2. Create database B, which is assigned DBID of 6
3. Create database C, which is assigned DBID of 7
4. Set the default DB of a login to C
5. Drop database A
6. Restore database C from a backup - the restored DB will have a DBID of 5 not 7.
Now, when the login connects, they will get an error message that the default database is not available.
SQL = Scarcely Qualifies as a Language
May 21, 2009 at 10:42 am
I have tried running sp_validatelogins, it returns nothing. This group has not been renamed in AD, it's only existed so long as I've been testing this out.
I wrote this query to check permissions
select users.name, users.type_desc, default_schema_name, p.permission_name, p.state_desc fromsys.database_permissions p
join sys.database_principals users
on users.principal_id = p.grantee_principal_id
where users.name not in ('public', 'guest')
order by users.name asc, p.permission_name asc
It returns this
ABC\IT-ABC Production Infastructure UsersWINDOWS_GROUPNULLCONNECT GRANT
ABC\IT-ABC Production Infastructure UsersWINDOWS_GROUPNULLEXECUTE GRANT
ABC\IT-ABC Production Infastructure UsersWINDOWS_GROUPNULLINSERT GRANT
ABC\IT-ABC Production Infastructure UsersWINDOWS_GROUPNULLREFERENCESGRANT
ABC\IT-ABC Production Infastructure UsersWINDOWS_GROUPNULLSELECT GRANT
ABC\IT-ABC Production Infastructure UsersWINDOWS_GROUPNULLUPDATE GRANT
ABC\IT-ABC Production Infastructure UsersWINDOWS_GROUPNULLVIEW DEFINITIONGRANT
ABC\IT-ABC TEST SQL WINDOWS_GROUPNULLALTER DENY
ABC\IT-ABC TEST SQL WINDOWS_GROUPNULLCONNECT GRANT
ABC\IT-ABC TEST SQL WINDOWS_GROUPNULLCONTROL DENY
ABC\IT-ABC TEST SQL WINDOWS_GROUPNULLDELETE DENY
ABC\IT-ABC TEST SQL WINDOWS_GROUPNULLEXECUTE GRANT
ABC\IT-ABC TEST SQL WINDOWS_GROUPNULLINSERT GRANT
ABC\IT-ABC TEST SQL WINDOWS_GROUPNULLREFERENCESGRANT
ABC\IT-ABC TEST SQL WINDOWS_GROUPNULLSELECT GRANT
ABC\IT-ABC TEST SQL WINDOWS_GROUPNULLSELECT GRANT
ABC\IT-ABC TEST SQL WINDOWS_GROUPNULLTAKE OWNERSHIPDENY
ABC\IT-ABC TEST SQL WINDOWS_GROUPNULLUPDATE GRANT
ABC\IT-ABC TEST SQL WINDOWS_GROUPNULLVIEW DEFINITIONGRANT
User Joshua.Austilltest that exists in the ad group [IT-ABC TEST SQL] can login and works just fine. User Debbie.Smith that exists in group [IT-ABC Production Infastructure Users] can not log in and work. It either gives her a connection failed SQLState: 28000 sql server error 18456 error. Or, if I grant sysadmin on the [IT-ABC Production Infastructure Users] group, she gets a select denied error.
May 21, 2009 at 12:12 pm
User Debbie.Smith that exists in group [IT-ABC Production Infastructure Users] can not log in and work. It either gives her a connection failed SQLState: 28000 sql server error 18456 error.
Error 18456 indicates a login failure but the root cause is not returned to the client but is recorded in the SQL Serve Errorlog file, so search for Debbie.Smith's login failure,such as:
2009-05-21 00:00:00.00 Logon Error: 18456, Severity: 14, State: #.
2009-05-2100:02:00.34 Logon Login failed for user 'Debbie.Smith'. [CLIENT: ]
Find the "state number" indicated in bold which translates to:
STATE DESCRIPTION
2 and 5 Invalid userid
6 Attempt to use a Windows login name with SQL Authentication
7 Login disabled and password mismatch
8 Password mismatch
9 Invalid password
11 and 12 Valid login but server access failure
13 SQL Server service paused
16 Login does not have access to the default database.
18 Change password required
27 Default database does not exist
SQL = Scarcely Qualifies as a Language
May 21, 2009 at 12:18 pm
Carl Federl (5/21/2009)
User Debbie.Smith that exists in group [IT-ABC Production Infastructure Users] can not log in and work. It either gives her a connection failed SQLState: 28000 sql server error 18456 error.
Error 18456 indicates a login failure but the root cause is not returned to the client but is recorded in the SQL Serve Errorlog file, so search for Debbie.Smith's login failure,such as:
2009-05-21 00:00:00.00 Logon Error: 18456, Severity: 14, State: #.
2009-05-2100:02:00.34 Logon Login failed for user 'Debbie.Smith'. [CLIENT: ]
Find the "state number" indicated in bold which translates to:
STATE DESCRIPTION
2 and 5 Invalid userid
6 Attempt to use a Windows login name with SQL Authentication
7 Login disabled and password mismatch
8 Password mismatch
9 Invalid password
11 and 12 Valid login but server access failure
13 SQL Server service paused
16 Login does not have access to the default database.
18 Change password required
27 Default database does not exist
Hey, I hadn't even thought to see if there was a different error in the event log, but your right, there is. It's
Login failed for user "ABC\Debbie.Smith". [Client xxx.xxx.xxx.xxx]
Error: 18456, Severity: 14, State: 11.
So apparently this means valid login but server access failure, I've never seen that one before, googe time haha.
May 21, 2009 at 12:26 pm
Found that a state of 3 means "maximum user connections exceeded" and the value can be checked by running
exec sp_configure 'user connections'
SQL = Scarcely Qualifies as a Language
May 21, 2009 at 3:37 pm
Carl Federl (5/21/2009)
Found that a state of 3 means "maximum user connections exceeded" and the value can be checked by runningexec sp_configure 'user connections'
I have yet to see an error state of 3, but that's good to know. One more thing to make this complicated is that adding Debbie.Smith to the [IT-ABC SQL TEST] group still gives the same error when she tries to connect, so, apparently this has something to do with the individual logins and not the groups.
May 22, 2009 at 3:16 pm
Does anyone know if this could have anything to do with the server being on a different domain than the users and groups? Like, my server is servername.domain1.system.root, and my users and groups are all in the domain2.system.root domain. My SQL service is running as a domain account from the same domain as the users and groups however. I'm reaching because this issues has gotten serious, and I need to figure it out.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply