Raul Garcia's blog - All Comments

re: Quick guide to DB users without logins in SQL Server 2005

Raul Garcia - MS — Mon, 27 Feb 2012 23:30:10 GMT

Quick disclaimer in case you haven’t seen it yet. I am no longer part of the SQL Server group, but I will keep posting and responding to questions/comments on SQL Server topics on this blog.

Users without login are similar to approles, with the main difference that application roles are set and controlled via a password (i.e. middle tier needs to know the password), which brings by itself all sort of password management issues, while users without login are permission based (IMPERSONATE permission on the user).

This is a significant difference when dealing with code. When people use approles, I have often seen hardcoded passwords as a simplification for the developers dealing with them, but this typically causes a lot of problems for DBAs/ops who need to conform to password policies. The user without login approach on the other hand can simplify the password administration.

Even more, considering test & prod environments, the deployment would be pretty much the same, without risk of the devs having access to the prod environment (unless DBA explicitly grants them access to the database and permission to impersonate the application-specific user). Because impersonation is permission based, the impersonation action itself can be audited.

Another great advantage of user without logins, is that you can use them with EXECUTE AS within a module (i.e. SP) and grant permissions via the SP directly instead of granting permissions on the underlying tables to the SP caller. Once the SP execution finishes, the execution context would revert back to the caller automatically.

Because impersonation is permission based, you can also take advantage of AD security groups, and grant IMPERSONATE permission on the application user (or EXECUTE permission on the SP if you prefer the EXECUTE AS approach) and take advantage of managing security via Windows security groups.

Both the users with no-logins and the application roles are very similar in terms of access to server-scoped resources. As I recall, the main difference is that approles have some limited access to some catalog views (metadata) for backwards compatibility purposes. By default access to other databases is also similar (only via “guest”).

The main difference regarding permission management for users without login is that there are supported mechanisms to have a controlled escalation of privileges to full server-scope of across databases (i.e. trustworthy bit on the DB or usage of digital signatures).

I hope this helps,

-Raul

re: Quick guide to DB users without logins in SQL Server 2005

Mark Burns — Tue, 14 Feb 2012 21:57:16 GMT

Raul,

I find this topic very interesting, and I would like to get some advice for using these NLI-Users (No LogIn Users) as that Application Role replacement from the POV of an MS Access FE + SQL Server BE Application Developer.

In general it'd be best for an application (regardless of AD user logins and groups) to have either little or no actual table/view access from the linked tables (enough access permissions to create the linked tableDef in the Access DBs, but not to pull data from the server) from the old Database Window (or the newer navigation menus). Ideally, then we could launch access permissions based upon these users w/o logins on a form-by-form/report-by-report/code-module-by-code-module basis, greatly enhancing the security of the application and data.

Could you offer any insights as to how to best approach this idea - taking into account the differing needs of the App developer (in a DEV or TEST server environment of course) an actual SQL Server DBA's roles and access requirements, and the (TEST + PROD servers) needs of varying groups of users (think AD Groups) for multiple permutations of read and write access to the different data tables, depending upon database functions employed and their AD group memberships.

re: Disaster Recovery: What to do when the SA account password is lost in SQL Server 2005

Raul Garcia - MS — Mon, 13 Feb 2012 18:16:57 GMT

This is a disaster recovery mode, and a single admin connection is allowed. Most tools with a rich GUI typically open multiple connections, and they are not suitable for this task. Please try using sqlcmd, which will only open a single connection.

If sqlcmd is also giving the error message, most likely there is another tool/service establishing a connection before you have a chance to connect. Stop all services that may connect to SQL Server during the password disaster recovery operation.

re: Disaster Recovery: What to do when the SA account password is lost in SQL Server 2005

Jason — Wed, 11 Jan 2012 07:29:13 GMT

Maybe I'm missing something here... after starting SQL server in single user mode. I try to access the SQLSMD.. it couldn't giving an error "Server is in single user mode. Only one administrator can connect at this time."

I couldn't access via the management studio either with the same issue.

Please advise.

re: Disaster Recovery: What to do when the SA account password is lost in SQL Server 2005

Cesar — Tue, 06 Dec 2011 16:57:04 GMT

I was able to just add the ";-m" to the start up parameters, log in to the SQL express instance in via management studio, and give my user the admin rights necessary.

this worked thanks so much!

re: Quick guide to DB users without logins in SQL Server 2005

Raul Garcia - MS — Tue, 06 Dec 2011 00:49:49 GMT

Can you please provide more information about the error? I cannot repro on my own, but I may be able to recognize the root cause from the error description. There shouldn’t be any different behavior whether the statement you mentioned is executed remotely or on the local server.

If the error persists, I would also recommend opening a bug @ Microsoft connect (connect.microsoft.com/sql).

re: SQL Server 2005 –Encrypting data on existing applications

Raul Garcia - MS — Tue, 06 Dec 2011 00:44:05 GMT

The method I described here pretty much was designed as a workaround for scenarios that needed to be encrypted at rest, but the application was not prepared for it. 200K records is not that much, so I am a bit surprised; try using DecryptByAutoCert directly on the base table (i.e. SELECT *, DecryptbyAutoCert( encrypted_column) … ) and see if there is any difference.

BTW. This article was written before the introduction of SQL Server 2008 Transparent Data Encryption (msdn.microsoft.com/.../bb934049.aspx). If you have the possibility, I would strongly recommend using TDE instead. The performance penalty will be significantly reduced.

re: Disaster Recovery: What to do when the SA account password is lost in SQL Server 2005

Tarun__Arora — Fri, 25 Nov 2011 14:25:15 GMT

A big Thanks, this works for SQL Server 2008 R2 as well.

Cheers

re: Quick guide to DB users without logins in SQL Server 2005

Matt1855 — Thu, 24 Nov 2011 10:03:53 GMT

I encountered a issue when I comment the command 'CREATE USER [mytestDB] WITHOUT LOGIN WITH DEFAULT_SCHEMA=[dbo]', if I deployed the db to the remote site, the DB connection has error, but if I used it in local site, it's no DB connection error.

Could you pls give me some idea?

Thank you very much.

Matt.

re: Quick guide to DB users without logins in SQL Server 2005

matt — Thu, 24 Nov 2011 09:51:23 GMT

One question, why I comments 'CREATE USER [mytestDB] WITHOUT LOGIN WITH DEFAULT_SCHEMA=[dbo]', when I deploym db to the remote site, the db connection will generate error? If I used it in local site, it has no error?

Could you pls give me some comments? Thanks a lot.