December 11, 2014 at 6:08 am
Good morning everyone,
I have a DB that I noticed has a user listed with access to that specific DB. There is no visible "Login" listed for this user and I thought perhaps it was an orphaned user, but it was confirmed that this user DOES have access to the specified DB. I normally grant access through a Login user mapped with specific permissions. Can anyone tell me how this might have been done? Thanks is advance for your help.
December 11, 2014 at 6:16 am
Did you upgrade from SQL 2005 or SQL 2000? It might be a holdover from one of those.
Also, it is entirely possible the login was orphaned (the server level login was deleted without removing the database level permissions). Sometimes doing that doesn't screw up database level permissions. Sometimes it does. I have yet to be able to figure out why this happens, because I haven't seen it happen often enough for me to determine a pattern from it.
December 11, 2014 at 6:19 am
Hey Brandie,
This is a relatively new user that didn't go through any SQL updates/upgrades. The user is not an orphaned user. The person who put this user in there doesn't recall how she did it. I do feel like I was present while it happened. It was created this way so our provider could access the DB without having a login to the SQL engine, and only the DB. I thought at first it might have been just a Status setting but I 'm unable to yield the same results.
December 11, 2014 at 6:43 am
Was it perhaps granted only on the database level security then?
I.E., try opening up the Database, going to Security, and adding a new user via the GUI.
If that doesn't work, try creating the user with T-SQL just on the database level. Or script out the user and see if the user inadvertantly got mapped to a server level user that is NOT the same name. (I've done that before).
December 11, 2014 at 6:55 am
I think I found out what happened.
It looks like the user was scripted without a default schema as:
USE [database]
GO
CREATE USER [dbuser] WITHOUT LOGIN WITH DEFAULT_SCHEMA=[dbo]
GO
This is off the base topic, but what exactly would this accomplish, and as far as I can see, this can only be accomplished via CREATE TO or t script, is that right?
Thanks for your replies.
December 11, 2014 at 7:02 am
I think you are correct. I would advise creating a server level public login to go with that DB login, though, for ease of maintenance and to prevent it from accidentally registering as orphaned later on.
And, of course, you could test it in a Dev environment to see if that will do the trick.
December 11, 2014 at 7:07 am
Another question Brandie, if you don't mind...
The script above, is that no different than providing access through creating a Login user with mapping to a specific database, then selecting the Login Disabled on the Status option on the user?
December 11, 2014 at 7:17 am
I've never actually disabled any login except the sa and guest (old days) logins. And so far as I know, that's a server-level setting only. I'm pretty sure that once a login is disabled, it cannot be used for anything on SQL, not even database level security.
But it's been a while since I played with this. My feelings are that every login should be mapped to a server-level login. For database only access, the server-level login should only have "public" access with user mapping to each individual database and then permissions applied on the database level. I always do this through either T-SQL or the server-level security GUI page.
But again, to know for sure, you should play with this on a dev database (not production) and test it out for yourself.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply