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] | ||
| + | === 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 | ||
=== Run a command on each table in a database === | === Run a command on each table in a database === | ||
Revision as of 19:06, 7 August 2011
Contents
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
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.
