September 8, 2013 at 3:17 pm
Using SQL Server 2012, I am trying to create a user with a default schema which is the same as the username but with dbo as the owner of the schema. I am getting the following error when trying to create a table as the log in user:
"Msg 2760, Level 16, State 1, Line 2
The specified schema name "TestUser1" either does not exist or you do not have permission to use it."
Here is my attempt to set up a user schema (the user's default schema) with dbo as owner. I would like for the user to be able to create objects within his\her schema which is owned by dbo. I hope I am not making this more difficult than it should be.
--NOTE: In Step 4, I had to change the schema owner to dbo in order to assign permissions. With TestUser1 as the schema owner, it returns the following error: "Cannot grant, deny, or revoke permissions to sa, dbo, entity owner, information_schema, sys, or yourself."
--1. Create Login.
USE [master]
GO
CREATE LOGIN TestLogin1
WITH PASSWORD='?????????',
DEFAULT_DATABASE=ST_Demo;
GO
--2. Create a database user, map it to a login and add a default schema.
This step does not create a schema.
--NOTE: At this point, the user is linked to his/her schema but the schema does not exists.
USE ST_Demo;
GO
CREATE USER TestUser1 FOR LOGIN TestLogin1
WITH DEFAULT_SCHEMA=TestUser1;
--3. Create a Schema.
--The AUTHORIZATION section on the syntax identifies the owner.
--This section is optional, and if you do not include it, the owner
--will be the dbo user.
USE ST_Demo;
GO
CREATE SCHEMA TestUser1
AUTHORIZATION TestUser1;
GO
--4. Apply Permissions on Schemas.
--NOTE: Had to changed the schema owner to dbo in order to assign permissions.
--With TestUser1 as the schema owner, it returns the following error:
--Cannot grant, deny, or revoke permissions to sa, dbo, entity owner, information_schema, sys, or yourself.
USE ST_Demo;
GO
ALTER AUTHORIZATION ON SCHEMA::[TestUser1] TO [dbo]
GO
GRANT ALTER ON SCHEMA::[TestUser1] TO [TestUser1]
GO
GRANT CONTROL ON SCHEMA::[TestUser1] TO [TestUser1]
GO
GRANT SELECT ON SCHEMA::[TestUser1] TO [TestUser1]
GO
GRANT DELETE ON SCHEMA::[TestUser1] TO [TestUser1]
GO
GRANT INSERT ON SCHEMA::[TestUser1] TO [TestUser1]
GO
GRANT UPDATE ON SCHEMA::[TestUser1] TO [TestUser1]
GO
GRANT CREATE TABLE TO TestUser1;
GO
5. Test TestLogin1.
Log in as TestLogin1 and execute the Create Table Statement.
--Create Table.
USE ST_Demo;
GO
CREATE TABLE Customer
(
CustomerID int NOT NULL IDENTITY(1,1),
CustomerName varchar(25) NOT NULL,
StateCode char(2) NOT NULL,
CreditLimit money NULL
);
GO
This is the error it returns with 'dbo' as the owner of the schema.
--Msg 2760, Level 16, State 1, Line 2
--The specified schema name "TestUser1" either does not exist or you do not have permission to use it.
--The above Create Table Statement works, if I set "TestUser1" as the owner of the schema instead of 'dbo':
USE ST_Demo;
GO
ALTER AUTHORIZATION ON SCHEMA::[TestUser1] TO [TestUser1]
GO
I have been told that when I created the users, I gave them a default schema with the same name as the user name and this caused the error. Is this true?
I still thought if I created a schema (no matter the name whether it is username or HumanResources or Development) that this would work but evidently not. I guess schemas should not be created with user names even though SQL Server allows it. Schemas should be created by department or function. When you create the users, give them a default schema, not one that has the same name as the users.
Separation of users and schemas is the best practice, right? So, I guess when I create a user I would like to eliminate users from owning objects, including schemas. I should create a user, create a schema with 'dbo' as the owner, assigned the schema as the default schema for the user. When the user creates an object, the object should be created in the user's default schema. However, 'dbo' should still be the owner of the schema which is what I am trying to accomplish.
From my reading and understanding:
•Database schema names are distinct from user names.
•Multiple users may share a schema, which means it can hold objects owned by multiple users.
•Permissions to act inside the schema may be granted to individual users.
•Database users can be dropped without changing objects in schemas, which is a big difference between Oracle and SQL Server.
Considering the first bullet above (•Database schema names are distinct from user names), I wonder why SQL Server allows you to still create a schema that has the same name as a database user?
When I get to work on Monday, I will try the following and I guess it should work because I have a schema name that is separate from the user name.
Create TestLogin1 and TestLogin2.
Create User TestUser1 for Login TestLogin1 with Default_Schema as Development.
Create User TestUser2 for Login TestLogin2 with Default_Schema as Development.
Create Development Schema (this schema could be called HumanResources or Development). Since I can not call the schema the same name as the User or it would cause problems.
Make the Development Schema the default schema for the TestUser1 and TestUser2.
When the users TestUser1 and TestUser2 create objects, the objects would be created in their default schema (Development Schema).
Just wonderiing if I am headed in the right direction and should the goal be to have 'dbo' own the schemas but allow database users to create objects within the schemas.
Thanks, Kevin
September 8, 2013 at 4:19 pm
Note sure what you actually did; the CREATE TABLE statement should pass. It would be easier to make dbo owner of the schema from the start, but it does not matter. Maybe you have a DENY in the mix somewhere.
Anyway, this script runs on my machine without errors:
USE [master]
GO
CREATE DATABASE ST_Demo
go
CREATE LOGIN TestLogin1
WITH PASSWORD='?????????',
DEFAULT_DATABASE=ST_Demo;
GO
USE ST_Demo
go
CREATE USER TestUser1 FOR LOGIN TestLogin1
WITH DEFAULT_SCHEMA=TestUser1;
GO
CREATE SCHEMA TestUser1
AUTHORIZATION TestUser1;
GO
GO
ALTER AUTHORIZATION ON SCHEMA::[TestUser1] TO [dbo]
GO
GRANT ALTER ON SCHEMA::[TestUser1] TO [TestUser1]
GO
GRANT CONTROL ON SCHEMA::[TestUser1] TO [TestUser1]
GO
GRANT SELECT ON SCHEMA::[TestUser1] TO [TestUser1]
GO
GRANT DELETE ON SCHEMA::[TestUser1] TO [TestUser1]
GO
GRANT INSERT ON SCHEMA::[TestUser1] TO [TestUser1]
GO
GRANT UPDATE ON SCHEMA::[TestUser1] TO [TestUser1]
GO
GRANT CREATE TABLE TO TestUser1;
GO
EXECUTE AS LOGIN = 'TestLogin1'
go
CREATE TABLE Customer
(
CustomerID int NOT NULL IDENTITY(1,1),
CustomerName varchar(25) NOT NULL,
StateCode char(2) NOT NULL,
CreditLimit money NULL
);
GO
REVERT
Go
USE tempdb
go
DROP DATABASE ST_Demo
DROP LOGIN TestLogin1
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
September 9, 2013 at 3:52 am
September 9, 2013 at 12:35 pm
Thanks Erland and Dennis. It works for me ... must have been confusing myself. However, I had to change the schema owner to dbo in order to assign permissions ... not sure why I had to do this. Do you know? With TestUser1 as the schema owner, it returns the following error:
"Cannot grant, deny, or revoke permissions to sa, dbo, entity owner, information_schema, sys, or yourself."
So, I decided to assign dbo as the schema owner when I create the schema. See code below.
I thought because I made the username and schema name the same this caused a problem. I guess it is just not a 'good practice' with SQL Server 2005 and later to make them the same. I think with SQL Server 2005 and later, DBAs are encouraged to use departmental or functional names as schemas names instead of user names. In other words use:
server.db.schema.object instead of server.db.user.object
To avoid having to change object ownership when a user leaves the company.
I guess next thing would be to create a role, apply the permissions to schema via the role and assign users to the role. To make it easier in assigning permissions. Thanks for your help.
--Create Test DB.
USE master
GO
CREATE DATABASE ST_Kevin
GO
--Create Server Login with default DB.
CREATE LOGIN TestLogin1
WITH PASSWORD='???????',
DEFAULT_DATABASE=ST_Kevin;
GO
USE ST_Kevin
GO
--Create DB User for Server Login with default Schema.
CREATE USER TestUser1 FOR LOGIN TestLogin1
WITH DEFAULT_SCHEMA=TestUser1;
GO
--Create DB Schema and assign the schema owner.
CREATE SCHEMA TestUser1 AUTHORIZATION dbo;
GO
--Assign permissions on schema.
GRANT ALTER ON SCHEMA::[TestUser1] TO [TestUser1]
GO
GRANT CONTROL ON SCHEMA::[TestUser1] TO [TestUser1]
GO
GRANT SELECT ON SCHEMA::[TestUser1] TO [TestUser1]
GO
GRANT DELETE ON SCHEMA::[TestUser1] TO [TestUser1]
GO
GRANT INSERT ON SCHEMA::[TestUser1] TO [TestUser1]
GO
GRANT UPDATE ON SCHEMA::[TestUser1] TO [TestUser1]
GO
GRANT CREATE TABLE TO TestUser1;
GO
EXECUTE AS LOGIN = 'TestLogin1'
GO
CREATE TABLE Customer
(
CustomerID int NOT NULL IDENTITY(1,1),
CustomerName varchar(25) NOT NULL,
StateCode char(2) NOT NULL,
CreditLimit money NULL
);
GO
REVERT
GO
--Drop Test DB and Server Login.
USE master
GO
--DROP DATABASE ST_Kevin
DROP LOGIN TestLogin1
September 9, 2013 at 3:49 pm
kevinsql7 (9/9/2013)
to assign permissions ... not sure why I had to do this. Do you know? With TestUser1 as the schema owner, it returns the following error:"Cannot grant, deny, or revoke permissions to sa, dbo, entity owner, information_schema, sys, or yourself."
Yes, owner. If TestUser1 owns the schema you cannot grant TestUser1 any rights in it. Nor would there be any need to.
I thought because I made the username and schema name the same this caused a problem. I guess it is just not a 'good practice' with SQL Server 2005 and later to make them the same.
It all depends on what you want to achieve. It is the old pattern from SQL 2000 that I did not see much point with, but not all shops are alike.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
September 10, 2013 at 1:15 pm
Thanks Erland.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply