Fetch the row count for all tables in a MS SQL SERVER database

The following SQL will get you the row count of all tables in a database:

   CREATE TABLE #counts(table_name varchar(255),row_count int)

EXEC sp_MSForEachTable @command1='INSERT #counts (table_name, row_count) SELECT ''?'', COUNT(*) FROM ?'
SELECT table_name, row_count FROM #counts ORDER BY table_name, row_count DESC
DROP TABLE #counts

The output will be a list of tables and their row counts.

If you just want the total row count across the whole database, appending

SELECT SUM(row_count) AS total_row_count FROM #counts

MS SQL Express - CREATE DATABASE using permission denied user account in SQL Server

Steps:

1.  shut down SQL Server from services

2.  open cmd window (as administrator) and run single-user mode as local admin with this command:

"c:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\Binn\sqlservr.exe" -m -s SQLEXPRESS

3.  open another cmd window (as administrator)

4.  open sqlcmd:

sqlcmd -S .\SQLEXPRESS

Now add the sysadmin user:

a.  sp_addsrvrolemember 'domain\user', 'sysadmin'

b.  GO

5.  now Ctrl+C the single-user mode from the first cmd window to kill SQL Server.  Now restart it from services the normal way. 

6.Exit cmd window, Log into Management Studio and the user you created should be listed under logins with the credential of "sysadmin."

(or)

Another way to  add the current user to the sysadmin role in SQL Server follow

 addselftosqlsysadmin.cmd