Create a New Db User in SQL Server 2005

Create a new db user in SQL Server 2005

CREATE LOGIN [user] WITH PASSWORD='password', 
       DEFAULT_DATABASE=[your_db], CHECK_POLICY=OFF
GO

CREATE USER [user] FOR LOGIN [user]
EXEC sp_addrolemember N'db_datareader', N'your_db'
EXEC sp_addrolemember N'db_datawriter', N'your_db'
GO

Where CHECK_POLICY=OFF switches off password complexity check, etc

New user and database on SQL Server

USE master;
GO
CREATE LOGIN mobstat WITH PASSWORD = 'mobstat123';    
GO
CREATE DATABASE mobstat COLLATE SQL_Hungarian_CP1250_CI_AS;
GO

-- You were creating a schema and a User before you have created a database
USE [mobstat]
GO
CREATE USER mobstat FOR LOGIN mobstat;
GO
EXEC sp_addrolemember N'db_owner', N'mobstat';
GO
CREATE SCHEMA [mobstat] AUTHORIZATION [mobstat]
GO

SQL Server 2005 - Create Database with permissions for another database

Like Martin said you need a new schema not a database.

CREATE SCHEMA [SchemaName] AUTHORIZATION dbo;

Create your Views under the new schema name then,

CREATE LOGIN [LoginName] {FROM WINDOWS} (If you are using an AD Group)

USE [DatabaseName];

CREATE USER [LoginName/Username]; (They can be the same)

GRANT EXECUTE, SELECT, INSERT, DELETE, VIEW DEFINITION ON Schema::[NewSchemaName] TO [LoginName/Username];

If you want to have a Role the create the role under the database and make the UserName a member of the Role and grant the permissions to the role.

How to create a new instance of Sql Server 2005

Assuming you are a member of the Windows Admininstrator group, you can put the server in Single User mode, you could try this -

http://blogs.msdn.com/raulga/archive/2007/07/12/disaster-recovery-what-to-do-when-the-sa-account-password-is-lost-in-sql-server-2005.aspx

How is it possible in SQL Server 2005 for different users having tables with same name?

I think could be diferent schemas like the Example:

create schema X
create schema Y

create table X.Test (id int)
create table Y.Test (id int)

will work normally.
The Users maybe have diferents default schema and so you can create tables with the same name.

SQLServer difference between new login and new database user

A login is a login account for the entire SQL Server instance - an instance can contain numerous databases.

A user is defined at the database level, and is associated with a login to provide interactive access (privileges providing)

Logins are a server side (instance level) objects. Their correct name is 'server principals' (see sys.server_principals). Server wide privileges are granted to logins, like create database or view server state permissions.

Users are a database objects, correctly referred to as 'database principals' (see sys.database_principals). They are the recipients of database permissions, like create table or select.

Ordinarily a login is mapped 1-to-1 to a user in each database, via a matching SID, but there are some exception, like all members of the sysadmin fixed server role are always mapped to dbo.

Users without login are a specific construct for Service Broker remote identities (see Remote Service Bindings) and for code signing. You should never have to create one in any other context, and if you do, you're likely doing it wrong. Users without login are never meant to be impersonated.

sql script to create a new database

CREATE DATABASE OBXKites
ON PRIMARY
     (NAME = 'OBXKites', FILENAME = 'D:\SQLData\OBXKites.mdf'),  
   FILEGROUP Static
     (NAME = 'OBXKitesStatic', FILENAME = 'c:\SQLData\OBXKitesStatic.ndf')
LOG ON 
   (NAME = 'OBXKitesLog',  FILENAME = 'c:\SQLData\OBXKites.ldf')
go

This code in your script should create a new database. If no error occurrs in the process.

---

Related Topics