Generate Object Level Permission

Run below script to generate users and permission in any databases.
Mostly it helps to generate users and its permission before refreshing from prod database or migrating to another server.




SELECT 'CREATE USER [' + name + '] for login [' + name + ']' from sys.database_principals
-- where
 --Type = 'U'
 --and
 -- name not like '%'
 --------------------------------------
 SELECT 'EXECUTE sp_AddRoleMember ''' + roles.name + ''', ''' + users.name + '''' from sys.database_principals users inner join sys.database_role_members link on link.member_principal_id = users.principal_id inner join sys.database_principals roles on roles.principal_id = link.role_principal_id
 -----------------------------------
 if object_id('dbo.tempPermissions') is not null Drop table dbo.tempPermissions Create table tempPermissions(ID int identity , Queries Varchar(255)) Insert into tempPermissions(Queries) select 'GRANT ' + dp.permission_name collate latin1_general_cs_as + ' ON ' + s.name + '.' + o.name + ' TO ' + '[' +dpr.name + ']' FROM sys.database_permissions AS dp INNER JOIN sys.objects AS o ON dp.major_id=o.object_id INNER JOIN sys.schemas AS s ON o.schema_id = s.schema_id INNER JOIN sys.database_principals AS dpr ON dp.grantee_principal_id=dpr.principal_id WHERE dpr.name NOT IN ('public','guest') declare @count int, @max int, @query Varchar(255) set @count =1 set @max = (Select max(ID) from tempPermissions) set @query = (Select Queries from tempPermissions where ID = @count) while(@count < @max) begin exec(@query) set @count += 1 set @query = (Select Queries from tempPermissions where ID = @count) end select * from tempPermissions drop table tempPermissions

SQL - Find out database restored history

Find the history of SQL database backups.

You need to add database name where I put DBNAME in this script. It will give you full, differential and log backup history. I know how important it is in DBA's daily routine.
SELECT
CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,
msdb.dbo.backupset.database_name,
msdb.dbo.backupset.backup_start_date,
msdb.dbo.backupset.backup_finish_date,
CASE msdb..backupset.type
WHEN 'D' THEN 'Database'
WHEN 'L' THEN 'Log'
END AS backup_type,
msdb.dbo.backupset.backup_size,
msdb.dbo.backupmediafamily.physical_device_name,
msdb.dbo.backupset.name AS backupset_name



FROM msdb.dbo.backupmediafamily
INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
WHERE


--(CONVERT(datetime, msdb.dbo.backupset.backup_start_date, 102) >= GETDATE() - 7)

--and
msdb.dbo.backupset.database_name = 'DBNAME'
and msdb..backupset.type = 'D'
ORDER BY
msdb.dbo.backupset.database_name,
msdb.dbo.backupset.backup_finish_date desc

Find out from SQL Management studio - Who is part of network group id?

I have script which tells you who is part of groupid. It helps to find some login issue.


declare @DomainGroupName varchar(50)

select @DomainGroupName = 'domain\GroupIDName'

exec xp_logininfo @DomainGroupName,'members'

Script User, Database and Object Permission in SQL Server 2005 and onward

Generate Create User Script on a Database

 

SELECT 'CREATE USER [' + name + '] for login [' + name + ']'

from sys.database_principals

where

 Type = 'U'

  and

name <>  'dbo'

Generate Database role permission on a Database

 

 

 

SELECT 'EXECUTE sp_AddRoleMember ''' + roles.name + ''', ''' + users.name + ''''

from sys.database_principals users

inner join sys.database_role_members link

on link.member_principal_id = users.principal_id

inner join sys.database_principals roles

on roles.principal_id = link.role_principal_id

 

 

Generate Object Level peremission on a Database

 

 

if object_id('dbo.tempPermissions') is not null

Drop table dbo.tempPermissions

Create table tempPermissions(ID int identity , Queries Varchar(255))

Insert into tempPermissions(Queries)

select 'GRANT ' + dp.permission_name collate latin1_general_cs_as

+ ' ON ' + s.name + '.' + o.name + ' TO ' + dpr.name

FROM sys.database_permissions AS dp

INNER JOIN sys.objects AS o ON dp.major_id=o.object_id

INNER JOIN sys.schemas AS s ON o.schema_id = s.schema_id

INNER JOIN sys.database_principals AS dpr ON dp.grantee_principal_id=dpr.principal_id

WHERE dpr.name NOT IN ('public','guest')

declare @count int, @max int, @query Varchar(255)

set @count =1

set @max = (Select max(ID) from tempPermissions)

set @query = (Select Queries from tempPermissions where ID = @count)

while(@count < @max)



begin
 exec(@query)

set @count += 1

set @query = (Select Queries from tempPermissions where ID = @count)



end
 select * from tempPermissions

drop table tempPermissions 

I have struggle a lot to find out those scripts but I always try to make like simple for other DBA's.
let me know if you have any questions.


How to send TEXT when sql job fails.

I was wondering how to prioritize failure jobs. DBA gets tons of emails everyday and we don't want to keep checking out emails for failing jobs. It would be lot better if we get text message when some high priority job fails. Read following

Most carriers have “SMS gateways” which take email messages from the Internet and deliver them to their customers’ cell phones as SMS text messages. The trick is that you need to know what carrier the recipient’s phone is on — it’s not enough to know their phone number. That’s because the carrier determines what the email address of the receiving phone is going to be. For example, Cingular phones’ address are all “something@cingularme.com” while Verizon phones are “something@vtext.com.”

Sound complicated? It’s not. All you really need to do is find your carrier in the list below, and then use the pattern shown there to figure out your email address. If you want to send email to a friend’s phone, just ask them which carrier they use, and off you go!

(Note: For the major carriers, I’ve included a link to a page with more detailed information on how SMS works with that carrier, how much it costs, and where you can find more information. In the list below, just click any carrier’s highlighted name to find out more.)

Alltel
[10-digit phone number]@message.alltel.com
Example: 2125551212@message.alltel.com

AT&T (formerly Cingular)
[10-digit phone number]@txt.att.net
Example: 2125551212@txt.att.net

For multimedia messages, use [10-digit-number]@mms.att.net
Example: 2125551212@mms.att.net

Boost Mobile
[10-digit phone number]@myboostmobile.com
Example: 2125551212@myboostmobile.com

Cricket Wireless
[10-digit phone number]@sms.mycricket.com
Example: 1234567890@sms.mycricket.com

For multimedia messages: [10-digit phone number]@mms.mycricket.com
Example: 1234567890@mms.mycricket.com

Nextel (now part of Sprint Nextel)
[10-digit telephone number]@messaging.nextel.com
Example: 7035551234@messaging.nextel.com

Sprint (now Sprint Nextel)
[10-digit phone number]@messaging.sprintpcs.com
Example: 2125551234@messaging.sprintpcs.com

T-Mobile
[10-digit phone number]@tmomail.net
Example: 4251234567@tmomail.net

Verizon
[10-digit phone number]@vtext.com
Example: 5552223333@vtext.com

Virgin Mobile USA
[10-digit phone number]@vmobl.com
Example: 5551234567@vmobl.com

Other U.S. and Canadian carriers:

Bell Canada: [10-digit-phone-number]@txt.bellmobility.ca

Centennial Wireless: [10-digit-phone-number]@cwemail.com

Cellular South: [10-digit-phone-number]@csouth1.com

Cincinnati Bell: [10-digit-phone-number]@gocbw.com

Metro PCS: [10-digit-phone-number]@mymetropcs.com or [10-digit-phone-number]@metropcs.sms.us

Qwest: [10-digit-phone-number]@qwestmp.com

Rogers: [10-digit-phone-number]@pcs.rogers.com

Suncom: [10-digit-phone-number]@tms.suncom.com

Telus: [10-digit-phone-number]@msg.telus.com

U.S. Cellular: [10-digit-phone-number]@email.uscc.net

thanks to http://sms411.net/how-to-send-email-to-a-phone/ for such a great article.

How to migrate MSDB SSIS packages from one server to another....

Coming soon..