question

SataleSandipPune-5594 avatar image
0 Votes"
SataleSandipPune-5594 asked OlafHelper-2800 answered

How to drop database table created by specific user

There is need to setup a maintenance job to drop database tables created by particular user list in SQL SERVER.

I was trying to use Sys.Objects, Sys.tables and sysusers objects but not able to build exact TSQL For this.

Please provide solution.

sql-server-generalsql-server-transact-sql
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

OlafHelper-2800 avatar image
0 Votes"
OlafHelper-2800 answered

created by particular user list in SQL SERVER.

SQL Server don't log how created which object in a database.
To achieve this you have to log it on your own, e.g. using a DDL trigger.
See
https://docs.microsoft.com/en-us/sql/relational-databases/triggers/ddl-triggers?view=sql-server-ver15
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

NandanHegde-7720 avatar image
0 Votes"
NandanHegde-7720 answered SataleSandipPune-5594 edited

Hey,
Below query would help you get that list :
select
so.name, su.name, so.crdate
from
sysobjects so
join
sysusers su on so.uid = su.uid

and then you can use dynamic sql to drop those

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

It's giving me results as follows,

Table Name | dbo| Created Date

Instead of 'dbo' as owner name, result should return Login name used during windows Authentication for TSQL connection.

Is there is any code which should return result similar with "Schema change history" report for a database?

0 Votes 0 ·