SQL user without a login or WITHOUT LOGIN

Question

question

Shawn_Melton asked Aug 2, '11

SQL user without a login or WITHOUT LOGIN

SQL 2000 if you found a SQL user account in a database without a login mapped to it, it was considered an orphaned account and you could run the below code to find them.

sp_change_users_login @Action='Report';

With SQL 2005 and beyond that is no the case anymore. It allows you to create a database user without specifically mapping a login to it.

CREATE USER my_user WITHOUT LOGIN

What might you want to use this for? I found this [article][1] that walks through a few examples of using it. I think it is pretty interesting, but also noticed in the comments that it changes how you look for actual orphaned users. The sp_change_users_login BOL article states it is going away in future releases (among other things :). One of the authors comments gives a good query to find actual orphaned users by checking the SID length.


-- Finding users without login
SELECT * FROM sys.database_principals
WHERE
DATALENGTH(sid) > 16 -- users mapped to logins have a 16 bytes SID,

 -- users without login have a length of ~28 bytes
AND sid not in (SELECT sid FROM sys.server_principals) -- No login with a matching SID
AND type = 'S' -- Only SQL users
AND principal_id > 4 -- filter system (well-known) principals
go

-- Finding orphaned users
SELECT * FROM sys.database_principals
WHERE
DATALENGTH(sid) <= 16 -- users mapped to logins have a 16 bytes SID,

 -- users without login have a length of ~28 bytes
AND sid not in (SELECT sid FROM sys.server_principals) --No login with a matching SID
AND type = 'S' -- Only SQL users
AND principal_id > 4 -- filter system (well-known) principals
go

Was just wondering if anyone has used this yet or is using this feature or ability in SQL 2005+? [1]: http://blogs.msdn.com/b/raulga/archive/2006/07/03/655587.aspx

sql-server security login

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 1 · 2011-08-02T12:44:13Z

K. Brian Kelley answered Aug 2, '11

It's also great for testing a security model. You can use CREATE USER and the WITHOUT LOGIN clause to set up the different types of users in the database. Make those users members of the appropriate roles. The use EXECUTE AS USER = ''; and REVERT; to switch in and out of context and test your security. I do this all the time in demo scripts when I present and explain that it makes it easy to see exactly what would happen when a particular role tries to do something in the database.

1

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Shawn_Melton commented · Aug 04, 2011 at 06:42 AM

Do you see a purpose of using them in production environments? One of the things I had thought deals with the customer I work for is (shall we say) required to follow IASE checklist regarding OS and DBMS security posture. One of those "suggestions" is for the account that owns objects within the database should be disabled and not used. In most instances this can be done, mostly because we disable the "sa" account anyway so we just make it the owner, unless the application dictates otherwise. What if we used a user WITHOUT LOGIN for that? Would there be any issue or security concern in doing that?

0 ·

Answer 2 · 2011-08-02T11:58:02Z

Oleg answered Aug 2, '11

A user created without login obviously cannot connect to the database, but can query and have permissions to the database objects, and thus, the fact that Microsoft allows such user to be created is a great feature. For example, suppose that you must have a procedure which uses some dynamic SQL which deletes records from some table. Since it is a synamic SQL, the login you use to connect to the database has to have an execute permission on the proc and delete permission on that table. What about if you cannot afford granting that delete permission? You can then create a user without login and grant that user an appropriate delete permission . Now, if you create a procedure with **EXECUTE AS [that\_user\_without\_login]** and grant the app user impersonate permission on user without login then you are all set because the connection string used to connect the application user (which has a login) can execute the procedure despite the fact that that user does not have a delete permission and the procedure uses dynamic SQL. If your developers don't know that they can use the credentials of the **application** login to create and execute a malicious script, such as **exec ('some\_evil\_script') as user =[that\_user\_without\_login]** then it would appear that creating a user without login is a useful feature. I hope that I am making some sense here :) Oleg

2

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Oleg commented · Aug 02, 2011 at 12:23 PM

No, I think that you should not provide any information related to the existence of the user without login, so all that devs know is the normal user mapped to some login with password does not have any extra permissions. If am a dev (which I am) and all I know is that the creds I use to connect my app to the database do not have any delete permissions, I would sleep well knowing that my app cannot be used to delete anything maliciously (without the specific knowledge about that user without login which can never connect to the database directly anyway).

1 ·

Shawn_Melton commented · Aug 02, 2011 at 12:07 PM

yeah I can see that as advantage, because you could provide them an account to use without having to pass around a password. would there be a way to prevent the evil nature from occuring? or would you just specifically audit those user_without_login accounts to capture misuse of them?

0 ·

Answer 3 · 2014-08-28T17:33:07Z

bpodfw answered Aug 28, '14

I did run the code you provide for the different types of users and it does work for 2005 and up but if you have databases created in SQL 2000 that were upgraded then if you have old application role SQL user IDs they do not have a SID over 16 bytes but they are not in sys.syslogins. They are in sys.sysusers and sys.database_principals and there does not appear to be any difference between them in the record structure. The only difference I could find was they did not appear in sys.syslogins. I did check and ran the code against an SSIS 2012 msdb and found the SQL user w/o login that is in there, MS_DataCollectorInternalUser, used by the system. So the code does seem to work but only for databases created with SQL 2005 and above.

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

question