Security problem, changing db collation to fix?
-
Maybe an explicit deny?
SELECT DB_NAME() AS database_name,
PR.name AS principal_name,
PE.permission_name,
OBJECT_NAME(PE.major_id) AS object_name
FROM sys.database_principals PR
INNER JOIN sys.database_permissions PE
ON PR.principal_id = PE.grantee_principal_id
WHERE PR.name = 'yourgroupnamegoeshere'
AND PE.state = 'D'
-- Gianluca Sartori
-
Ignacio A. Salom Rangel (8/26/2011)
Try to give the new group you create in the AD rights on another database and see if then you can connect to the other database (On the same server).
good idea. not so easy to test but i will find a way.
Ignacio A. Salom Rangel (8/26/2011)
Where the user ever able to log into the database? and Did you or one of your colleagues change anything on the SQL Server?they have been blocked from the start afaik. no server changes, i'm the only dba.
Gianluca Sartori (8/26/2011)
Maybe an explicit deny?nope, not an explicit deny. none exist on the server.
off to a meeting till the end of day, looks like i'll pick this up on monday.
thanks for the help!
-
Gianluca Sartori (8/26/2011)
Maybe an explicit deny?SELECT DB_NAME() AS database_name,
PR.name AS principal_name,
PE.permission_name,
OBJECT_NAME(PE.major_id) AS object_name
FROM sys.database_principals PR
INNER JOIN sys.database_permissions PE
ON PR.principal_id = PE.grantee_principal_id
WHERE PR.name = 'yourgroupnamegoeshere'
AND PE.state = 'D'
Gianluca, the deny will not be on hte group, but on the users, since some users do still have access.
-
Ignacio A. Salom Rangel (8/26/2011)
Gianluca Sartori (8/26/2011)
Maybe an explicit deny?SELECT DB_NAME() AS database_name,
PR.name AS principal_name,
PE.permission_name,
OBJECT_NAME(PE.major_id) AS object_name
FROM sys.database_principals PR
INNER JOIN sys.database_permissions PE
ON PR.principal_id = PE.grantee_principal_id
WHERE PR.name = 'yourgroupnamegoeshere'
AND PE.state = 'D'
Gianluca, the deny will not be on hte group, but on the users, since some users do still have access.
Yes, nice catch.
-- Gianluca Sartori
-
and the problem is usually something very simple yet very hidden....
There was an orphaned login group on the server, with these users as members of the group. The login was for an old database that no longer exists on the server; it was moved to a different server. In the properties of the login, the default database was blank (nothing selected), because the old database no longer exists. Therefore, SQL was giving an error on the blank default database.
-
Great!
Glad you solved your issue.
-- Gianluca Sartori
-
OLDCHAPPY (8/31/2011)
and the problem is usually something very simple yet very hidden....There was an orphaned login group on the server, with these users as members of the group. The login was for an old database that no longer exists on the server; it was moved to a different server. In the properties of the login, the default database was blank (nothing selected), because the old database no longer exists. Therefore, SQL was giving an error on the blank default database.
I'm glad you solved! Thank you for sharing.
Viewing 8 posts - 16 through 23 (of 23 total)
You must be logged in to reply to this topic. Login to reply