Rename SQL server DB - tables

Sol1 : just click on the table in Object explorer, hit F2 and rename it

Sol 2 :

EXECUTE sp_rename 'oldTableName' , 'newTableName'

EXECUTE sp_rename 'tb1' , 'tb2'

It really work try it.

Modifying SQL Server Tables

 Adding Columns to a Table

Syntax : ALTER TABLE table-name ADD column-specification
Ex : ALTER TABLE empolyee ADD JobDescription VARCHAR(500)

 Modifying Existing Columns


ALTER TABLE employee ALTER COLUMN EmpName VARCHAR(30) NOT NULL



ALTER TABLE employee ALTER COLUMN EmpName VARCHAR(30) NOT NULL



ALTER TABLE employee ALTER COLUMN address2 VARCHAR(200) NULL

 Deleting Columns


ALTER TABLE empolyee DROP COLUMN address3






   

How to recover SA password on Microsoft SQL Server 2008 R2

http://v-consult.be/2011/05/26/recover-sa-password-microsoft-sql-server-2008-r2/

Pull Replication

http://www.sqlservercentral.com/articles/Stairway+Series/72322/

Rename a SQL Server database

 Note: If not done. Change user type muti-user to single user

This command works for SQL Server 2005, 2008, 2008R2 and 2012:

ALTER DATABASE oldName MODIFY NAME = newName

If you are using SQL Server 2000 you can use this T-SQL command to make the database name change.  This still works for SQL 2005, 2008, 2008R2 and 2012, but Microsoft says it will be phased out at some time.

EXEC sp_renamedb 'oldName', 'newName'
 

tsql : Split row based on row number , ROW_NUMBER(),RANK(),

select * from  (select
 F=cast((RowNo/100) as int)+1,F1,F2 from (
               SELECT ROW_NUMBER() OVER(ORDER BY f2) RowNo,F1,F2 FROM TempTable where F2 IS NOT NULL
               ) sub1) AS T WHERE T.F=1

Drop All Stored procedures,views and functions - GET Objects from sys.objects

 GET Objects from sys.objects
--select *from sys.objects where type in('P','V','FN') order by type


--Query for stored procedure
select ('DROP PROCEDURE ' + NAME),*from sys.objects where type in('P') order by type

--Query for view
select ('DROP VIEW' + NAME),*from sys.objects where type in('V') order by type

--Query for FUNCTIONS
select ('DROP FUNCTION' + NAME),*from sys.objects where type in('FN') order by type

Fixed the error: 15138 The database principal owns a schema in the database, and cannot be dropped. - Drop the USER sql server 2008 r2

Error: 15138 The database principal owns a schema in the database, and cannot be dropped.

Cause: That means, you are trying to drop a user owning a schema. In order to drop the user, you have to find the schema that’s assigned and then transfer the ownership to another user/role or to drop it.

Resolution: You can fix the issue following two ways.

Step 1 :
By script: You can find out which schema is owned by this user with the query below:

SELECT name FROM sys.schemas WHERE principal_id = USER_ID('myUser')
Step 2 :
Then, use the names found from the above query below in place of the SchemaName below. And drop your user.

ALTER AUTHORIZATION ON SCHEMA::SchemaName TO dbo

GO
Step 3 :
DROP USER myUser

Find Stored Procedures and Functions to Refer any Tables

Hi,
Just replace the "search" in the code with the name of the table/view/stored procedure/function etc that you want to search on


select        distinct
            ObjectType        = o.type_desc
        ,    ObjectName        = o.name
        ,    CodeSequence    = c_display.colid
        ,    Code            = c_display.[text]
from        sys.objects o
inner join    sys.syscomments c_search
            on c_search.id = o.[object_id]
inner join    sys.syscomments c_display
            on c_display.id = o.[object_id]
where        o.type_desc not in ('INTERNAL_TABLE','SERVICE_QUEUE','SYSTEM_TABLE')
and            c_search.[text] like '%search%'
order by    1, 2, 3

SQL SERVER – FIX : Error: 18486 Login failed for user ‘sa’ because the account is currently locked out.

msg: 18486
Login failed for user ‘sa’ because the account is currently locked out. The system administrator can unlock it.


Fix/Solution/Workaround:

1) Disable the policy on your system or on your domain level. However, this may not be the most appropriate option as it will adversely affect your security protection level.

2) If this is a one-time issue, enable “sa” login WITH changing password of “sa” login.

ALTER LOGIN sa WITH PASSWORD = 'yourpass' UNLOCK ;
GO

3) If this is a one-time issue, enable “sa” login WITHOUT changing password of “sa” login.

ALTER LOGIN sa WITH CHECK_POLICY = OFF;
ALTER LOGIN sa WITH CHECK_POLICY = ON;
GO

 For Eg:

ALTER LOGIN devuser WITH CHECK_POLICY = OFF;

ALTER LOGIN devuser WITH PASSWORD = 'Dev#123' UNLOCK ;

SQL Login

Database servers are the most important server in the company as they contains the critical and important information of company that’s why database security plays an important role in DBA’s career

Database servers are very crucial that’s why we have to give proper permissions and access to the user. To enter into the SQL Server you must have a login if SQL Server login has not been set up, authentication fails and the user receives an error. The user is granted access to the server after his login has been authenticated. Database security then validates the user’s permissions by checking the database user accounts on the server.

Here we are going to learn the types of access and permission that we have to give to the user as per the database security best practices.

There two types of login from which you can access the database

Windows Login

            Permissions are inherited from windows 

SQL Login
              You have to create a native SQL Login and have to give a proper permission to that user on  database.

Login Creation for SQL Server 2005

Click Start > Programs > Microsoft SQL Server 2005 > SQL Server Management Studio.
In the Registered Servers panel, expand the Database Engine tree. 

Expand the Security folder in the Object Explorer pane. Right-click the Logins folder and click New Login.

Provide a new login name in the Login Name field.

Select SQL Server Authentication and provide the Password.

Change the Default Database to the one you want the login name assigned to.

Leave the Default Language set to default.

Select Server Roles in the Select a page pane and verify that all Server roles are not selected.

Select User Mapping in the Select a page pane. In the Users mapped to this login field, select the databases you want the user assigned to.

In the Default schema column, specify the user name for the default schema. The user name for the schema must be the same as the user login name.

In the database role membership pane, verify that only public and db-owner are selected.

Click OK.

Syntax for Windows Login

CREATE LOGIN [SQL\Sqlindex] FROM WINDOWS

WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]

Syntax for SQL Login

create login Sqlindex with password ='*********',

DEFAULT_DATABASE=[master],

DEFAULT_LANGUAGE=[us_english],

CHECK_EXPIRATION=ON, CHECK_POLICY=ON

EXEC sys.sp_addsrvrolemember 'Sqlindex','dbcreator'

Alter Login Syntax

      ALTER LOGIN login ENABLE

      ALTER LOGIN login DISABLE

     ALTER LOGIN login WITH option [ ,... ] 

options:  

    PASSWORD = 'password' 

    [ 

      OLD_PASSWORD = 'oldpassword'| password_option[password_option] 

    ]

    DEFAULT_DATABASE = database

    DEFAULT_LANGUAGE = language

    NAME = login

    CHECK_POLICY = {ON | OFF}

    CHECK_EXPIRATION = {ON | OFF}

    CREDENTIAL = credential

    NO CREDENTIAL

Example

To enable the login

alter login sqlindex enable

To disable Login

alter login sqlindex disable

To change the password

alter login sqlindex with password='Sqlindex'

To change the default database of login.

alter login sqlindex with DEFAULT_DATABASE=[Dbsqlindex]

DROP LOGIN

Remove a SQL Server login account (User).

Syntax

drop login Sqlindex