Tag Archives: SQL Server

Query to fetch table sizes and row counts quickly

Here’s a query that comes in handy when checking the size of your tables within a SQL Server Database:

SELECT st.name AS TableName,
	(SELECT SUM(max_length) FROM sys.columns WHERE object_id=st.object_id) AS RowLength,
	si.rowcnt AS [RowCount],
	CAST(si.rowcnt*(SELECT SUM(max_length) FROM sys.columns WHERE object_id=st.object_id)/1024.0/1024.0 AS INT) AS MaxTableSizeMB
FROM sys.tables as st
LEFT JOIN sysindexes si on st.object_id=si.id and si.indid < 2
ORDER BY st.name

SQL Server login with read access on every database

Here’s a quick script to add a new SQL server login with read access to every database on the server:

USE [master]
GO
 
CREATE LOGIN [AndreasReader] WITH PASSWORD=N'AndreasPasswordOhlalala12345'
GO
 
SELECT 
'USE ['+ name +']' + CHAR(13) + CHAR(10) + 'GO' + CHAR(13) + CHAR(10) + 
'EXEC sp_adduser [AndreasReader]' + CHAR(13) + CHAR(10) + 'GO ' + CHAR(13) + CHAR(10) + 
'EXEC sp_addrolemember ''db_datareader'',''AndreasReader''' + CHAR(13) + CHAR(10) + 'GO' + CHAR(13) + CHAR(10) + 
'EXEC sp_addrolemember ''db_denydatawriter'',''AndreasReader''' + CHAR(13) + CHAR(10) + 'GO' + CHAR(13) + CHAR(10)
FROM sys.databases

For newer versions of SQL Server, use this:

USE [master]
GO
 
CREATE LOGIN [AndreasReader] WITH PASSWORD=N'AndreasPasswordOhlalala12345'
GO
 
SELECT 
'USE ['+ name +']' + CHAR(13) + CHAR(10) + 'GO' + CHAR(13) + CHAR(10) + 
'CREATE USER [AndreasReader]' + CHAR(13) + CHAR(10) + 'GO ' + CHAR(13) + CHAR(10) + 
'ALTER ROLE [db_datareader] ADD MEMBER [AndreasReader]' + CHAR(13) + CHAR(10) + 'GO' + CHAR(13) + CHAR(10) + 
'ALTER ROLE [db_denydatawriter] ADD MEMBER [AndreasReader]' + CHAR(13) + CHAR(10) + 'GO' + CHAR(13) + CHAR(10)
FROM sys.databases

Be sure to also add to the “model” database. That way you will also have read access to newly created databases.

Cannot access reporting services (Denali CTP3)

This is a problem I’ve encountered many times. I do a fresh install of SQL Server, and when accessing Reporting Manager I get the error message:

“User ‘Domain\User’ does not have required permissions. Verify that sufficient permissions have been granted and Windows User Account Control (UAC) restrictions have been addressed.”

This is caused by the UAC, and is usually fixed by running IE as an administrator. However, this didn’t work for me today on my fresh install of SQL Server Denali CTP3.

What DID work, was adding another user to the amdinistrators-group, and then running IE as administrator. The weird thing is that I didn’t need to run it in the context of the new user, and my user was a member of the administrators-group the whole time.