Category: Databases

Articles related to databases.

Posted on

Setting Up ASP.NET Membership Tables

My hosting provider is deprecating this information because there are newer methods of setting up logins and handling authentication, however, I believe this information is still invaluable to those who haven’t adopted the newer methods and/or can’t keep up with the pace of all the technological changes in the world (or don’t want to).  And in the interest of preserving knowledge, here it is:

The following steps create the full Application Services database schema on our SQL Server database:

Open the command prompt on your local computer, and navigate to:
C:\WINDOWS\Microsoft.NET\Framework\[framework version]

Execute the command:
aspnet_regsql.exe -S DBServerName -U DBLogin -P DBPassword -A all -d DBName

Currently, there is no management interface to manage the membership database other than using Visual Web Developer or creating your own application using the membership provider class. Here is an example of how to configure Visual Studio or Visual Web Developer to manage the membership database:

1. Create a web application in Visual Web Developer or Visual Studio.
2. Open the web.config file.
3. The default membership provider uses a connection string called “LocalSqlServer”.  Therefore, replace:

<connectionstrings></connectionstrings>

with:

<connectionstrings>
<remove name="LocalSqlServer"></remove>
<add name="LocalSqlServer" connectionString="Data Source=DBServerName;Integrated Security=false;Initial Catalog=DBName;User ID=DBLogin;Password=DBPassword" providerName="System.Data.SqlClient"></add>
</connectionstrings>

4. Save and close the web.config file.
5. Go to Website menu and run the ASP.NET Configuration tool. This will open the Web Site Administration tool.
6. In the Web Site Administration tool, go to the Security tab.
7. Click on “Select authentication type”.
8. Select “From the internet”. Click the Done button.
9. Create your admin roles and users.
10. Then create access rules.
11. Create a rule that applies to the “Anonymous users” with “Deny” permissions.
12. Create another rule that applies to the admin role you created with “Allow” permissions.

Your application is now ready to use the membership provider, and you can begin creating your log in forms.

Posted on

Easy Way to GRANT/REVOKE Rights to Tables within a Microsoft SQL Server Database

Yesterday, a customer asked for help setting up an additional database user with only SELECT rights to his database.  He had a lot of tables, so the easiest solution I came up with involved using the sp_MSforeachtable system stored procedure.  Here’s the T-SQL code:

EXEC sp_MSforeachtable
'REVOKE ALL ON ? FROM username'

EXEC sp_MSforeachtable
'GRANT SELECT ON ? TO username'

Make sure you replace username with the database username that you want the rights applied to.

Posted on

Incorrect Space Usage Reporting in SQL Server

One of our customers for my company asked a great question the other day. He was wondering why after deleting/truncating a lot of data in a table, it was providing inaccurate results after executing the system stored procedure sp_spaceused on a table. He only had 1 row of data, and it couldn’t possibly be taking up 3 MB of disk space. The answer can be found on Microsoft’s Official documentation regarding sp_spaceused on the web.  The stored procedure actually gets it data from the sys.allocation_units and sys.partitions catalog views,  and according to the documentation:

When you drop or rebuild large indexes, or drop or truncate large tables, the Database Engine defers the actual page deallocations, and their associated locks, until after the transaction commits. Deferred drop operations do not release allocated space immediately. Therefore, the values returned by sp_spaceused immediately after dropping or truncating a large object may not reflect the actual disk space available.

Fortunately, there is a simple solution to this problem.  You can update the statistics by either executing this Transact-SQL statement:

EXEC sp_spaceused @updateusage = N'TRUE';

or this one:

DBCC UPDATEUSAGE (0)

Both will update the statistics for the current database.