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..

How to script sql server jobs and migrate to another server ....

1. First of all open SSMS and connect to your sql server from where you want to copy jobs.

2. Then click on View on menu bar and then select Object Explorer Details

3.  Select all jobs from Object Explorer Details Windows
4.  Right Click on selected jobs and then select Script Job As -> Create To -> New Query window

5. Now copy script from new query window and then paste it to new server.

6. Before executing scripts you need to make sure followings.

1. Make sure email operator are exist on new server.
2. Make sure SSIS packages are exist on new server.
3. Make sure Security Credentials are created on new server.
4. Make sure users are present on new server as well.

7. Execute script on new server and now you have migrated jobs in new server.

Let me know if you have any comment.

SQL 2012 - Contained Database

A contained database is a database that is isolated from other databases and from the instance of SQL Server that hosts the database. SQL Server 2012 helps user to isolate their database from the instance in 4 ways.

• Much of the metadata that describes a database is maintained in the database. (In addition to, or instead of, maintaining metadata in the master database.)
• All metadata are defined using the same collation.
• User authentication can be performed by the database, reducing the databases dependency on the logins of the instance of SQL Server.

Requirements of contained databases.

• It is required to enable contained databases on the instance.

• The contained database needs to be added to the connection string or specified when connecting via SQL Server Management Studio

Partial containment is enabled using the CREATE DATABASE and ALTER DATABASE statements or by using SQL Server Management Studio. 

How to configure contained database:

EXEC SP_CONFIGURE 'show advanced options',1

GO

RECONFIGURE

GO

EXEC SP_CONFIGURE 'contained database authentication',1

GO

RECONFIGURE

GO

USE [master]

GO

CREATE DATABASE [ContainedDB] CONTAINMENT = PARTIAL

Found some disadvantages of Contained Database:

Connection strings

Connection strings to a contained database must explicitly specify the database in the connection string. You can no longer rely on the login's default database to establish a connection; if you don't specify a database, SQL Server is not going to step through all the contained databases and try to find any database where your credentials may match.

---

Cross-db queries

Even if you create the same user with the same password in two different contained databases on the same server, your application will not be able to perform cross-database queries. The usernames and passwords may be the same, but they're not the same user. The reason for this? If you have contained databases on a hosted server, you shouldn't be prevented from having the same contained user as someone else who happens to be using the same hosted server. When full containment arrives (likely in the version after SQL Server 2012), cross-database queries will absolutely be prohibited anyway. I highly, highly, highly recommend that you don't create server-level logins with the same name as contained database users, and try to avoid creating the same contained user name in multiple contained databases.

---

Synonyms

Most 3- and 4-part names are easy to identify, and appear in a DMV. However if you create a synonym that points to a 3- or 4-part name, these do not show up in the DMV. So if you make heavy use of synonyms, it is possible that you will miss some external dependencies, and this can cause problems at the point where you migrate the database to a different server. I complained about this issue, but it was closed as "by design." (Note that the DMV will also miss 3- and 4-part names that are constructed via dynamic SQL.)

---

Password policy

If you have created a contained database user on a system without a password policy in place, you may find it hard to create the same user on a different system that does have a password policy in place. This is because the CREATE USER syntax does not support bypassing the password policy. I filed a bug about this problem, and it remains open. And it seems strange to me that on a system with a password policy in place, you can create a server-level login that easily bypasses the policy, but you can't create a database user that does so - even though this user is inherently less of a security risk.

---

Collation

Since we can no longer rely on the collation of tempdb, you may need to change any code that currently uses explicit collation or DATABASE_DEFAULT to use CATALOG_DEFAULT instead. See this BOL article for some potential issues.

---

IntelliSense

If you connect to a contained database as a contained user, SSMS will not fully support IntelliSense. You'll get basic underlining for syntax errors, but no auto-complete lists or tooltips and all the fun stuff. I filed a bug about this issue, and it remains open.

---

SQL Server Data Tools

If you are planning to use SSDT for database development, there currently isn't full support for contained databases. Which really just means that building the project won't fail if you use some feature or syntax that breaks containment, since SSDT currently doesn't know what containment is and what might break it.

---

ALTER DATABASE

When running an ALTER DATABASE command from within the context of a contained database, rRather than ALTER DATABASE foo you will need to use ALTER DATABASE CURRENT - this is so that if the database is moved, renamed, etc. these commands don't need to know anything about their external context or reference.

---

A few others

Some things you probably shouldn't still be using but nonetheless should be mentioned in the list of things that aren't supported or are deprecated and shouldn't be used in contained databases:

• numbered procedures
• temporary procedures
• collation changes in bound objects
• change data capture
• change tracking
• replication

SQL Server - Interview Questions and Answers . (Updated in 2018)

SQL Server DBA interview questions and answers..

Lets help other dba to find their dream job.



•  Tell me about your self. - Most of the time, this will be the first questions in your interview.

•  What is a page in sql server? -  The fundamental unit of data storage in SQL Server is the page. The disk space allocated to a data file (.mdf or .ndf) in a database is logically divided into pages numbered contiguously from 0 to n. Disk I/O operations are performed at the page level. That is, SQL Server reads or writes whole data pages. In SQL Server, the page size is 8 KB. This means SQL Server databases have 128 pages per megabyte. Each page begins with a 96-byte header that is used to store system information about the page. This information includes the page number, page type, the amount of free space on the page, and the allocation unit ID of the object that owns the page. Data rows are put on the page serially, starting immediately after the header. A row offset table starts at the end of the page, and each row offset table contains one entry for each row on the page. Each entry records how far the first byte of the row is from the start of the page. The entries in the row offset table are in reverse sequence from the sequence of the rows on the page.
  

• 
  What is an extent in sql server? - Extents are a collection of eight physically contiguous pages and are used to efficiently manage the pages. All pages are stored in extents. Extents are the basic unit in which space is managed. An extent is eight physically contiguous pages, or 64 KB. This means SQL Server databases have 16 extents per megabyte.
  To make its space allocation efficient, SQL Server does not allocate whole extents to tables with small amounts of data. SQL Server has two types of extents:
  
  Uniform extents are owned by a single object; all eight pages in the extent can only be used by the owning object.
  Mixed extents are shared by up to eight objects. Each of the eight pages in the extent can be owned by a different object. A new table or index is generally allocated pages from mixed extents. When the table or index grows to the point that it has eight pages, it then switches to use uniform extents for subsequent allocations. If you create an index on an existing table that has enough rows to generate eight pages in the index, all allocations to the index are in uniform extents.
  
  
  
  
  What is the relation between page and extent? - read above answer.
  How many services are installed when you install sql server first time? - SQL SERVICES, SQL AGENT SERVICE, SQL BROWSER SERVICE, SQL SERVER ACTIVE DIRECTORY HELP, SQL SERVER VSS WRITER.
  
  
  
  What doesn SQL Browser Service do? - The SQL Server Browser program runs as a Windows service. SQL Server Browser listens for incoming requests for Microsoft SQL Server resources and provides information about SQL Server instances installed on the computer. SQL Server Browser contributes to the following actions:
  
  Browsing a list of available servers
  Connecting to the correct server instance
  Connecting to dedicated administrator connection (DAC) endpoints
  What types of indexes are available in SQL Server? Cluster Index and Non Cluster Index.
  What is the difference between Cluster and Non-Clustered index?
  Why do you need Non-Clustered Index?
  What is covering index? Why do you need Covering Index?
  What is Composite Index? Why do we need it?
  Difference between Composite Index and Covering Index.
  What was your backup strategies?
  What is the difference between Full backup vs Differential backup?
  What is tail-log backup? When do we use tail-log backup?
  What is the difference between rebuild index and reorg index?
  When do we rebuild index and reorg index?
  What is sql statistics and what kind of information it contains?
  When do you need to update statistics?
  How do you find out statistics is not updated?
  Which DMV finds the missing indexes?
  Is it good idea to add all recommended missing indexes?
  How do you find out Indexes are fragmented? How do you fix this issue?
  How do you find out the length of the column?
  How do you find out BLOB object column length?
  What is your DR strategies?
  What is the difference between Mirroring and LogShipping?
  What types of mirroring are available?
  What is the difference between Asynchronous and synchronous mirroring?
  How do you setup LogShipping in your environment?
  Have you ever worked with replication? What types of replications are available?
  How do you setup replication if database is really big?
  How does replication works? exp: Transactional Replication.
  How do you solve latency issue in sql server?
  Tell us about some DBCC commands you use in daily work.
  Tell us about some DMV commands you use in daily work.
  Have you ever worked on clustered environment? What is the difference between active and passive clustered?
  How big was your clustered environment?
  What is the difference between 2005 and 2008 clustered?
  Can you install sql server from remote machine in cluster?
  Have you ever add SAN in clustered? if Yes, what are the steps to add SAN in clustered?
  Have you ever add or move Quorum drive? What are the steps to do it?
  What are the steps to upgrade SQL on clustered environment?
  What is minimum requirements before installing SQL on Cluster environment?
  What is Index Padding and Fill Factor?
  What would you do if you want to update statistics and db is really huge?
  What is Sample rows or percentage in Update Statistics?
  Have you ever worked with reporting service? Explain your role in that.
  How to migrate reports from one server to another server?
  How to find out error in reporting server? - Reporting service has their own log file.
  Have you ever worked with SSIS? explain your role in that.
  How many ways you can deploy SSIS packages?
  What is the difference between File System and SQL Server deployment?
  How to migrate packages from File System to File system and MSDB to MSDB?
  If you are doing replication of one database and if you do rebuild index on primary server does secondary server will follow same?
  How to move one table from one disk to another disk when there is a space issue on database and you don't have any other data files?
  How to add data or log file when database is in Mirroring, Log-Shipping or AlwaysOn?
  How to start SQL when WSFC is offline? https://www.mssqltips.com/sqlservertip/4917/recover-wsfc-using-forced-quorum-for-sql-server-alwayson-availability-group/
  How to upgrade SQL Server when database is in AlwaysOn?
  How to troubleshoot AlwaysOn performance or not synchronizing issue?
  Which SQL 2016 features you have been using in your current environment?
  What is the difference in SQL 2012 and 2014/2016 AlwaysOn Availibility Group?
  What is the difference between SQL 2012 and SQL 2016 installation?
  What is SQL Server R Service?

Using Distributed Replay to load test your SQL Server–Part 2

After we have configured the Distributed Replay as explained in the previous post, we can use the Distributed Replay environment to create the load on the SQL Server.

Data Collection from Source Server

We need to collect some data from the main production server and then we can use the collected data to create load on the new SQL Server. To do this, follow the steps below.

1. Create “C:\DRDemo” folder on the controller (which is ServerN1 in our test.)
2. Open SQL Server 2012 Profiler Trace utility on ServerN1
3. Select File –> New Trace –> Connect to ServerN1. Note that we are using ServerN1 to simulate the workload as well for this test. In the real world we would be capturing the trace from a server which is currently in production.
4. Select the TSQL – Replay template within “Use the template” drop down menu and run the trace.
5. Run “C:\DRDemo\StartWorkload.cmd” to bring some load to SQL Server 2008 R2 system. The system is not production so the script is used to create some load on the production.
6. After the script is completed, save the trace file within “C:\DRDemo”. For the example it is “C:\DRDemo\ServerN1_SQL2008R2_Trace.trc”.
7. Now the trace from source server is ready.

Preprocess the source Trace file

The source trace file will be used to prepare files which are used by distributed clients to create load on the new SQL Server. Run the command below using command prompt on the controller (ServerN1) to prepare the files for the distributed clients.

c:\DRDemo>dreplay preprocess -i "C:\DRDemo\ServerN1_SQL2008R2_Trace.trc" -d "C:\DRPreProcess"

The screenshot above shows that an error occurred. The important part is “…and that the console user has the proper permissions to access the controller service”
The login account is sqladmin on the server. It can be checked using “whoamI” command using the command prompt on ServerN1. The system event log on ServerN1 says that

Description:
The application-specific permission settings do not grant Local Activation permission for the COM Server application with CLSID {6DF8CB71-153B-4C66-8FC4-E59301B8011B} and APPID {961AD749-64E9-4BD5-BCC8-ECE8BA0E241F} to the user AYSQLTEST\sqladmin SID (S-1-5-21-2826735731-136765897-3671058344-1125) from address LocalHost (Using LRPC). This security permission can be modified using the Component Services administrative tool.

To fix this issue, we need to the steps below on ServerN1:

1. ServerN1 - Run and type dcomcnfg and Component Services will be opened.
2. Find DReplayController (Console Root –> Component Services –> Computers –> My Computer – >DCOM Config -> DReplayController)
3. Open the properties of DReplayController and select Security tab
4. Edit “Launch and Activation Permissions” and grant “sqladmin” domain user account “Local Activation” and “Remote Activation” permissions.
5. Edit “Access Permissions” and grant “sqladmin” domain user account “Local Access” and “Remote Access”.
6. Restart controller and client services like below
   NET STOP "SQL Server Distributed Replay Controller"
   NET STOP "SQL Server Distributed Replay Client"
   NET START "SQL Server Distributed Replay Controller"
   NET START "SQL Server Distributed Replay Client"
7. Run the command again:

dreplay preprocess -i "C:\DRDemo\ServerN1_SQL2008R2_Trace.trc" -d "C:\DRPreProcess"

BTW, these steps might seem familiar – we had used them in the first post for the service account itself. Once the permission issue is fixed, the preprocess command generated 2 files within “C:\DRPreProcess” folder.

Replay against SQL Server 2012 using clients

Preprocess phase has been completed. Controller will take 2 files “ReplayEvents.irf”, “TraceStats.xml” and copy them to the clients (ServerN1,ServerN3) and replay them on the clients against target server which is SQL Server 2012 called ServerN2\SQL2012. Here is the command to do so:

dreplay replay -s ServerN2\sql2012rc0 -w ServerN1,ServerN3 -f 10 -o -d "C:\DRPreProcess"

The -o parameter will save the trace output within “C:\Program Files (x86)\Microsoft SQL Server\110\Tools\DReplayClient\ResultDir”
At the same time open a profiler trace and connect to target server which is ServerN2\SQL2012 collect profiler trace. Then you can compare the trace file from source server and trace file from target server and check some query performance. You can refer to this article for more details.
That’s it! Hopefully we have covered the basics of Distributed Replay and how you can use it to reproduce a workload on a test system. Please leave your feedback and questions in the comments area below!