Administration

Hi All
In Oracle Administration we will be having the users with normal and administrative privileges.
These privileges will be maintained in pre or user defined roles. Dba grants These roles to the users.

My doubt is in Sqlserver how to maintain the security . Plz help me
I know that in sqlserver also we will be having users,roles and schemas. How to apply these things
Waiting for reply
Sri

Login Name

Login identifiers (Ids) are associated with users when they connect to Microsoft? SQL Server? 2000. Login IDs are the accounts that control access to the SQL Server system. A user cannot connect to SQL Server without first specifying a valid login ID. Members of the sysadmin fixed server role define login IDs.

Purpose : It is used to connect the SQL server.

sp_addlogin (sql server authentication)

sp_grantlogin (NT authentication)

User Name

A user identifier (ID) identifies a user within a database. All permissions and ownership of objects in the database are controlled by the user account. User accounts are specific to a database; the xyz user account in the sales database is different from the xyz user account in the inventory database, even though both accounts have the same ID. User IDs are defined by members of the db_owner fixed database role.

A login ID by itself does not give a user permissions to access objects in any databases. A login ID must be associated with a user ID in each database before anyone connecting with that login ID can access objects in the databases. If a login ID has not been explicitly associated with any user ID in a database, it is associated with the guest user ID. If a database has no guest user account, a login cannot access the database unless it has been associated with a valid user account.

When a user ID is defined, it is associated with a login ID. For example, a member of the db_owner role can associate the Microsoft? Windows? 2000 login NETDOMAIN\Joe with user ID abc in the sales database and user ID def in the employee database. The default is for the login ID and user ID to be the same.

Purpose : If specific login wants to access on specific database they should be added into the database.

sp_grantdbaccess

Schema Name

It is inside the database; specific to Table and View permission aginst to the User(not login). It will describe the permission(s) on the Table and View. In otherword it is inline permission statement while creating Tables and Views.

Purpose: You can grant permission while creating (or just before creating) the Table or View. Here there is no specific order where the grant statement should appear(ie, you can first give the permission before creating table or view).

CREATE SCHEMA AUTHORIZATION

So, the Top level is Login name, to connect the SQL Server, User Name is the access permission to the Database, Schema Names are inline access permissions while creating the database. Database objects are named using UserName.ObjectName. UserName and LoginName may not be identical. ie., you can change the UserName for any login name. By default the Username and LoginName are same. (ex. DBO is username of SA loginname)

|||Thank u for u r valuable reply
Baba