add-sqluser => uses DEFAULT_SCHEMA for SQLUSER linked to SQLLOGIN on WINDOWS GROUP.

Mar 5, 2013 at 9:28 AM
Edited Mar 5, 2013 at 9:52 AM
Hi, If i create a LOGIN based on a windows group it seems impossible to add it as 'USER' to a database because of the default_schema that is always used by the add-sqluser command. A Default schema can't be set for DBUSERS based on INSTANCE users based on windows groups. i'm downloading the source code now to see if i can adapt the cmdlet to my needs.

EDIT=> After removing the default_schema reference from the code completely, it works. I created a new function add-gsqluser


Add-SqlUser : Exception calling "Create" with "0" argument(s): "Create failed for User 'G.r-Server123_MgmtDb_db_ddladmin'. "
Microsoft.SqlServer.Management.Smo.FailedOperationException: Create failed for User 'G.R-srvbedi172_DemeMgmtDb_db_ddladmin'. ---> Microsoft.SqlServer.Manageme
nt.Common.ExecutionFailureException: An exception occurred while executing a Transact-SQL statement or batch. ---> System.Data.SqlClient.SqlException: The DEFA
ULT_SCHEMA clause cannot be used with a Windows group or with principals mapped to certificates or asymmetric keys.
at Microsoft.SqlServer.Management.Common.ConnectionManager.ExecuteTSql(ExecuteTSqlAction action, Object execObject, DataSet fillDataSet, Boolean catchExcept
ion)
Coordinator
Mar 5, 2013 at 12:03 PM
This issue is an underlying SQL Server feautre. As you've noticed you cannot assign a default schema to a Windows group. See this connect item for more details: http://connect.microsoft.com/SQLServer/feedback/details/328585/default-schema-for-windows-group
Mar 5, 2013 at 12:54 PM
Yes i know. I'm not pointing in that direction, i'm pointing to the fact that if a function is created to create db users, it should verify if the login specified is a group or not and then create it with or without default schema (default_schema = DBO for users / default_Schema is not parsed for user based on groups ) ? Now the function just checks if a default_schema is given or not. If not it passes DBO. It should not pass anything in case the instance login is an AD group. to my feeling, this is a bug.

For my issue, it's solved. Perhaps in a future release this can be caught?

anyway, great set of functions !