Pages

Monday, December 29, 2014

Sql Azure : Way Around for Sp_depends in Sql Azure

You can use following query to find out table used in Stored procedures similar to sp_depends.

SELECT ReferencingObjectType = o1.type,
       ReferencingObject = SCHEMA_NAME(o1.schema_id)+'.'+o1.name,
       ReferencedObject = SCHEMA_NAME(o2.schema_id)+'.'+ed.referenced_entity_name,
       ReferencedObjectType = o2.type
FROM   sys.sql_expression_dependencies ed
       INNER JOIN  sys.objects o1
         ON ed.referencing_id = o1.object_id
       INNER JOIN sys.objects o2
         ON ed.referenced_id = o2.object_id
WHERE o1.type in ('P','TR','V', 'TF') and ed.referenced_entity_name like '<Table Name>'
ORDER BY ReferencingObjectType, ReferencingObject

replace <Table name>  with your actual table name which you want to search

Steps to Create a customize Role for Azure database

Steps to Create a customize Role for Azure database
Download "Azure User Management Console" tool from https://aumc.codeplex.com/

Create a Login
Use “Azure User Management Console”
EG: dbdevwebuser
Create User for login on master
Connect to master database, and execute the following:
CREATE USER dbdevwebuser FOR LOGIN dbdevwebuser WITH DEFAULT_SCHEMA=[dbo]

This has created user “dbdevwebuser” for the login “dbdevwebuser”.
Note: You can keep the login and the user name separate.

Create User for login on the database to be used by application
Connect to application database, and execute the following:
CREATE USER dbdevwebuser FOR LOGIN dbdevwebuser WITH DEFAULT_SCHEMA=[dbo]

Create Customized Role 
Connect to application database, and execute the following:
CREATE ROLE [db_executor] AUTHORIZATION [dbo]
This will create a role by the name “db_executor”

Grant Permissions to Customized Role 
Here I am trying to create a role to grant execute for all stored procedure
GRANT EXECUTE TO [db_executor]

This will grant execute permission to the role “db_executor”
Note: You can “GRANT” more permission to this role, to make it more customized.

Assign Roles to User Created
Assign different roles of your choice to the user
sp_addrolemember @rolename = 'db_executor', @membername = 'dbdevwebuser'
sp_addrolemember @rolename = 'db_datareader', @membername = 'dbdevwebuser'
sp_addrolemember @rolename = 'db_datawriter', @membername = 'dbdevwebuser'

This will assign roles of “db_executor”, “db_datawrited” and “db_datareader” to the user 'dbdevwebuser' on the application database.
NOTE:
1. These kind of roles are usually used when you want your application to execute procedures, read and write data. But do not want them to manipulate any Schema.

Testing working of the User for the Roles Assigned

This user should
1. Execute procedure
2. Select, Insert, Update, Delete from the database
3. Create Temporary tables
4. Select View
This user should not:
1. Create, ALTER, Delete Table
2. Create, ALTER, Delete Procedure
3. Create, ALTER, Delete View
4. Create, ALTER, Delete Function
5. See Content “sp_helptext” for a Procedure


How to make a database copy from production to staging server in Azure

On the same server




1.       Connect to destination server (Here Staging DB) “master” database with productions username and pwd  because we need to have same username with pwd on source and destination DB.

2.       Execute the following command:
a.       CREATE DATABASE [NEWDATABASENAME] AS COPY OF [SOURCE_SERVERNAME].[SOURCEDATASE]
b.      IMP: Replace the NEWDATABASENAME, SOURCE_SERVERNAME and SOURCEDATASE with correct names
To track progress you can run following command or you can connect to Azure portal and check the progress in database tab

3.       Execute: Select *  from sys.dm_database_copies, this will give you the stats of the % of database copied

How to rename a SQL Azure database?

1. Connect to your Database
2. Select Master Database
3. Type following Query

Syntax:
alter database <old-database-name> modify name = <new-database-name>

Description:
<old-database-name> is your old DB name
<new-database-name> is your new DB name
Note: Don't use single or double quotes around DB Name

Example:
alter database MyStudent modify name = StudentMaster


Your DB name is changed :)

Thursday, April 10, 2014

Error Logging using Log4Net for azure and normal applications


Following are good links for error logging using Log4Net

For Normal Applications:
http://geekswithblogs.net/MarkPearl/archive/2012/01/30/log4net-basics-with-a-console-application-c.aspx

For Azure Applications:
http://www.kongsli.net/nblog/2011/08/15/log4net-writing-log-entries-to-azure-table-storage/

Wednesday, April 9, 2014

Azure Active Directory integration in asp.net MVC

Azure Active Directory integration in asp.net MVC

Pre- requisite:
1. Visual Studio 2012 Professional or Visual Studio 2012 Ultimate
2. Identity and Access Tools for Visual Studio 2012
3. Azure Subscription

Step 1:
 You need to create Active directory in your Azure subscription. You need to register your application in azure subscription.
 Active directory is called as tenant. Your users from your active directory(tenant) are able to login to your application.
 other users are not able to login. To make other users access available then you need to implement step 3 as well.
Details explained in following link.
 http://msdn.microsoft.com/en-us/library/windowsazure/dn151790.aspx

Step 2:
You can achieve following:
 1. Getting full user details
 2. Creating and Updating Users
 3. Getting a list of groups
 4. Updating group membership
Details explained in following link.
 http://msdn.microsoft.com/en-us/library/windowsazure/dn151791.aspx

Step 3:
 If you want to allow access to other user from your organization. then you need to implement multiple tenant(active directory) in your application.
 So other user will be able to login to your application.
Details explained in following link.
 http://msdn.microsoft.com/en-us/library/windowsazure/dn151789.aspx 

Monday, April 7, 2014

Azure: Building and Packaging Virtual Applications within Azure Projects

Check following links for proper deployment. Which deploys published code instead of complete code solution.

http://kellyhpdx.wordpress.com/2012/04/11/deploying-multiple-web-applications-to-a-single-azure-instance-and-applying-web-config-transforms-correctly/


http://blogs.msdn.com/b/davidhardin/archive/2013/01/18/building-and-packaging-virtual-applications-within-azure-projects.aspx