• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 387
  • Last Modified:

Delete a user form Sql permissions

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
kdeutsch
Asked:
kdeutsch
  • 9
  • 6
  • 3
2 Solutions
 
BrandonGalderisiCommented:
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
 
Daniel_PLDB Expert/ArchitectCommented:
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
 
kdeutschAuthor Commented:
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
Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

 
Daniel_PLDB Expert/ArchitectCommented:
Do you want to drop schema or user or both? ;)
0
 
BrandonGalderisiCommented:
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
 
kdeutschAuthor Commented:
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
 
Daniel_PLDB Expert/ArchitectCommented:
You need to transfer that object also to dbo
ALTER SCHEMA dbo TRANSFER Person.051003_make_table;
0
 
kdeutschAuthor Commented:
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
 
BrandonGalderisiCommented:
Since the object name starts with a number, you must encapsulate it in brackets [].

ALTER SCHEMA dbo TRANSFER jeff.beaner.[051003_make_table]
0
 
kdeutschAuthor Commented:
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
 
BrandonGalderisiCommented:
Is the schema [jeff.beaner]?  Is the DB [jeff] and the schema [beaner]?



ALTER SCHEMA dbo TRANSFER [schemaName].[tableName]
0
 
kdeutschAuthor Commented:
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
 
BrandonGalderisiCommented:
The proper syntax would then be.

ALTER SCHEMA dbo TRANSFER [jeff.beaner].[051003_make_table]
0
 
kdeutschAuthor Commented:
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
 
BrandonGalderisiCommented:
What does this yield?

select * from information_schema.tables where table_schema = 'jeff.beaner'
0
 
kdeutschAuthor Commented:
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
 
kdeutschAuthor Commented:
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
 
kdeutschAuthor Commented:
thanks
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 9
  • 6
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now