Difference between revisions of "SQL Server"
| Line 1: | Line 1: | ||
* [http://msdn.microsoft.com/en-us/library/aa302392.aspx Building Secure ASP.NET Applications: Authentication, Authorization, and Secure Communication] | * [http://msdn.microsoft.com/en-us/library/aa302392.aspx Building Secure ASP.NET Applications: Authentication, Authorization, and Secure Communication] | ||
| + | |||
| + | === Count database connections in database === | ||
| + | SELECT DB_NAME(sP.dbid) AS the_database | ||
| + | , COUNT(sP.spid) AS total_database_connections | ||
| + | FROM sys.sysprocesses sP | ||
| + | GROUP BY DB_NAME(sP.dbid) | ||
| + | ORDER BY 1; | ||
=== List permissions each table in a database === | === List permissions each table in a database === | ||
Revision as of 20:41, 1 May 2017
Contents
Count database connections in database
SELECT DB_NAME(sP.dbid) AS the_database
, COUNT(sP.spid) AS total_database_connections
FROM sys.sysprocesses sP
GROUP BY DB_NAME(sP.dbid)
ORDER BY 1;
List permissions each table in a database
Use the sp_helprotect system stored proc. Run with no arguments it list permissions on all database objects
List all tables in a database
Use databasename SELECT * from sys.tables
Run a command on each table in a database
Use the sp_msforeachtable undocumented system stored proc, for example:
sp_msforeachTable @command1='select count(*) NR from ? where CreatedBy = ''kurzejar'' or ModifiedBy = ''kurzejar'' '
To restrict tables to those in the dbo schema, use the @whereAnd parameter:
sp_msforeachTable @command1='select count(*) NR from ? where CreatedBy = ''kurzejar'' or ModifiedBy = ''kurzejar'' ' , @whereAnd = 'and uid = 1'
SQL 2008 - Change sysadmin password on local database
Pre-requisite: Make sure your Domain login is a member of the PC’s local admin group.
1) Stop SQL Server (either from Management Studio or Services)
2) Open a cmd prompt at your sql installation folder (C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Binn) and run: sqlserv.exe –m to put it into single user mode. Leave the command window open.
3) Open another cmd prompt (pathed) and run sqlcmd –E –S .
CREATE LOGIN [BEACON\myUsername] FROM WINDOWS WITH DEFAULT_DATABASE=[master] EXEC master..sp_addsrvrolemember @loginame = N'BEACON\myUsername', @rolename = N'sysadmin'
4) Close the both command windows and restart the SQL Service
Open SS Management Studio, expand server roles and check you are in the sysadmin role.
