SQL Server contained database feature

SQL Server 2012 has a "contained database" feature.

In the normal case the logins of the (SQL Server) server are coupled to the users in each of its databases. The logins are stored in its MASTER database. If a database gets restored to another server, then the mapping is lost and you end up with "orphaned users". Those accounts can no longer access the database.

For a contained database on a SQL Server:

• The server logins are not used/required. Users can be created and used without a matching login on the server. These users are called "portable users"
• The database handles the autorization instead of the server. This is called "contained database authentication". Activate this delegation of functionality on the server:
  

  EXEC sp_configure 'contained database authentication',1
  GO
  RECONFIGURE

  In SSMS, a new option appears in the ->Security->Users->New User... screen: "SQL User with password"
• Works for SQL and Active Directory Accounts, e.g. statement for both without logins:
  

  CREATE USER [testdomain\testuser]
  CREATE USER [biuser]

Notes:

• Partial containment: in 2012 only the "partial containment" functionality works. In the future a "full containment" option if foreseen
• Connect from SSMS: you need to specify the database, otherwise the connection attempt fails, because the database is now doing the authentication
• Security: once a portable user gains access to a contained database through contained database authentication, that user also ends up gaining guest access to all other databases on the host system
• Conversion: conventional existing users can be converted into portable users through the use of a special stored procedure: sp_migrate_user_to_contained. It provides an argument that you can use to specify whether or not to disable the server-level login -- something you'll typically want to do as a best practice to avoid ugly login problems that can occur when duplicate logins and users overlap each other
• Downsides: no cross-database joins, linked server use, database mail, possible tempdb colation issues. Use "select * from  sys.dm_db_uncontained_entities" to check containment readiness

PowerPivot cannot load from procedure with info messages

Data can be loaded into PowerPivot from a SQL Server stored procedure, but the procedure cannot contain:

• PRINT statements, like print 'lets start'
• informational RAISERROR events, like raiserror ('lets start', 0, 1)

Both return messages to the client. Tools like Management Studio can handle these messages and display them in a separate window. PowerPivot handles them like the returned data and fails reporting an error. The error is not very helpful:

OLE DB or ODBC error: lets start; 01000.
An error occurred while processing table 'Query'.
The current operation was cancelled because another operation in the transaction failed.

The only solution is to remove these instructions from the procedure and any procedures it might call. There is no way to suppress this on the OLEDB connection during the call to the procedure.

It is also advisable to have the following two instructions at the start of the procedure:

• set fmtonly off
• set nocount on

LISTAGG / implode solution for SQL Server

Group multiple records into one and concatenate the values of the string/text values of a field:

ALTER view [dbo].[vcomments] as
with x as
(
select
cmt_domain,
cmt_indx,
cmt_seq,
concat(cmt_cmmt1,cmt_cmmt2,cmt_cmmt3,cmt_cmmt4,cmt_cmmt5,cmt_cmmt6,cmt_cmmt7,cmt_cmmt8,cmt_cmmt9,cmt_cmmt10,cmt_cmmt11,cmt_cmmt12,cmt_cmmt13,cmt_cmmt14,cmt_cmmt15) as cmt_cmmt,
cmt_lang
from
v_cmt_det_act
)
select
cmt_domain,
cmt_indx,
--FOR XML PATH returns a one record XML datatype in which all records are "unioned".
-- However, special characters are encoded as excape sequences, i.e. "<" becomes "<"
-- To reverse the encoding, get the entire root document/element as a value using the VALUE function.
-- The [1] is required because VALUE expects a singleton, i.e. exactly ONE element
rtrim(substring(
(
select char(10) + '[PAGE ' + cast(x2.cmt_seq as nvarchar(2)) + ']' + char(10) + x2.cmt_cmmt -- a field with no name gets no XML element to enclosed it. In short, the XML tag is removed
from x x2
where x1.cmt_domain = x2.cmt_domain and x1.cmt_indx = x2.cmt_indx
order by x2.cmt_seq
for XML PATH (''), type -- the brackets indicate the XML root element. Specifying an empty string as root element name removes it
).value('(/)[1]','nvarchar(max)')
,2,100000)) as cmt_cmmt
from
(
select distinct cmt_domain, cmt_indx
from x
) x1

Query Active Directory from SQL Server

Get active directory server and LDAP servers in a domain
nltest /dclist:sub.dom.com

results amongst others in:
\\dcserver1.sub.dom.com -> dcserver1 is the AD server

Analyse active directory structure
Use Sysinternals ADExplorer to analyse the structure of active directory. Connect to server: dcserver1

Users in a group from command prompt
net group adtestgroup /domain

Active Directory structure in FROM clause
E.g. FROM "LDAP://sub.dom.com/OU=Groups,OU=Global,DC=sub,DC=dom,DC=com"

LDAP = case-sensitive protocol name, always this value
Before the slash: sub.dom.com, is the domain for which to find the AD structure
After the slash: the part of the tree to search. Specified from right to left. So in the example, from parent to child:
com -> dom -> sub -> Global -> Groups -> ADTestGroup, searches the nodes beneath the lowest level, which is ADTestGroup

The names ("dom", "Groups", etc), type ("OU", "CN", "DC") and depth of the levels are dynamic. So check with ADExplorer how AD is configured in the specific instance.

Users in a group from SQL Server
First, add a linked server to ADSI (fill with correct password for DOMAIN\ACCOUNT):

exec sp_addlinkedserver @server = 'ADSI', @srvproduct = 'Active Directory Services 2.5', @provider = 'ADSDSOObject', @datasrc = 'adsdatasource'
exec sp_addlinkedsrvlogin @rmtsrvname = 'ADSI', @useself = 'False', @locallogin = null, @rmtuser = 'DOMAIN\ACCOUNT', @rmtpassword = '********'

Select users in group "adtestgroup":

select * from openquery(ADSI,'
    SELECT objectCategory, cn, sn, mail, name, department, company
    FROM ''LDAP://sub.dom.com/DC=sub,DC=dom,DC=com''
    WHERE MemberOf=''CN=adtestgroup,OU=Groups,OU=Global,DC=sub,DC=dom,DC=com''
    ORDER BY cn
')

Unfortunately, dynamic sql is not possible inside a udf, so a procedure is needed:

alter procedure cst_usersingroup(@grp nvarchar(100))
as
begin
    declare @sql as nvarchar(max) = '
    select samaccountname as accountname, name, mail, department, title, company
    from openquery(ADSI,''
        SELECT objectCategory, cn, sn, mail, name, department, company, title, samaccountname
        FROM ''''LDAP://sub.dom.com/DC=sub,DC=dom,DC=com''''
        WHERE MemberOf=''''CN=' + @grp + ',OU=Groups,OU=Global,DC=sub,DC=dom,DC=com'''''')
    x
    order by samaccountname'

    exec(@sql)
end

Select all BI_* and EDW_* groups from active directory:

select lower(cn) as grp, whencreated, whenchanged, distinguishedname
from openquery(ADSI,'
    SELECT cn, distinguishedname, whencreated, whenchanged
    FROM ''LDAP://sub.dom.com/OU=Groups,OU=Global,DC=sub,DC=dom,DC=com''
    where CN=''bi_*'' or  CN=''edw_*''
') x
order by cn

Notes:

• In most cases, all domain users can query AD using the basic search method specified before. More advanced AD search methods might be disabled and require special rights