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';

Create Insert Command for SQL tables

declare @columnnames nvarchar(4000) , @insertcommand nvarchar(4000) , @tablename nvarchar(100) ,@str1 nvarchar(1000), @tmptablename nvarchar(100), @whereclause nvarchar(1000)
set @columnnames = ''
set @tablename = 'planresource'
set @whereclause = ' where planid in (select planid from [plan] where planid >= 1500) '

set @tmptablename = '##tmp__'+@tablename
set @str1 = 'select * into '+@tmptablename+' from ['+@tablename+'] '+@whereclause
--print @str1
exec sp_executesql @str1
set @str1 = 'alter table '+@tmptablename+' add insertcommand nvarchar(3000)'
exec sp_executesql @str1



select @columnnames = @columnnames + '['+column_name+'], ' from information_schema.columns where table_name = @tablename
--print @columnnames
--print left(@columnnames , len(@columnnames)-1)
set @columnnames = left(@columnnames , len(@columnnames)-1)
--print @columnnames
set @insertcommand = 'insert into ['+@tablename+'] ('+@columnnames+') values ('
--print @insertcommand


set @str1 = 'update '+@tmptablename+' set insertcommand = '''+@insertcommand +''''
--print @str1
exec sp_executesql @str1


declare @sqlstring2 nvarchar(4000), @colname nvarchar(100)
set @sqlstring2 = 'update '+@tmptablename+' set insertcommand = insertcommand + '''
DECLARE tmptable_name CURSOR FOR select column_name from information_schema.columns where table_name = @tablename
OPEN tmptable_name

FETCH NEXT FROM tmptable_name INTO @colname

WHILE @@FETCH_STATUS = 0
BEGIN
--set @sqlstring2 = @sqlstring2 + ' convert(nvarchar(1000),isnull(replace(['+@colname+'],'''''''',''''''''''''),''''))+'''''')'' '
set @sqlstring2 = @sqlstring2 + '''''''+ convert(nvarchar(1000),isnull(replace(['+@colname+'],'''''''',''''''''''''),''''))+'''''', '
FETCH NEXT FROM tmptable_name INTO @colname
end
--print @sqlstring2
CLOSE tmptable_name
DEALLOCATE tmptable_name
set @sqlstring2 = left(@sqlstring2 , len(@sqlstring2)-1)+')'''
--print @sqlstring2
exec sp_executesql @sqlstring2

--select insertcommand from ##tmp__landingpage
--select insertcommand from #tmp




declare @string3 nvarchar(1000)
set @string3 =
'
declare @sqlstring nvarchar(4000)
DECLARE tmptable_name CURSOR FOR select insertcommand from '+@tmptablename+'
OPEN tmptable_name

FETCH NEXT FROM tmptable_name INTO @sqlstring

WHILE @@FETCH_STATUS = 0
BEGIN
print @sqlstring
print ''go''
FETCH NEXT FROM tmptable_name INTO @sqlstring
end

CLOSE tmptable_name
DEALLOCATE tmptable_name
'

--print @string3
exec sp_executesql @string3


set @string3 = 'drop table '+@tmptablename
exec sp_executesql @string3