default schema for user

when a user creates an obejct, it creates it in a schema of the user's name.
LVL 5
25112Asked:
Who is Participating?
 
dave4dlConnect With a Mentor Commented:
you can alter a user to have a different default schema with something like the code below
ALTER USER [DECLA\DevGroup] WITH DEFAULT_SCHEMA=[some_new_schema]

Open in new window

0
 
25112Author Commented:

for example
DECLA\DevGroup is the Windows Group that has access to a server/db.

when users within this group create an objects.. it creates with
DECLA\DevGroup schema.. how can we try to give them the dbo schema instead of the above when creating?
0
 
25112Author Commented:
When forced to take the dbo, it complains

The specified schema name "dbo" either does not exist or you do not have permission to use it.

0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

 
25112Author Commented:
OK.. but i get the error

Msg 15259, Level 16, State 2, Line 2
The DEFAULT_SCHEMA clause cannot be used with a Windows group or with principals mapped to certificates or asymmetric keys.

-------------------------
even if it's not default, how can i give permissions so  that they can create with dbo schema?
0
 
25112Author Commented:
should I do the below? but we want them to have only
create proc and create function privilege.. no to any tables on dbo..
use [SATSA]
GO
GRANT ALTER ON SCHEMA::[dbo] TO [DECLA\DevGroup]
GO
use [SATSA]
GO
GRANT CONTROL ON SCHEMA::[dbo] TO [DECLA\DevGroup]
GO
use [SATSA]
GO
GRANT DELETE ON SCHEMA::[dbo] TO [DECLA\DevGroup]
GO
use [SATSA]
GO
GRANT EXECUTE ON SCHEMA::[dbo] TO [DECLA\DevGroup]
GO
use [SATSA]
GO
GRANT INSERT ON SCHEMA::[dbo] TO [DECLA\DevGroup]
GO
use [SATSA]
GO
GRANT REFERENCES ON SCHEMA::[dbo] TO [DECLA\DevGroup]
GO
use [SATSA]
GO
GRANT SELECT ON SCHEMA::[dbo] TO [DECLA\DevGroup]
GO
use [SATSA]
GO
GRANT TAKE OWNERSHIP ON SCHEMA::[dbo] TO [DECLA\DevGroup]
GO
use [SATSA]
GO
GRANT UPDATE ON SCHEMA::[dbo] TO [DECLA\DevGroup]
GO
use [SATSA]
GO
GRANT VIEW DEFINITION ON SCHEMA::[dbo] TO [DECLA\DevGroup]
GO

Open in new window

0
 
25112Author Commented:

wish this was a valid syntax.. then that is exactly what i would have needed...
GRANT CREATE PROCEDURE ON SCHEMA ::dbo TO [DECLA\DevGroup]
GRANT CREATE FUNCTION ON SCHEMA ::dbo TO [DECLA\DevGroup]
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.