Change schema


Change schema on tables and stored procedures in sql 2005 :

–Change schema for stored procedures

SELECT ‘ALTER SCHEMA dbo TRANSFER ‘+’[‘ + s.Name + ‘].’ + p.Name
FROM sys.Procedures p INNER JOIN
sys.Schemas s on p.schema_id = s.schema_id WHERE
s.Name ‘dbo’

–Alter schemas on tables

SELECT ‘ALTER SCHEMA dbo TRANSFER
‘ + TABLE_SCHEMA + ‘.’ + TABLE_NAME FROM
INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = ‘dbo’

Grant rights to run profiler in sql 2005 :In Sql server 2005 I finaly can grant users the right to do traces, Note! traces does hurt the performance so this is not an right the users should have permanently! For specific task we can grant it but on the first place the tracing should be done by us. I can grant this right for users for a limited period of time

GRANT ALTER TRACE TO [xyzthc0018]

To be bale to se use t.ec sp:who we can also grant this right:

GRANT VIEW server STATE TO [xyzthc0018]

 Från Khan sql dba – mcitp www.addarr.com