Solved

Delete a user form Sql permissions

Posted on 2011-03-09
18
384 Views
Last Modified: 2012-06-27
Ok for the life of me I cannot find out where this use is embeded. These are the errors it gives on me.

the database principal owns a schema in the db and cannot be dropped.

Second error
Referenced by object 051003_make _table

I have not extran schemas that where made on the db and everywhere i go in and look the person is not in there.
0
Comment
Question by:kdeutsch
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 6
  • 3
18 Comments
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 35083764
What does this yield?  It will tell you who the owner of each schema is.

select s.name AS 'SchemaName'
     ,u.name AS 'PrincipleName'
from sys.schemas s
  INNER JOIN sys.sysusers u
    ON s.principal_id = u.uid
0
 
LVL 14

Expert Comment

by:Daniel_PL
ID: 35084149
When you find particular login and owned schema you need to tranfer ownership to other user to be able to drop that old user.
ALTER AUTHORIZATION ON SCHEMA::<schema_name> TO <new_owner_name>;
0
 

Author Comment

by:kdeutsch
ID: 35084547
All,

Ok I cannot even delete the schema that he created and belonged to because it gives this error

Cannot drop schema 'Person' because it is being referenced by object 051003_make_table

I don't get where to get rid of this reference.
0
Comparison of Amazon Drive, Google Drive, OneDrive

What is Best for Backup: Amazon Drive, Google Drive or MS OneDrive? In this free whitepaper we look at their performance, pricing, and platform availability to help you decide which cloud drive is right for your situation. Download and read the results of our testing for free!

 
LVL 14

Expert Comment

by:Daniel_PL
ID: 35084573
Do you want to drop schema or user or both? ;)
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 35084574
Ok.  If you drop a schema, or move the objects to a different schema (ex. dbo) then you run the risk of breaking any application or source that references that table by the current schema.  To see a list of tables in the schema....

select * from information_schema.tables where table_schema = 'person'
0
 

Author Comment

by:kdeutsch
ID: 35084722
BrandonGalderisi:

If I run that on the database in question it comes up with nothing.

I want to drop the schema and user, I have alrady given ownership of the Db to another account.  This user has not been in the db for 2 versions but personnel have just backed up and resotred DB without regard to permissions or cutting ties to them.  i am moveing everything over to a new server and cleaning up all the junk.
0
 
LVL 14

Assisted Solution

by:Daniel_PL
Daniel_PL earned 250 total points
ID: 35084827
You need to transfer that object also to dbo
ALTER SCHEMA dbo TRANSFER Person.051003_make_table;
0
 

Author Comment

by:kdeutsch
ID: 35085017
I keep getting a syntax error
ALTER SCHEMA dbo TRANSFER jeff.beaner.051003_make_table


Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '.051003'.
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 35085041
Since the object name starts with a number, you must encapsulate it in brackets [].

ALTER SCHEMA dbo TRANSFER jeff.beaner.[051003_make_table]
0
 

Author Comment

by:kdeutsch
ID: 35085084
Hi,
If I ran as is I get his error
Msg 117, Level 15, State 24, Line 1
The object name 'jeff.beener.051003_make_table' contains more than the maximum number of prefixes. The maximum is 1.

ALTER SCHEMA dbo TRANSFER [051003_make_table]
If I take out the person i get this error
Msg 15151, Level 16, State 1, Line 1
Cannot find the object '051003_make_table', because it does not exist or you do not have permission.
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 35085107
Is the schema [jeff.beaner]?  Is the DB [jeff] and the schema [beaner]?



ALTER SCHEMA dbo TRANSFER [schemaName].[tableName]
0
 

Author Comment

by:kdeutsch
ID: 35085234
Hi,
the DB is called Milpo and the schema is jeff.beaner

I tried running this
select * from sys.schemas where principal_id = user_id('jeff.beaner')

and i get nothing, if I look in all the properties of the schemas and assign them to there originals I still cannot delete him and gete that same error, is this something left over from sql 7 or 2000.
0
 
LVL 39

Accepted Solution

by:
BrandonGalderisi earned 250 total points
ID: 35085241
The proper syntax would then be.

ALTER SCHEMA dbo TRANSFER [jeff.beaner].[051003_make_table]
0
 

Author Comment

by:kdeutsch
ID: 35085273
UGH,

I get this

Msg 15151, Level 16, State 1, Line 1
Cannot find the object '051003_make_table', because it does not exist or you do not have permission.

I added myself as a user and gave me dbo provledges and I still get the error.
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 35085294
What does this yield?

select * from information_schema.tables where table_schema = 'jeff.beaner'
0
 

Author Comment

by:kdeutsch
ID: 35085306
Ok,
sorry it was supposed to be 2 ee's that worked but still cannot drop the schema now it gives me
 because it si being references by object 'Example_into'
0
 

Author Comment

by:kdeutsch
ID: 35085378
All,
ok finally able to get rid of this user, had to run command 16 times and finally was able to delete all.  thank you for the help.
0
 

Author Closing Comment

by:kdeutsch
ID: 35085505
thanks
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
In this video, viewers are given an introduction to using the Windows 10 Snipping Tool, how to quickly locate it when it's needed and also how make it always available with a single click of a mouse button, by pinning it to the Desktop Task Bar. Int…
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…

717 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question