SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Windows authentication only allows login with sysadmin role


Windows authentication only allows login with sysadmin role

Author
Message
sqldriver
sqldriver
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2156 Visits: 2536
Beatrix Kiddo (4/11/2014)
No, a login, not a role. I'm trying to think how to phrase this;



I know what you're getting at, and yes, the they're added as/mapped to Windows AD credentials.
Beatrix Kiddo
Beatrix Kiddo
SSCertifiable
SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)

Group: General Forum Members
Points: 6831 Visits: 4830
Posted too soon!

They log into SSMS as DomainName\Username yes? If you check under Security > Logins > DomainName\Username right-click, Properties, what have they got under Server Roles? And what have they got under User Mapping (i.e. are there any databases they're mapped to?)
Beatrix Kiddo
Beatrix Kiddo
SSCertifiable
SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)

Group: General Forum Members
Points: 6831 Visits: 4830
(Dup!)
sqldriver
sqldriver
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2156 Visits: 2536
Beatrix Kiddo (4/11/2014)
Posted too soon!

They log into SSMS as DomainName\Username yes? If you check under Security > Logins > DomainName\Username right-click, Properties, what have they got under Server Roles? And what have they got under User Mapping (i.e. are there any databases they're mapped to?)


Under server roles: public and sysadmin

Under user mapping: public is checked down the bottom, and a smattering of DBs are checked up top: all but one of them has user as dbo and default schema as dbo. The auslander is domain\user and default schema dbo.

Thanks
Beatrix Kiddo
Beatrix Kiddo
SSCertifiable
SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)

Group: General Forum Members
Points: 6831 Visits: 4830
Thanks. It's really hard when you can't just see the instance in question :-D.

So are you saying that if you untick Sysadmin under Server Roles and save it, this prevents that person logging in at all? I wonder if they are missing a default database? In General, what is their default database?
sqldriver
sqldriver
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2156 Visits: 2536
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
Beatrix Kiddo
SSCertifiable
SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)

Group: General Forum Members
Points: 6831 Visits: 4830
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?
Attachments
Dialogue box.png (9 views, 55.00 KB)
Beatrix Kiddo
Beatrix Kiddo
SSCertifiable
SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)

Group: General Forum Members
Points: 6831 Visits: 4830
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
sqldriver
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2156 Visits: 2536
In order:

Made no difference.

Public is wide open.

Thanks
Beatrix Kiddo
Beatrix Kiddo
SSCertifiable
SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)

Group: General Forum Members
Points: 6831 Visits: 4830
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!)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search