Loginless users in SQL Server 2005+

MSDN

When you create a user without a login, there is no path that allows a login to be mapped to that particular user in the database.  The only way to "utilize" this particular user in the database is through impersonation.  Again this is BY DESIGN.  Loginless users were added in SQL Server 2005 as a replacement to application roles.  The main reasons behind this were that you did not need to embed a password into an application and you also did not suddenly have multiple people coming into your SQL Server all with the same "login".  By utilizing loginless users, the user's of an application login to the SQL Server using THEIR login credentials (NOT a generic login), so that you can audit activity to a given login.  Once they change context to the application database, you can issue an EXECUTE AS command to change their user context thereby giving them the authority they need within the application.

Testing execute as ->

create user deep without login;

USE DEEPTEST;

GO

CREATE PROCEDURE dbo.usp_Demo

WITH EXECUTE AS 'deep'

AS

SELECT user_name();

GO

USE DEEPTEST;

exec usp_Demo ->  deep
SELECT user_name(); -> dbo

List of users in Business Objects

Author: Adrián BO_Worker developed from Julien Bras' userlist utility.
Author notes: Excel 97-2003 VBA application. Displays some lists for users, groups, universes, reports... Only reading, not writing back. Please go to configuration sheet and fill Server, Username and Password in order to get it working. After that try the rest of sheets pressing the buttons to field the data below. Whenever you get orange cells that's a place you should fill. If you get Id and Name to fill you can write one or the other. In names you usually can use % character. Some queries can take a long time (10-15 minutes) depending on your server and number of objects involved.

I hope it's useful for someone and want to thank Julien Bras for previous programming. 

---

http://www.forumtopics.com/busobj/viewtopic.php?t=118177

BOXIR3_Lists97.zip
Description:		Download
Filename:	BOXIR3_Lists97.zip
Filesize:	105.64 KB
Downloaded:	6275 Time(s)

Database snapshot in SQL server 2008

-- CREATE A EMPTY DATABASE

USE [master];

GO

CREATE DATABASE DEEPTEST;

GO

--CREATE A DATABASE SNPSHOT-1

CREATE DATABASE SNPDEEPDB ON

(Name ='DEEPTEST',

FileName='D:\DB_SnapShot\DEEPTEST.SNP')

AS SNAPSHOT OF DEEPTEST;

GO

--CREATE A DATABASE SNPSHOT-2

CREATE DATABASE SNPDEEPDB2 ON

(Name ='DEEPTEST',

FileName='D:\DB_SnapShot\DEEPTEST2.SNP')

AS SNAPSHOT OF DEEPTEST;

GO

--NOW WE HAVE TWO SNPASHOTS AND LETS TRY TO RESTORE ORIGNAL ONE FROM SNPSHOT2

USE [master]

GO

RESTORE DATABASE DEEPTEST

FROM DATABASE_SNAPSHOT = 'SNPDEEPDB';

GO

--You get the error becuase we can only restore from a snapshot if that snapshot is the only existing snapshot of the database while we have two

--so lets try to drop one of snpshot and try again

--Msg 3137, Level 16, State 4, Line 1

--Database cannot be reverted. Either the primary or the snapshot names are improperly specified, all other snapshots have not been dropped, or there are missing files.

--Msg 3013, Level 16, State 1, Line 1

--RESTORE DATABASE is terminating abnormally.

DROP DATABASE SNPDEEPDB2

--Command(s) completed successfully.

USE [master]

GO

RESTORE DATABASE DEEPTEST

FROM DATABASE_SNAPSHOT = 'SNPDEEPDB';

GO

--Command(s) completed successfully.

--Lets add a new datafile and filegroup to database now

USE [master]

GO

ALTER DATABASE [DEEPTEST] ADD FILEGROUP [SECONDARY]

GO

ALTER DATABASE [DEEPTEST]

ADD FILE

(

    NAME = DEEPTEST3,

    FILENAME = 'D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\DEEPTEST3.NDF',

    SIZE = 5MB,

    MAXSIZE = 100MB,

    FILEGROWTH = 5MB

)

TO FILEGROUP SECONDARY

--Now we have database with multiple files

SELECT SDF.NAME AS [FILENAME],SDF.PHYSICAL_NAME AS 'PHYSICAL NAME',

SIZE/128 AS [SIZE_IN_MB], FG.NAME AS [FILE_GROUP_NAME]

FROM SYS.DATABASE_FILES SDF INNER JOIN SYS.FILEGROUPS FG ON SDF.DATA_SPACE_ID=FG.DATA_SPACE_ID

FILENAME	PHYSICAL NAME	SIZE_IN_MB	FILE_GROUP_NAME
DEEPTEST	D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\DEEPTEST.mdf	1	PRIMARY
DEEPTEST2	D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\DEEPTEST2.NDF	5	PRIMARY
DEEPTEST3	D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\DEEPTEST3.NDF	5	SECONDARY

--Lets try to create a snpshot again and we get the error

--CREATE A DATABASE SNPSHOT-3

CREATE DATABASE SNPDEEPDB3 ON

(Name ='DEEPTEST3',

FileName='D:\DB_SnapShot\DEEPTEST3.SNP')

AS SNAPSHOT OF DEEPTEST;

GO

--Msg 5127, Level 16, State 1, Line 2

--All files must be specified for database snapshot creation. Missing the file "DEEPTEST".

--Msg 5127, Level 16, State 1, Line 1

--All files must be specified for database snapshot creation. Missing the file "DEEPTEST".

-- So We will have to We have to specify files for each of them

CREATE DATABASE DEEPTEST3 ON

(Name = 'DEEPTEST', FileName='D:\DB_SnapShot\DEEPTEST31.SNP'),

(Name = 'DEEPTEST2',Filename='D:\DB_SnapShot\DEEPTEST32.SNP'),

(Name = 'DEEPTEST3',Filename='D:\DB_SnapShot\DEEPTEST33.SNP')

AS SNAPSHOT OF DEEPTEST

--Command(s) completed successfully.

Lets take a datafile offline and  run a snpshot now

ALTER DATABASE DEEPTEST MODIFY FILE (NAME = 'DEEPTEST3', OFFLINE)

NAME	STATE
DEEPTEST	ONLINE
DEEPTEST_log	ONLINE
DEEPTEST2	ONLINE
DEEPTEST3	OFFLINE

CREATE DATABASE DEEPTEST4 ON

(Name = 'DEEPTEST', FileName='D:\DB_SnapShot\DEEPTEST41.SNP'),

(Name = 'DEEPTEST2',Filename='D:\DB_SnapShot\DEEPTEST42.SNP'),

(Name = 'DEEPTEST3',Filename='D:\DB_SnapShot\DEEPTEST43.SNP')

AS SNAPSHOT OF DEEPTEST

--Msg 5114, Level 16, State 1, Line 1

--Log files, offline files, restoring files, and defunct files for database snapshots should not be specified. "DEEPTEST3" is not an --eligible file for a database snapshot.

--here we will have to exclude the offlile datafile while taking snpshot.

CREATE DATABASE DEEPTEST4 ON

(Name = 'DEEPTEST', FileName='D:\DB_SnapShot\DEEPTEST41.SNP'),

(Name = 'DEEPTEST2',Filename='D:\DB_SnapShot\DEEPTEST42.SNP')

--(Name = 'DEEPTEST3',Filename='D:\DB_SnapShot\DEEPTEST43.SNP')

AS SNAPSHOT OF DEEPTEST

--Command(s) completed successfully.

Cross DB Ownership Chaining in SQL Server

From Microsoft MSDN -

Use the Cross DB Ownership Chaining option to configure cross-database ownership chaining for the instance of SQL Server. This security enhancement was added in SQL Server 2000 SP3 to allow you to manage cross-database security.

This server option allows you to control cross-database ownership chaining at the database level or to allow cross-database ownership chaining for all databases:

• When Cross DB Ownership Chaining is off (0) for the instance, cross-database ownership chaining is off. You can turn on cross-database ownership chaining for individual databases using sp_dboption.
  By default all user databases have cross-database ownership chaining turned off. Cross-database ownership chaining is on for the master, tempdb, and msdb system databases. You cannot change cross-database ownership chaining for the master, msdb, model, and tempdb system databases.
• When Cross DB Ownership Chaining is on (1) for the instance, cross-database ownership chaining is on for all databases. This is equivalent to pre-SP3 functionality.
  Setting this option to 1 is not recommended unless all of the databases hosted by the instance of SQL Server must participate in cross-database ownership chaining and you are aware of the security implications of this setting. For more information, see Using Ownership Chains.

Controlling Cross-Database Ownership Chaining

Before turning cross-database ownership chaining on or off, consider the following:

• You must be a member of the sysadmin role to turn cross-database ownership chaining on or off.
  
• Before turning off cross-database ownership chaining on a production server, fully test all applications, including third-party applications, to ensure that the changes do not affect application functionality.
  
• You can change the Cross DB Ownership Chaining option while the server is running if you specify RECONFIGURE with sp_configure.
  
• If you have databases that require cross-database ownership chaining, the recommended practice is to set the Cross DB Ownership Chaining option to 0 and to turn on cross-database ownership for any database that must participate in cross-database ownership chaining.
  For example, if Cross DB Ownership Chaining is currently set to 1 (allowed for all databases), you can run the following statements to turn off cross-database ownership chaining for all user databases, and then turn it on for the Northwind and Pubs database:

  USE master
  EXEC sp_configure 'Cross DB Ownership Chaining', '0'; RECONFIGURE
  EXEC sp_dboption 'Northwind', 'db chaining', 'ON'
  EXEC sp_dboption 'Pubs', 'db chaining', 'ON'

BCP Utility in SQL Server

The bcp utility bulk copies data between an instance of Microsoft SQL Server and a data file in a user-specified format. The bcp utility can be used to import large numbers of new rows into SQL Server tables or to export data out of tables into data files. Except when used with the queryout option, the utility requires no knowledge of Transact-SQL. To import data into a table, you must either use a format file created for that table or understand the structure of the table and the types of data that are valid for its columns.

to know more please refer to this link - http://msdn.microsoft.com/en-us/library/ms162802.aspx