domingo, 10 de enero de 2010

List permission tables

This SQL sentences allows to knows the permissions of one table or all tables that are currently in you database.

The fist one shows the permissions of one table:

select object_name(major_id) as object,
user_name(grantee_principal_id) as grantee,
user_name(grantor_principal_id) as grantor,
permission_name,
state_desc
from sys.database_permissions
where major_id = object_id('XXX_TABLE')
and class = 1


This other shows the permissions of all tables:

select * From fn_my_permissions(NULL, 'XXX_DATABASE')

Delete data from all tables

Sometimes we need to erase data from all tables of the database, and you have to delete it for each table, and in the correct order for saving Primary keys, this script allows to erase all data saving primary keys.

CREATE PROCEDURE EmplyAllTables AS

EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'

EXEC sp_MSForEachTable 'DELETE FROM ?'

EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL'

GO

I hope that it helps you.