Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««1234»»»

Windows authentication only allows login with sysadmin role Expand / Collapse
Author
Message
Posted Friday, April 11, 2014 8:12 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 5:09 PM
Points: 445, Visits: 1,799
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.
Post #1560947
Posted Friday, April 11, 2014 8:12 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, September 24, 2014 5:37 AM
Points: 498, Visits: 1,845
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?)
Post #1560948
Posted Friday, April 11, 2014 8:12 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, September 24, 2014 5:37 AM
Points: 498, Visits: 1,845
(Dup!)
Post #1560949
Posted Friday, April 11, 2014 8:34 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 5:09 PM
Points: 445, Visits: 1,799
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
Post #1560972
Posted Friday, April 11, 2014 8:38 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, September 24, 2014 5:37 AM
Points: 498, Visits: 1,845
Thanks. It's really hard when you can't just see the instance in question .

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?
Post #1560973
Posted Friday, April 11, 2014 8:46 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 5:09 PM
Points: 445, Visits: 1,799
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
Post #1560976
Posted Friday, April 11, 2014 8:55 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, September 24, 2014 5:37 AM
Points: 498, Visits: 1,845
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?



  Post Attachments 
Dialogue box.png (5 views, 55.63 KB)
Post #1560986
Posted Friday, April 11, 2014 9:03 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, September 24, 2014 5:37 AM
Points: 498, Visits: 1,845
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).
Post #1560994
Posted Friday, April 11, 2014 9:43 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 5:09 PM
Points: 445, Visits: 1,799
In order:

Made no difference.

Public is wide open.

Thanks
Post #1561025
Posted Friday, April 11, 2014 9:46 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, September 24, 2014 5:37 AM
Points: 498, Visits: 1,845
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!)
Post #1561029
« Prev Topic | Next Topic »

Add to briefcase ««1234»»»

Permissions Expand / Collapse