How to uncheck a permissions checkbox

Question

Post reply

How to uncheck a permissions checkbox

Slick84

SSCertifiable

Points: 5602
More actions
December 18, 2009 at 10:17 am

#137743

Hi,
I'm trying to delete a user from a database but I get the message that the user owns some schema's.
So I went into the user permissions settings and tried to uncheck the checkboxes for the schema rolls but was unable to.
It's not checked, but unusually filled green. Please see screenshot.
Thanks,
S
--
:hehe:

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

The user owns the schemas. You will need to transfer that or remove that user from owning the schemas.

http://msdn.microsoft.com/en-us/library/ms173423.aspx

You may want to verify that the account is not the database owner

try sp_changedbowner to change that as well.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Slick84 SSCertifiable Points: 5602 More actions · Answer 2

Thanks Cirque.

However, I did it a little differently.

I altered the authorization querying the DB where the user existed by typing in the below T-SQL:

USE [Database]

GO

ALTER AUTHORIZATION ON SCHEMA::db_ddlAdmin to dbo

GO

ALTER AUTHORIZATION ON SCHEMA::db_owner to dbo

GO

DROP USER [DOMAIN\UserName]

GO

And then I was able to simply drop the user from the database.

SOURCE: MSDN Article on ALTER AUTHORIZATION

Thanks,

S

--
:hehe:

SQLRNNR SSC Guru Points: 281334 More actions · Answer 3

Thanks for the feedback. That was the next suggestion.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Slick84 SSCertifiable Points: 5602 More actions · Answer 4

Okay so I'll expand on this.

Basically, I have a user who is part of a active directory security group called DataWarehouseTeam which has 5 other people. The DataWarehouseTeam login has the "Public" server role assigned to it at the server level security and access to about 6 different databases on that server through the "User Mapping" tab.

At the database level security for one of the databases, this same group has "db_datareader" Database Role Membership. At the database permissions level, this group has the explicit permission of "Connect".

Now this user needs to create tables. I added his domain account as "New User" at the Database Level security giving him "db_owner" Database Role Membership. At the database permissions level, I gave him GRANT on :

ALTER SCHEMA, ALTER, CONNECT CONTROL, CREATE FUNCTION, CREATE PROCEDURE, CREATE TABLE, CREATE VIEW, DELETE, EXECUTE, INSERT, SELECT, UPDATE, SHOWPLAN, VIEW DEFINITION.

He still can't create a table. Below is the error received:

The server principal "this_user" is not able to access the database "Unrelated Database" under the current security context.

The funny part is not only is he unable to create a table, but the error message points to a database which isn't related to the create table query! Any ideas?

Thanks,

S

--
:hehe:

SQLRNNR SSC Guru Points: 281334 More actions · Answer 5

Something may be caching in the system for a different database.

When he tries to create the table, does he do it through the GUI or TSQL? Have you watched him to make sure he isn't doing something weird?

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Slick84 SSCertifiable Points: 5602 More actions · Answer 6

I haven't watched him but he sent me his T-SQL which he was trying to run. I told him to use the "USE Database" statement. However, he was doing it like below.

CREATE TABLE DatabaseName.SchemaName.TableName (Column1 INT IDENTITY PRIMARY KEY, Column2 VARCHAR(20))

--
:hehe:

SQLRNNR SSC Guru Points: 281334 More actions · Answer 7

The Use Database statement may correct this issue.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Slick84 SSCertifiable Points: 5602 More actions · Answer 8

Great, hopefully it works. Waiting to hear back from him.

Thanks for your help.

--
:hehe: