February 26, 2009 at 12:45 am
Hi,
I have created one windows AD group. Added set of members for that group. Grant DBO Priviledges for that AD group in one of my database in SQL 2005.
But i can't grant default schema for that windows AD group.
When i create any objects on that database through this AD group users received the below error
"Property Default schema is not available for Database (xxx). This property may not exist for this object or may not be retrievable due to insufficient access rights"
Regards,
S.Balavenkatesh.
February 26, 2009 at 8:17 am
Is this a database that was upgraded from SQL Server 7, 2000? Check the compatibility setting of the database.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
February 27, 2009 at 8:44 am
Hi,
No. It is new database created in sql 2005.
Regards,
S.Balavenkatesh
February 28, 2009 at 9:43 pm
From Books Online:
DEFAULT_SCHEMA cannot be specified for a user who is mapped to a Windows group, a certificate, or an asymmetric key.
You cannot set a default schema for a Windows group. Users which map to SQL Server logins or Windows user accounts may have default schema set for them. However, the issue with having a Windows group with a default schema is you could have a Windows user who is a member of multiple Windows groups. If more than one of those Windows groups have access to the database and they have different default schema, there would be no way for SQL Server to determine which one the user should use. Therefore, default schema can't be set for users which map to Windows groups.
K. Brian Kelley
@kbriankelley
September 3, 2009 at 9:14 am
Please take a look at the possible workaronds:
1) Create a SQL login with dbo rights to the database where tables and other objects have to be created. Have the users connect to SSMS using the SQL login that you have created. Tables can be created using SSMS without issues.
2) Have the user of windows security group create table using TSQL. You will see that a new schema and user will be created for this database with the user name of the user. Table gets created with windows user name as the owner .
Now, go to the database user which got created. Change the default schema to dbo.
User of that security group can create tables in SSMS and with dbo as the object owner.
Apparently, this is a microsoft bug and has not been resolved yet.
https://connect.microsoft.com/feedback/viewfeedback.aspx?FeedbackID=238246&wa=wsignin1.0&siteid=68
If you need further information, please feel free to contact prakash7900@yahoo.com.
Thanks
Prakash
Prakash B
September 4, 2009 at 10:06 am
Prakash.Bhojegowda (9/3/2009)
1) Create a SQL login with dbo rights to the database where tables and other objects have to be created. Have the users connect to SSMS using the SQL login that you have created. Tables can be created using SSMS without issues.
2) Have the user of windows security group create table using TSQL. You will see that a new schema and user will be created for this database with the user name of the user. Table gets created with windows user name as the owner .
Now, go to the database user which got created. Change the default schema to dbo.
User of that security group can create tables in SSMS and with dbo as the object owner.
Apparently, this is a microsoft bug and has not been resolved yet.
For #1, don't do this. It would be better to explicitly use the Windows user account (not group) rather than a SQL Server login. Allow AD to be the single source for authentication. If you create a SQL Server based login, you break this.
For #2, it's not a bug, though it's filed as one. It's the designed behavior. What's being tracked is a request to change the designed behavior.
K. Brian Kelley
@kbriankelley
January 6, 2010 at 1:10 pm
Well, when i granted my developers group db_owner to the new database, they were not able to create table from SSMS with schema dbo.
However whey they created from query analyzer, they were able to create a table.
What is interesting is, they were able to modify that same table create via script from SSMS.
Not sure what did i miss?!!!
January 18, 2010 at 9:23 am
I wonder if that is my issue. I have an AD group of computer accounts. A piece of software being installed does everything in the context of a user except a few INSERTs and UPDATEs being done in the context of the machine account. The AD group login has the dbowner role on the DB. However these statements are failing. Is this because it is not trying to access the table in the dbo schema since there is no default on the group? If the query was to explicitly specify the schema, [dbo].<tablename>, would the queries succeed?
January 18, 2010 at 9:38 am
Well I just checked auditing and it says the queries happened on the dbo schema. Seems like only the public role works. Can you not assign other roles to a group or is it still somehow a default schema issue?
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply