Windows authentication only allows login with sysadmin role

Question

Windows authentication only allows login with sysadmin role

Viewing 15 posts - 16 through 30 (of 31 total)

You must be logged in to reply to this topic. Login to reply

sqldriver SSChampion Points: 10440 More actions · Answer 1

Yep, that's the story. Also, if I assign another role (so they're under public and setupadmin, or public and serveradmin), they can't log in.

The default db is master for all roles and live user logins.

Thanks

Beatrix Kiddo SSC-Dedicated Points: 32407 More actions · Answer 2

Ok, on the login dialogue box, under Options can you get them to specify the database name (by typing it in, not browsing for it)- see attached- then clicking Connect?

Beatrix Kiddo SSC-Dedicated Points: 32407 More actions · Answer 3

Related to this, if you check the permissions in the master database, has anybody removed access from the public role? It should be there, with a tick under Grant Connect (unless that is you have given this role elevated permissions for some reason, which is unlikely).

sqldriver SSChampion Points: 10440 More actions · Answer 4

In order:

Made no difference.

Public is wide open.

Thanks

Beatrix Kiddo SSC-Dedicated Points: 32407 More actions · Answer 5

It sounds like they're trying to connect to a database that doesn't exist, then. Are you auditing failed logins? If so check what it says in the SQL log. (Sorry if you've done that already!)

sqldriver SSChampion Points: 10440 More actions · Answer 6

They're connecting to master, which I promise, regular swear, and pinky swear exists. I'm not seeing these even show up in the login failure report. Dang.

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 7

Do any of the server roles have DENYs on them?

SELECT SUSER_NAME(grantee_principal_id), permission_name

FROM sys.server_permissions

WHERE state_desc = 'DENY'

John

sqldriver SSChampion Points: 10440 More actions · Answer 8

sqldriver

SSChampion

Points: 10440

April 11, 2014 at 10:05 am

#1705399

No sir. That query comes back empty.

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 9

I think the login failure error you're getting points to not being able to open the specified database. What is the default database for the user(s) this is affecting? Either give them access to that database, or (which is what I do) set the default to master for all users.

John

Edit: Oops - someone has already suggested that!

Beatrix Kiddo SSC-Dedicated Points: 32407 More actions · Answer 10

Yes, that's what I'm saying; state 38 means 'Login valid but database unavailable (or login not permissioned for that database)'.

http://sqlblog.com/blogs/aaron_bertrand/archive/2011/01/14/sql-server-v-next-denali-additional-states-for-error-18456.aspx

sqldriver SSChampion Points: 10440 More actions · Answer 11

I thought the exact same thing, but every login and role has permissions to the master database, and the master database permissions are open. Wild stuff.

Beatrix Kiddo SSC-Dedicated Points: 32407 More actions · Answer 12

Beatrix Kiddo

SSC-Dedicated

Points: 32407

April 11, 2014 at 10:20 am

#1705410

Is the guest user definitely enabled for master?

sqldriver SSChampion Points: 10440 More actions · Answer 13

Beatrix Kiddo (4/11/2014)
Is the guest user definitely enabled for master?

It looks to be so. Let me know if you'd like me to query differently.

SELECT name, permission_name, state_desc

FROM sys.database_principals dp

INNER JOIN sys.server_permissions sp

ON dp.principal_id = sp.grantee_principal_id

WHERE name = 'guest'

namepermission_namestate_desc

guestVIEW ANY DATABASEGRANT

guestCONNECTGRANT

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 14

I know you said the default database is master for all logins, but please humour me - what does this return?

SELECT name

FROM sys.server_principals

WHERE default_database_name <> 'master'

John

sqldriver SSChampion Points: 10440 More actions · Answer 15

John Mitchell-245523 (4/14/2014)
I know you said the default database is master for all logins, but please humour me - what does this return?
SELECT name
FROM sys.server_principals
WHERE default_database_name <> 'master'
John

Hi John,

Can you let me know what you'd be looking for in this list? There are some client specific service account names I wouldn't feel comfortable posting.

Thanks