Grant View Permission on Stored Procedures

--select * from information_Schema.routines
--select specific_name from information_Schema.routines

declare @name nvarchar(1000), @string nvarchar(4000),@user nvarchar(100)
set @user = 'SPARKSTAGE\developers'

DECLARE Cur_name CURSOR FOR
select specific_name From information_Schema.routines

OPEN Cur_name
FETCH NEXT FROM Cur_name INTO @name

WHILE @@FETCH_STATUS = 0
BEGIN
print '================================='
print @name
print '================================='
set @string ='GRANT VIEW DEFINITION ON [dbo].['+@name+'] TO ['+@user+']'
print @string
exec sp_executesql @string

FETCH NEXT FROM Cur_name INTO @name
END
CLOSE Cur_name
DEALLOCATE Cur_name

Extract Text after 2 commas in a string

select MemberGroupLangStr
, CHARINDEX(',',MemberGroupLangStr)
, substring(MemberGroupLangStr,CHARINDEX(',',MemberGroupLangStr)+1 , len(MemberGroupLangStr))
, CHARINDEX(',',substring(MemberGroupLangStr,CHARINDEX(',',MemberGroupLangStr)+1 , len(MemberGroupLangStr)))
, CHARINDEX(',',MemberGroupLangStr) + CHARINDEX(',',substring(MemberGroupLangStr,CHARINDEX(',',MemberGroupLangStr)+1 , len(MemberGroupLangStr)))
, substring(MemberGroupLangStr , CHARINDEX(',',MemberGroupLangStr) + CHARINDEX(',',substring(MemberGroupLangStr,CHARINDEX(',',MemberGroupLangStr)+1 , len(MemberGroupLangStr))) + 1 , len(MemberGroupLangStr))
From MembersWithUnapprovedTextAttributes

Extract Text between two commas

select MemberGroupLangStr,
CASE
-- when no second semi-colon,
WHEN CHARINDEX(',',(SUBSTRING(MemberGroupLangStr,CHARINDEX(',',MemberGroupLangStr,1)+1,99))) = 0
-- then extract the full string from first semi-colon to the max length of 99
THEN LTRIM(RTRIM(SUBSTRING(MemberGroupLangStr,CHARINDEX(',',MemberGroupLangStr,1)+1,99)))
-- else extract the full string from the first semi-colon up to second semi-colon
ELSE LTRIM(RTRIM(SUBSTRING(
/* > */ MemberGroupLangStr,
/* */ CHARINDEX(',',MemberGroupLangStr,1) + 1,
/* */ CHARINDEX(',', SUBSTRING(MemberGroupLangStr,
CHARINDEX(',', MemberGroupLangStr, 1) + 1,
99)) - 1
) ) )
END AS Result_string
From MembersWithUnapprovedTextAttributes

Grant access to database in a server for domain user

EXEC master..sp_MSForEachDB '

if(''?'' not in (''master'',''msdb'',''tempdb'',''model''))
begin
print ''use ''+''?''
print ''go''
print ''CREATE USER [Domain\user] FOR LOGIN [Domain\user]''
print ''go''
print ''EXEC sp_addrolemember N''''db_datareader'''', N''''Domain\user''''''
print ''go''
print ''EXEC sp_addrolemember N''''db_executor'''', N''''Domain\user''''''
print ''go''

end
'
go

List objects per filegroup

IF OBJECT_ID('tempdb.dbo.#FileGroup') IS NOT NULL

DROP TABLE #FileGroup



IF OBJECT_ID('tempdb.dbo.#ObjectFileGroup') IS NOT NULL

DROP TABLE #ObjectFileGroup



CREATE TABLE #FileGroup (

FileGroup sysname

)

CREATE TABLE #ObjectFileGroup (

ObjectName sysname,

ObjectType varchar(20),

FileGroupID int,

FileGroup sysname

)



SET NOCOUNT ON



DECLARE @TableName sysname

DECLARE @id int



DECLARE cur_Tables CURSOR FAST_FORWARD FOR

SELECT TableName = [name], id FROM dbo.sysobjects WHERE type = 'U'

OPEN cur_Tables

FETCH NEXT FROM cur_Tables INTO @TableName, @id

WHILE @@FETCH_STATUS = 0

BEGIN

TRUNCATE TABLE #FileGroup

INSERT #FileGroup (FileGroup)

EXEC sp_objectfilegroup @id

INSERT #ObjectFileGroup (ObjectName, ObjectType, FileGroupID, FileGroup)

SELECT @TableName, 'TABLE', FILEGROUP_ID(FileGroup), FileGroup

FROM #FileGroup

FETCH NEXT FROM cur_Tables INTO @TableName, @id

END



CLOSE cur_Tables

DEALLOCATE cur_Tables



INSERT #ObjectFileGroup (ObjectName, ObjectType, FileGroupID, FileGroup)

SELECT OBJECT_NAME(id) + ' * ' +[name], 'INDEX', groupid, FILEGROUP_NAME(groupid) FROM dbo.sysindexes

WHERE FILEGROUP_NAME(groupid) IS NOT NULL

AND OBJECT_NAME(id) NOT LIKE 'sys%'

AND [name] NOT LIKE '_WA_Sys%'

AND [name] NOT LIKE 'Statistic_%'



SELECT FileGroupName = FILEGROUP_NAME(sf.groupid),/*ofg.FileGroup, */ofg.ObjectName, ofg.ObjectType, FileName = sf.filename, FileSize = sf.[size] / 128

FROM #ObjectFileGroup ofg

RIGHT JOIN dbo.sysfiles sf

ON ofg.FileGroupID = sf.groupid

ORDER BY FileGroup, ObjectName



SQL 2005 (very simplified)



SELECT

fg.data_space_id, fg.name,

ObjectName = OBJECT_NAME(p.object_id), p.index_id

,df.name, df.physical_name, [Size] = df.size*8/1024

FROM sys.filegroups fg

LEFT JOIN sys.database_files df

ON fg.data_space_id = df.data_space_id

LEFT JOIN sys.partitions p

ON fg.data_space_id = p.partition_number

WHERE (p.object_id>4096 or p.object_id IS NULL)

Change all db owners to 'sa'

sp_helpdb
go
EXEC master..sp_MSForEachDB '

if(''?'' not in (''master'',''msdb'',''tempdb'',''model''))
begin
print ''use ''+''?''
print ''go''
print ''exec sp_changedbowner sa''
print ''go''
end
'
go

sp_helpdb
go

Revoke & Grant Execut to a Database Role

--Check if role not exisit then create new role
declare @User varchar(25), @Force bit

set @User = 'db_executor'
set @force = 0

if not exists (select * from sys.database_principals where name = 'db_executor' and (type = 'R' or type = 'A'))
begin
CREATE ROLE [db_executor] AUTHORIZATION [dbo]
end
GO


-- revoke all previous access
declare @User varchar(25), @Force bit

set @User = 'db_executor'
set @force = 0

set nocount on

declare @Name varchar(100),
@Command varchar(255),
@uid int
declare @ProcCount int
set @ProcCount = 0

select @Name = min([name])
from sysobjects
where type in ('P', 'FN', 'IF') and
left([name],3) <> 'dt_' and
[name] <> 'up_GrantExecute'

while @Name is not null begin
select @uid = uid
from sysusers
where [name] = @User

if exists (select * from sysprotects where id = object_id(@Name) and action = 224 and uid = @uid) or
@Force = 1 begin
set @Command = 'REVOKE execute on ' + @Name + ' to ' + @User
print @Command
set @ProcCount = @ProcCount + 1
exec (@Command)
end

select @Name = min(name)
from sysobjects
where type in ('P', 'FN', 'IF') and
left([name],3) <> 'dt_' and
[name] <> 'up_GrantExecute' and
[name] > @Name
end


-- grant execute access

set @force = 0
set @ProcCount = 0

select @Name = min([name])
from sysobjects
where type in ('P', 'FN', 'IF') and
left([name],3) <> 'dt_' and
[name] <> 'up_GrantExecute'

while @Name is not null begin
select @uid = uid
from sysusers
where [name] = @User

if not exists (select * from sysprotects where id = object_id(@Name) and
action = 224 and uid = @uid) or
@Force = 1 begin
set @Command = 'grant execute on ' + @Name + ' to ' + @User
print @Command
set @ProcCount = @ProcCount + 1
exec (@Command)
end

select @Name = min(name)
from sysobjects
where type in ('P', 'FN', 'IF') and
left([name],3) <> 'dt_' and
[name] <> 'up_GrantExecute' and
[name] > @Name
end

SQL server security Audit; script to expose logins and access to DBs

print replicate('-',100)
print 'Server: ' +@@servername
print replicate('-',100)
print '-------------sql_logins------------------'
SELECT * FROM sys.sql_logins


print '-------------sp_helpsrvrolemember------------------'
Exec sp_helpsrvrolemember
print replicate('-',100)

EXEC sp_MSforeachdb 'use [?]
print ''Database: ''+ db_name()
print replicate(''-'',100)
print ''-------------sp_helprolemember------------------''
Exec sp_helprolemember

print ''-------------sp_helprotect----------------------''
Exec sp_helprotect
print replicate(''-'',100)
'

Grant Trace permission to SQL user

grant alter trace to traceuser
revoke alter trace to traceuser

SELECT * FROM fn_my_permissions(null,null)

User : traceuser
Pass: tr@ceus3r

Find all tables with a specific column name within entire server

I put this query together to find all tables with specific column_name within the entire databases on a server.

Method 1:

use master
go
if(object_id('tempdb..#tmp')>0)
drop table #tmp

create table #tmp (dbname nvarchar(1000), tablename nvarchar(1000), columnname nvarchar(1000))
declare @dbname nvarchar(100), @sqlstring nvarchar(1000), @columnname nvarchar(100)
set @columnname = 'promotionid'
DECLARE dbs_name CURSOR FOR select name from sys.databases
OPEN dbs_name

FETCH NEXT FROM dbs_name INTO @dbname

WHILE @@FETCH_STATUS = 0
BEGIN
set @sqlstring = 'insert into #tmp select table_catalog, table_name, column_name from '+@dbname+'.information_schema.columns where column_name = '''+@columnname+''' '
exec sp_executesql @sqlstring
FETCH NEXT FROM dbs_name INTO @dbname
end

CLOSE dbs_name
DEALLOCATE dbs_name

select * from #tmp


Method 2:

EXEC master..sp_MSForEachDB '
IF EXISTS
(
SELECT 1
FROM [?].INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME = ''promotionid''
)
BEGIN
SELECT ''[?]'';


SELECT ''['' + TABLE_SCHEMA + '']'', ''['' + TABLE_NAME + '']''
FROM [?].INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME = ''promotionid'';
END';