Script permissions on all databases

use master
go
begin
declare @databasename varchar(30)
declare cur cursor for
select name from sysdatabases
create table #result
(dbname varchar(30),result varchar(300))
open cur
fetch next from cur into @databasename
while(@@fetch_status=0)
begin
create table #t
(a1 varchar(50)
,a2 varchar(50)
,a3 varchar(50)
,a4 varchar(50)
,a5 varchar(50)
,a6 varchar(50)
,a7 varchar(50))
insert into #t exec sp_helprotect @username = null
insert into #result select @databasename,a5+' '+a6+' on ['+a1+'].['+a2+']'+
CASE
WHEN (PATINDEX('%All%', a7)=0) and (a7 <> '.')
THEN ' ('+a7+')'
ELSE ''
END+' to ['+a3+']' from #t
drop table #t
fetch next from cur into @databasename
end
select * from #result
close cur
deallocate cur
drop table #result
end
go

------------------------------------
http://www.sqlservercentral.com/scripts/Security/62477/

ACCESSING REMOTE DATA SOURCE (Linked Servers and Ad Hoc Queries)

http://dbdigger.blogspot.com/search/label/Linked%20Servers%20for%20SQL%20Server:
===============================================================================
In some situations a SQL Server DBA is required to fetch data from another data source. Other data source may be another instance of SQL Server or any other RDBMS. Also it may be a file. SQL Server 2005 provides two major ways to access data from other data sources. These are

Linked Servers (may use distributed queries)
Ad hoc queries with openrowset
In following elaboration of using these two ways I will discuss SQL Server as remote data source.


CONFIGURATION AND USAGE OF LINKED SERVERS:
------------------------------------------
Linked servers provide SQL Server with access to remote data sources that may be SQL Server also or any other relational DBMS or text file. Remote data sources are connected through OLE DB provider. OLE DB selection determines that what kind of distributed query operations may be implemented.



Create Linked Server
--------------------:
To create the linked server, use the system stored procedure sp_addlinkedserver. To create a linked server for a SQL Server instance named test on host DBA, we will have following format


EXEC sp_addlinkedserver @server= 'dba\test',

@srvproduct= 'SQL Server'

GO


Or if it is default instance then you may just enter host name instead of host\instance format.

Change Any Property Of Linked Server
------------------------------------:
After a linked server has been created then you may change its several properties like collation compatibility, collation name, connection timeout, data access and query time out etc. For example to change query time out property to 60 seconds for the created linked server, I will use following system stored procedure


EXEC sp_serveroption

@server = 'dba\test' ,

@optname = 'query timeout',

@optvalue = 60


View Meta Data Of Linked Server:
---------------------------------
Now we have a linked server with query time out property changed to 60 seconds.
In order to view the meta data of this linked servers use following script.


SELECT [name], data_source,query_timeout, modify_date
FROM sys.servers
WHERE is_linked = 1
GO

Configure Logins Of Linked Server:
----------------------------------
Now we have a linked server attached. But to access the data on it we have to map proper logins as required. In following script I have mapped the user dba (my local user) to user sa of linked server (remote data source). If I set the values of parameter @locallogin = null then all my local users will be mapped against the user sa of remote data source. If yoy set the parameter @useself = true then windows authentication will be used for connection to linked server. Also we have to provide password for remote user that is sa in this case.

EXEC sp_addlinkedsrvlogin
@rmtsrvname = 'dba\test',
@useself = false ,
@locallogin = 'dba', -- if null Applies to all local logins
@rmtuser = 'sa',
@rmtpassword = 'test'
GO



View Login Information Of Linked Server:
-----------------------------------------
To view the login mapping for remote server use the following script.

SELECT s.name LinkedServerName, ll.remote_name, p.name LocalLoginName
FROM sys.linked_logins ll
INNER JOIN sys.servers s
ON s.server_id = ll.server_id
LEFT OUTER JOIN sys.server_principals p
ON p.principal_id = ll.local_principal_id
WHERE s.is_linked = 1
Go


Executing the Queries
----------------------:
Now linked server is configured and logins are also mapped properly. It is now time to execute remote queries as it is actual purpose of all these configurations. Distributed queries reference one or more linked servers. And perform read or update operations against remote tables, views, or stored procedures. The types of query operations that are supported against linked servers depend on the level of support for transactions present in the OLE DB providers used. The basic syntax for referencing a linked server is using a four-part name. To fetch data from pubs.authors of dba\test server

SELECT * FROM [dba\test].pubs.dbo.authors

GO


To execute a system-stored procedure on the linked server
--------------------------------------------------------:
EXEC [dba\test].master.dbo.sp_monitor
GO

Tired Of Using Four Part Name:
------------------------------
If it is not convenient to use four parts lengthy name then you may create a synonym for whole name.
To create a synonym mySyn for whole four parts name

CREATE SYNONYM testSynonym FOR [dba\test].pubs.dbo.authors
GO

Using OPENQUERY :
-----------------
It is relevant to mention here that SQL Server provides another way to execute distributed queries other than using the four parts naming method. OPENQUERY
is a function that issues a pass-through query against an existing linked server and is referenced in the FROM clause of a query just like a table. The syntax is as follows:

OPENQUERY ( linked_server ,'query' )
GO

WIPE OUT :
---------
Now its time to see that how to drop the craeted objects like linked servers, login mappings and synonyms.

--To drop the mapped login that is in our case dba sp_droplinkedsrvlogin 'dba\test' , 'dba'
GO --To drop the linked server dba\test sp_dropserver 'dba\test'
GO --To directly drop linked server along with all mapped logins

sp_dropserver 'dba\test', 'droplogins'

GO --To drop the synonym mySyn for linked server four parts name

drop synonym testSynonym
GO

Ad Hoc QUERIES USING OPENROWSET :
---------------------------------
In some cases it is not required to retain the connection from linked server for a long time. So to execute such Ad hoc queries OPENROWSET command is used. It is referenced in the FROM clause and acts like a table in a SELECT statement. OPENROWSET creates an ad hoc connection to the data and does not use an existing linked server connection to query the remote data source.
This property to use OPENROWSET to query a remote data source is off by default in SQL Server 2005. You may turn it on through surface area configuration.



USAGE

SELECT *
FROM OPENROWSET('SQLNCLI','dba\test';'sa';'test','SELECT * from pubs..authors')
Go

My Script -- DeltaProject

ReadMe:
======
Voor het creëren en registreren van de uitgevoerde scripts in de databases Volg de onderste stappen:

creeër eerst de benodigde tabel en stored procedures:
----------------------------------------------------

1): Executeur eerst de 'S:\DataManagement\MS-SQL Beheer\Changes\DeltaProject\dam_delta_log_01.sql' Script.

2): Run daarna de 'S:\DataManagement\MS-SQL Beheer\Changes\DeltaProject\sp_StartScript_02.sql' Script
en de 'S:\DataManagement\MS-SQL Beheer\Changes\DeltaProject\sp_EndScript_03.sql' Script.


Voor de registratie van de Script in de 'dam_delta_log' tabel ,voer de volgende uit:
-----------------------------------------------------------------------------------

#): Run de 'Uw_Script.sql' Script en vul de benodigde parameters in:

Script_Nummer:
------------- De Nummer van de door u gemaakte script (of door RedGate).

Database_Naam:
------------- De naam van de database waarin de script uitgevoerd moet worden.

Script_Omschrijving:
------------------- De Script omschrijving.

Test:
=====

/*** --------------Test --------------------------------------------------------------

USE [Price]
GO

SET NOCOUNT ON

DECLARE @Script_Nummer varchar(50)
DECLARE @Script_Omschrijving varchar(50)
DECLARE @Database_Naam varchar(50)

set @Script_Nummer = 'Script_Nummer';
set @Script_Omschrijving = 'Omschrijving van de script';
set @Database_Naam = db_name();
exec BeheerDB..sp_StartScript @Script_Nummer,@Database_Naam,@Script_Omschrijving
select * from BeheerDB..dam_delta_log
GO
--Execute uw sscript------------------
--------------------------------------
exec BeheerDB..sp_EndScript @Script_Nummer
select * from BeheerDB..dam_delta_log
GO
-----------------------------------------------------------------------------------
USE [BeheerDB]
GO
/****** Object: Table [dbo].[dam_delta_log] Script Date: 11/21/2008 14:15:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[dam_delta_log](
[id] [int] IDENTITY(1,1) NOT NULL,
[delta] [varchar](50) NOT NULL,
[Omschrijving] [varchar](2000) NULL,
[Server] [varchar](100) NOT NULL CONSTRAINT [DF_ServerNaam] DEFAULT (@@servername),
[databasenaam] [varchar](100) NOT NULL CONSTRAINT [DF_DatabaseNaam] DEFAULT (db_name()),
[Gebruikernaam] [varchar](100) NOT NULL CONSTRAINT [DF_Gebruikernaam] DEFAULT (SYSTEM_USER),
[osuser] [varchar](100) NOT NULL CONSTRAINT [DF_Osuser] DEFAULT (suser_sname()),
[Hostnaam] [varchar](100) NOT NULL CONSTRAINT [DF_HostNaam] DEFAULT (host_name()),
[starttijd] [varchar](100) NOT NULL CONSTRAINT [DF_GetDate] DEFAULT (getdate()),
[eindtijd] [varchar](100) NULL,
CONSTRAINT [PK_dam_delta_log.dam_delta_log] PRIMARY KEY CLUSTERED
(
[id] ASC
)
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

--drop table dbo.dam_delta_log
------------------------------------------------------------------------------------
/*****************************************************************/
/* Script naam : sp_StartScript */
/* */
/* Server naam : <'Server_Naam'> */
/* */
/* Beschrijving :Dit Script wordt gemaakt voor het creëren van */
/* sp_StartScript' Stored Procedure in de 'BeheerDB'*/
/* database In Elke Server die een script moet */
/* uitgevoerd worden. */
/* */
/* */
/* File : S:\DataManagement\MS-SQL Beheer\Changes\ */
/* DeltaProject\sp_StartScript.sql */
/* */
/* Datum : 21-11-2008 */
/* Autore : Bahaa fadam */
/* Versie : */
/* Geupdated : */
/*****************************************************************/



--===============================================================
--# Script om Stored Procedure sp_StartScript te creëren =
--===============================================================

USE [BeheerDB]
GO
/****** Object: StoredProcedure [dbo].[sp_StartScript] Script Date: 11/24/2008 11:06:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


CREATE PROCEDURE [dbo].[sp_StartScript] (@Delta varchar(10),@databasenaam varchar(100),@Omschrijven varchar(2000))
AS

Declare @SQL as VARCHAR(2000)
Declare @RunDate as varchar(100)
Declare @i int
BEGIN TRANSACTION
SET NOCOUNT ON


SELECT @i=1
FROM master.dbo.sysdatabases
WHERE UPPER(Name) = UPPER(CONVERT(varchar(100),@databasenaam))

--zet tijd in variable om ook seconden te zien
SELECT @RunDate=convert(varchar, getdate(), 9)


If @i=1

INSERT INTO dbo.dam_delta_log (delta,Omschrijving,databasenaam,Gebruikernaam,Starttijd)
VALUES(CONVERT(varchar(100), @delta), CONVERT(varchar(100), @Omschrijven),@databasenaam,USER_NAME(),@RunDate )

print(@SQL)
EXEC(@SQL)
COMMIT TRANSACTION
------------------------------------------------------------------------------------
/*****************************************************************/
/* Script naam : sp_EndScript.sql */
/* */
/* Server naam : <'Server_Naam'> */
/* */
/* Beschrijving :Dit Script wordt gemaakt voor het creëren van */
/* 'sp_EndScript' Stored Procedure in de 'BeheerDB'*/
/* database In Elke Server die een script moet */
/* uitgevoerd worden. */
/* */
/* */
/* File : S:\DataManagement\MS-SQL Beheer\Changes\ */
/* DeltaProject\sp_EndScript.sql */
/* */
/* Datum : 21-11-2008 */
/* Autore : Bahaa fadam */
/* Versie : */
/* Geupdated : */
/*****************************************************************/



--===============================================================
--# Script om Stored Procedure sp_EndScript te creëren =
--===============================================================

USE [BeheerDB]
GO
/****** Object: StoredProcedure [dbo].[sp_EndScript] Script Date: 11/24/2008 11:38:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[sp_EndScript]
@delta varchar(10)

AS

update BeheerDB..dam_delta_log
set eindtijd = convert(varchar, getdate(), 9)
where delta = @delta
and eindtijd is null
and starttijd = (select MAX(starttijd)
from BeheerDB..dam_delta_log
where delta = @delta
and eindtijd is null

)


GO
-----------------------------------------------------------------------------
DECLARE @Script_Nummer varchar(50)
DECLARE @Script_Omschrijving varchar(50)
DECLARE @Database_Naam varchar(50)

set @Script_Nummer = 'Script_Nummer';
set @Script_Omschrijving = 'Omschrijving van de script';
set @Database_Naam = db_name();

exec BeheerDB..sp_StartScript @Script_Nummer,@Database_Naam,@Script_Omschrijving
select * from BeheerDB..dam_delta_log
GO

--Execute uw script------------------
--
--------------------------------------

exec BeheerDB..sp_EndScript @Script_Nummer
select * from BeheerDB..dam_delta_log
GO

usp_FindTableUsage

USE [DBeheer]
GO
/****** Object: StoredProcedure [dbo].[usp_FindTableUsage] Script Date: 11/25/2008 13:58:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO




CREATE PROCEDURE [dbo].[usp_FindTableUsage]

AS

SET NOCOUNT ON

DECLARE @vcTableList VARCHAR(8000)
SET @vcTableList = ''

SELECT @vcTableList = COALESCE(@vcTableList+ ', ', '') + name
from sysobjects where type='U'


--Create table to hold table names
DECLARE @tblTableArray TABLE
(
TableName varchar(40)
)

-- load table names into array table
INSERT INTO @tblTableArray
SELECT Element FROM
dbo.split(@vcTableList, ',')


PRINT ''
PRINT 'REPORT FOR TABLE DEPENDENCIES for TABLES:'
PRINT '-----------------------------------------'
PRINT CHAR(9)+CHAR(9)+ REPLACE(@vcTableList,',',CHAR(13)+CHAR(10)+CHAR(9)+CHAR(9))


PRINT ''
PRINT ''
PRINT 'STORED PROCEDURES:'
PRINT ''
SELECT DISTINCT t.TableName , SUBSTRING(o.NAME,1,60) AS [Procedure Name]
FROM sysobjects o
INNER JOIN syscomments c
ON o.ID = c.ID
INNER JOIN @tblTableArray t
ON c.Text LIKE '%[ ,=]' + t.TableName + '[ .,]%'
WHERE o.XTYPE = 'P'
AND o.NAME <> 'usp_FindTableUsage'


ORDER BY t.TableName, [Procedure Name]
PRINT CAST(@@ROWCOUNT as Varchar(5)) + ' dependent stored procedures'

PRINT''
PRINT''
PRINT 'VIEWS:'
PRINT''
SELECT DISTINCT t.TableName , SUBSTRING(o.NAME,1,60) AS [View Name]
FROM sysobjects o
INNER JOIN syscomments c
ON o.ID = c.ID
INNER JOIN @tblTableArray t
ON c.Text LIKE '%[ ,=]' + t.TableName + '[ .,]%'
WHERE o.XTYPE = 'V'

ORDER BY t.TableName, [View Name]
PRINT CAST(@@ROWCOUNT as Varchar(5)) + ' dependent views'

PRINT''
PRINT''
PRINT 'FUNCTIONS:'
PRINT''
SELECT DISTINCT t.TableName , SUBSTRING(o.NAME,1,60) AS [Function Name]
FROM sysobjects o
INNER JOIN syscomments c
ON o.ID = c.ID
INNER JOIN @tblTableArray t
ON c.Text LIKE '%[ ,=]' + t.TableName + '[ .,]%'
WHERE o.XTYPE IN ('FN','IF','TF')

ORDER BY t.TableName, [Function Name]
PRINT CAST(@@ROWCOUNT as Varchar(5)) + ' dependent functions'

PRINT''
PRINT''
PRINT 'TRIGGERS:'
PRINT''
SELECT DISTINCT t.TableName , SUBSTRING(o.NAME,1,60) AS [Trigger Name]
FROM sysobjects o
INNER JOIN syscomments c
ON o.ID = c.ID
INNER JOIN @tblTableArray t
ON c.Text LIKE '%[ ,=]' + t.TableName + '[ .,]%'
WHERE o.XTYPE = 'TR'

ORDER BY t.TableName, [Trigger Name]
PRINT CAST(@@ROWCOUNT as Varchar(5)) + ' dependent triggers'


PRINT''
PRINT''
PRINT 'JOBS:'
PRINT''
DECLARE @table_name SYSNAME;
SELECT @table_name=Element FROM
dbo.split(@vcTableList, ',');

SELECT
j.name,
s.step_name,
s.command
FROM
msdb.dbo.sysjobs j
INNER JOIN
msdb.dbo.sysjobsteps s
ON
j.job_id = s.job_id
WHERE
s.command LIKE '%' + @table_name + '%';


PRINT CAST(@@ROWCOUNT as Varchar(5)) + ' dependent jobs'


RETURN (0)

Error_Handler:
RETURN(-1)

-------------------------------------------------------------
USE [DBeheer]
GO
/****** Object: UserDefinedFunction [dbo].[Split] Script Date: 11/25/2008 14:03:17 ******/
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[Split] ( @vcDelimitedString varchar(8000),
@vcDelimiter varchar

(100) )

RETURNS @tblArray

TABLE

(
ElementID

smallint IDENTITY(1,1), --Array index
Element varchar

(1000) --Array element contents

)
AS

BEGIN


DECLARE
@siIndex smallint,
@siStart smallint,
@siDelSize smallint



SET @siDelSize = LEN(@vcDelimiter)

--loop through source string and add elements to destination table array

WHILE LEN(@vcDelimitedString) > 0

BEGIN

SET @siIndex = CHARINDEX(@vcDelimiter, @vcDelimitedString)

IF @siIndex = 0

BEGIN

INSERT INTO @tblArray VALUES(@vcDelimitedString)

BREAK

END

ELSE

BEGIN

INSERT INTO @tblArray VALUES(SUBSTRING(@vcDelimitedString, 1,@siIndex - 1))

SET @siStart = @siIndex + @siDelSize

SET @vcDelimitedString = SUBSTRING(@vcDelimitedString, @siStart , LEN(@vcDelimitedString) - @siStart + 1)

END

END


RETURN
END



-------------------------------------------------------------

exec usp_FindTableUsage

how to check if linked server exists

how to check if linked server exists in SQL Server 2000:
--------------------------------------------------------
Select 1 Where Exists (Select [SRVID] From master..sysservers Where [srvName]='PSQLWEB1')


how to check if linked server exists in SQL Server 2005:
--------------------------------------------------------

Select 1 Where Exists (Select [SERVER_ID] From sys.servers Where [Name]='PSQLWEB1')