how to script security permissions?

I have a very large table that is refreshed periodically. Since it's
so big, I do a 'drop table', 'create table', 'create index' then a bulk
load. It's much faster than doing a 'delete from'. I also do a
'shrinkdb' as part of this process.

The problem, however, is that the user permissions are also dropped in
this process. So, how can I script the user permissions? For example,
how do I give 'MyUser' select access to 'MyTable' in 'MyDB'? Many
thanks!!

Eben Yong
yonglove@.yahoo.comGRANT SELECT ON MyTable TO MyUser|||Eben (yonglove@.yahoo.com) writes:
> I have a very large table that is refreshed periodically. Since it's
> so big, I do a 'drop table', 'create table', 'create index' then a bulk
> load. It's much faster than doing a 'delete from'. I also do a
> 'shrinkdb' as part of this process.

So why not do a TRUNCATE TABLE instead? This is a minimally logged
operation, and you maintain indexes, permissions etc.

> The problem, however, is that the user permissions are also dropped in
> this process. So, how can I script the user permissions? For example,
> how do I give 'MyUser' select access to 'MyTable' in 'MyDB'? Many
> thanks!!

I guess you can do this with DMO, if you want to do this programmatically.
However, I have not used DMO myself.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland Sommarskog wrote:
> Eben (yonglove@.yahoo.com) writes:
> > I have a very large table that is refreshed periodically. Since it's
> > so big, I do a 'drop table', 'create table', 'create index' then a bulk
> > load. It's much faster than doing a 'delete from'. I also do a
> > 'shrinkdb' as part of this process.
> So why not do a TRUNCATE TABLE instead? This is a minimally logged
> operation, and you maintain indexes, permissions etc.
Although keeping indexes is sometimes a good thing, it's also sometimes
beneficial to drop all indexes before the truncate and only add them
back when all the data loading is complete.

Of course, it depends on how the data loading is being managed (in my
case, I happen to be loading 3 1/2 million records from a non-R DBMS,
and having to do it one row at a time - I'd rather not have the indexes
rebuilt for every insert)

Damien|||Thank you, everyone. I did not know about the TRUNCATE TABLE option.
But accomplishing this objective using DROP TABLE, CREATE TABLE, and so
on, has required that I learn many other SQL Server methods, so it's
good for me. I spent more time developing the solution but SQL Server
doesn't care one way or the other and both methods still get the job
done in the same amount of time. So, once again, thanks everyone for
your input!

How to retrieve all information abt all constraints inorder to drop and recreate

Hello,
I need to retrieve all the information about all the foreign key constraints,inorder to store them temporarily (to be deleted later)and then recreated after making the necessary modifications to the concerned tables.
The stored proc sp_helpconstraint shows all the constraint types, their user-defined or system-supplied name, the columns on which they have been defined, and the expression that defines them.But I don't know whether it can be manipulated to get what I want.I need to get it done programmatically..so that I can integrate it in my program which I'm building up progressively.
Any help or scripts would be appreciated!First step - dropping:

select 'alter table '+TABLE_NAME+ ' DROP CONSTRAINT '+CONSTRAINT_NAME
from INFORMATION_SCHEMA.TABLE_CONSTRAINTS
where CONSTRAINT_TYPE='FOREIGN KEY'|||i first need to store the information about the constraints before dropping them, so that they can be recreated later after the necessary changes have been made..i have 200+ tables...any tips?|||select *
INTO TempConstraintStore
from INFORMATION_SCHEMA.TABLE_CONSTRAINTS
where CONSTRAINT_TYPE='FOREIGN KEY'

select 'alter table '+TABLE_NAME+ ' DROP CONSTRAINT '+CONSTRAINT_NAME
from INFORMATION_SCHEMA.TABLE_CONSTRAINTS
where CONSTRAINT_TYPE='FOREIGN KEY'

INSERT INTO INFORMATION_SCHEMA.TABLE_CONSTRAINTS
SELECT * FROM TempConstraintStore

or something like that|||INSERT into system tables...hmmmmmmm

No thanks...

Just go in and Script the contraints and save the script

Do the drops, necessary changes have been made..then rerun the script

What are the changes?

If RI is out of wack the constraints will fail....|||i'm aware that scripting the constraints using EM would make life easier,but no such luck for me..it has to be done using TSQL..i'm using the information schemas to retreive info,like referential_constraints, columns,constraint_column_usage..is that enough??|||try to use SQL-DMO with something like that (VBS):

Set dmoSQLServer = CreateObject("SQLDMO.SQLServer")
dmoSQLServer.Connect "MyServer", "MyLogin", "MyPSWD"

For Each dmoObj In dmoSQLServer.Databases("MyDBName").Tables
If dmoObj.SystemObject = False Then
dmoObj.Script 134348800, dmoObj.Name & ".fky"
End If
Next

dmoSQLServer.Disconnect
Set dmoSQLServer = Nothing

One file storing all FK creates for each table (TableName.fky). If table have no FK this file will be empty|||Similar problem?

I simply want to add a foreign-key constraint to an existing column.

This is what I have but no luck so far.

alter table table_name with check
alter column column_name
add constraint foreign_key_name
references Reference_Table (Reference_column)

I know it can't be far from this - I think I've even done it before!

John|||I'd use something like:ALTER TABLE myTable
ADD CONSTRAINT myConstraint
FOREIGN KEY (myColumn)
REFERENCES anotherTable (differentColumn)-PatP