May 20, 2015 at 8:29 am
Hello,
I created a new login and then created a new user [COM] in DB with default schema pointing to [COM]
I created then schema [COM] WITH AUTHORIZATION [COM]
I want this [COM] user to have all permissions it needs on [COM] schema only. How do I do that? When I try to create table [Com].Table it gives me permission denied.
What am I missing?
May 20, 2015 at 8:42 am
You have to grant appropriate premissions for User to that schema
GRANT <premission> ON SCHEMA::com TO com
May 20, 2015 at 8:46 am
it does not work since [COM] already owner of the schema and hence have all permissions needed
Cannot grant, deny, or revoke permissions to sa, dbo, entity owner, information_schema, sys, or yourself.
May 20, 2015 at 10:33 am
create another user, grant permissions, link user to login?
May 20, 2015 at 10:37 am
Why do I need to create another user if I already have a user which has full permissions and can not create TABLES etc?
May 20, 2015 at 11:17 am
Your user has permission on the schema, but they don't have rights to create a table. They need CREATE TABLE permissions.
Here's a bit of test code:
CREATE LOGIN Com WITH PASSWORD = 'MyTest';
GO
USE Sandbox
GO
CREATE USER com FROM LOGIN com;
GO
CREATE SCHEMA [com] AUTHORIZATION [com];
GO
ALTER USER [com] WITH DEFAULT_SCHEMA = [com];
GO
SETUSER 'com';
GO
SELECT USER_NAME();
GO
CREATE TABLE MyTable( test INT);
GO
-- error
-- try this
CREATE TABLE Com.MyTable( test INT);
GO
SETUSER
GO
SELECT USER_NAME();
GO
GRANT CREATE TABLE TO com;
GO
SETUSER 'com';
GO
SELECT USER_NAME();
GO
CREATE TABLE MyTable( test INT);
GO
-- works
-- try this
CREATE TABLE Com.MyTable( test INT);
-- error, table exists.
GO
May 20, 2015 at 11:20 am
Thanks. I though being owner of the schema allows user to do everything it wants.
Is there a way to allow all available permissions to [com] user?
I want [com] user to have all available permissions to [com] schema only.
Also "setuser" is not support in SQL Azure, so not sure how I can grant CreateTable for 'com' to schema 'com'
May 20, 2015 at 12:00 pm
Ah, didn't realize this was Azure SQL Database.
Setuser merely lets me change context. You could use two connections here. One does the granting of permissions as dbo/sa and the other is as "com" to test. All the stuff between "setuser com" and "setuser" is run as the Com user and could be on another connection. Easier to demo than try to explain what runs in which context if you can run SETUSER.
To grant all permissions, as in create procs, views, etc., you need to grant those permissions. While the user as the owner of the schema have all permissions with the schema, you don't have permissions to create objects, which isn't part of the schema. The object gets placed in the schema, but it is a separate entity.
For me, I'd create a role here, not a user. use the GRANT statements to allow the role to build tables, views, procedures (GRANT CREATE VIEW, GRANT CREATE PROCEDURE). Then put the user in a role.
May 20, 2015 at 12:47 pm
If I grant "create table" to [com], would not that mean this user can create table in dbo schema as well as in [com] schema and everywhere else?
May 20, 2015 at 1:55 pm
Shouldn't, but I'd need to test in SQL Azure. AFAIK, this means the user can only create tables in schemas they can control or access. If I create a new user/role and grant just this, with authorization to the [com] schema, the [com] user can't create dbo.MyTable.
YMMV, but I'd be careful here. If you are granting permissions to users/roles in complex ways, the intersections could reveal a hole.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply