yeah this is purely to test the backups and I only ever had issues previously restoring master from 2000 to a higher version (error: here is already an object named...

Taking full backups do not interrupt the log chain so you can backup the primary databases with affecting log shipping. You should be regularly backing your primary databases anyway. Your...

the procedures are in a schema.(http://technet.microsoft.com/en-us/library/ms190387(v=sql.105).aspx) If you do not specify schema, all your objects (tables, views, procs, triggers, functions, etc) will be in the default schema of the user...

You could grant execute on the schema that the procs are in.

GRANT EXECUTE ON SCHEMA :: schemaName TO username;

Thank you both for your suggestions.

Luis, The pattern splitter is going to be very useful for me, thanks for introducing me to it, but in this case I dont...

the sys.database_permissions catalog view will give you which permissions are granted. join to the sys.database_principals to filter by role name.

SELECT ob.type_desc, dp.permission_name, dp.state_desc,ObjectName = OBJECT_NAME(major_id),...

hoseam (4/23/2014)

---

I do have that line of code, I just didn't put it here.

I don't think I will be able to list...

Use a column list in your select statement instead of select *, that way you can just exclude the ID column. Your @sqlstatement to create the table is also incorrect....

Because your sql agent account is local system on the...

you dont need to have the same AD account running sql agent. What you will need is to grant permission to the SQL Agent service account on your remote server...

what error are the sql agent jobs showing?

The most probable issue is the service account that sql agent is running under does not have access to write the the...

My first question would be are you sure your remote data collection is loading your MDW correctly.

what is the status of the agent jobs on your remote(not the one...

you have to change the order of your substrings to end up with the format yyyymmdd.

declare @date varchar(20)

set @date = '140416'

select CAST(('20' + LEFT(@date,2) + SUBSTRING(@date, 3, 2)+SUBSTRING(@date,5,2) ...

check this article

http://msdn.microsoft.com/en-us/library/cc646024(v=sql.110).aspx

Without DDL and sample data it makes it difficult to give a good answer. (see link in my signature)

based on what you have provided you could create a CTE for...