September 20, 2008 at 12:18 am
In my scenario
There are 10 users of the Database in Sql server 2005.
users staring with FWACDH/XYZ, FWACDH/ABC and so on....
I am using one SP , which is generation one table and uses that table for some manipulation.
Later on in same SP i drop that table.
Now problem is when user logeed into the system using this SSPI authentication.
He is not able to drop a table because i have written simplate
ddl statment to drop that table in that store procedure. like drop table Tablename.
When i saw that table in table list it was something like FWACDH/XYZ.Tablename instead of Dbo.tabelname.
So how should i write drop statment in my storeprocedure so that it drops this table.
Do i need to check who is the user logged in this database and that i have to write drop statment according to that?
Help me out.
September 20, 2008 at 4:26 am
Hello,
I presume you are creating a permanent table rather than a temporary one? I guess you have a reason for that.
You can specify the schema name when you create the table e.g. dbo.Tablename and the same schema when you drop the table.
In this case all instances of the SP will access the same table, but presumably that is what you want, or otherwise you could use a table variable, a temp table or a "Permanent" temp table.
Regards,
John Marsh
www.sql.lu
SQL Server Luxembourg User Group
September 20, 2008 at 6:47 am
You really should be creating temporary tables (#tablename) in your proc, and not a permanent table if you are constantly dropping them. You'll run into concurrency issues at some point.
September 22, 2008 at 12:05 am
Hello
You mentioned that your table name is some thing different than
dbo. ,actually when you assign your table to particular schema
then your table name will appear with that schema instead of defult schema dbo. make sure that while dropping the table you should specify the same schema extension to which the table belongs to.
use like this
drop table [SchemaName].TableName
it works fine .
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply