SQL CHECK Constraint in SQL Server

SQL CHECK Constraint

The CHECK constraint is used to limit the value range that can be placed in a column.
If you define a CHECK constraint on a single column it allows only certain values for this column.
If you define a CHECK constraint on a table it can limit the values in certain columns based on values in other columns in the row.

---

SQL CHECK Constraint on CREATE TABLE

The following SQL creates a CHECK constraint on the "P_Id" column when the "Persons" table is created. The CHECK constraint specifies that the column "P_Id" must only include integers greater than 0.
MySQL:

CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CHECK (P_Id>0)
)

SQL Server / Oracle / MS Access:

CREATE TABLE Persons
(
P_Id int NOT NULL CHECK (P_Id>0),
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)

To allow naming of a CHECK constraint, and for defining a CHECK constraint on multiple columns, use the following SQL syntax:
MySQL / SQL Server / Oracle / MS Access:

CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CONSTRAINT chk_Person CHECK (P_Id>0 AND City='Sandnes')
)

---

SQL CHECK Constraint on ALTER TABLE

To create a CHECK constraint on the "P_Id" column when the table is already created, use the following SQL:
MySQL / SQL Server / Oracle / MS Access:

ALTER TABLE Persons
ADD CHECK (P_Id>0)

To allow naming of a CHECK constraint, and for defining a CHECK constraint on multiple columns, use the following SQL syntax:
MySQL / SQL Server / Oracle / MS Access:

ALTER TABLE Persons
ADD CONSTRAINT chk_Person CHECK (P_Id>0 AND City='Sandnes')

---

To DROP a CHECK Constraint

To drop a CHECK constraint, use the following SQL:
SQL Server / Oracle / MS Access:

ALTER TABLE Persons
DROP CONSTRAINT chk_Person

Duplicate removal in Table

T-SQL:

with a
as
(
select * from ( select row_number() over(partition by emp_id order by emp_id)[dup],* from Employee_Test)[samp]
)
delete from a where [dup]>1-------->to remove
--select * from a where [dup]>1----> to see 

Use database inside stored procedure

 Use database inside stored procedure

I am thinking about an important funcitonality or property in sql server.
Is it possible to give the database name dynamically in sql server?
OOps, i think we can achieve this using dynamic sql.
I have made a try on this. Lets see,
use master
GO
declare @sql nvarchar(max)
select @sql = 'use'
select @sql = @sql + ' ' + 'Northwind'
select * from dbo.Employees -- Northwind table
exec sp_executesql @sql
--------------------------------------------------------
Its not working. I have tried to analyse this problem, its because once the query gets executed its coming out of that particular scope and enter into the public scope.

The work around for this problem is, we need to

use tempdb
go
declare @sql nvarchar(1000)
declare @dbname varchar(40)
select @dbname = 'Northwind'
select db_name() --tempdb
select @sql = 'use'
select @sql = @sql + ' '+@dbname +' select * from dbo.Employees
select db_name()'
exec sp_executesql @sql --northwind
select db_name() --tempdb

A network-related or instance-specific error occurred while establishing a connection to SQL Server.

Error: A network-related or instance-specific error occurred while establishing a connection to SQL Server.

Error: A network-related or instance-specific error occurred while establishing a connection to SQL Server.



The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections.


(provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)

Solution:

http://blog.sqlauthority.com/2009/05/21/sql-server-fix-error-provider-named-pipes-provider-error-40-could-not-open-a-connection-to-sql-server-microsoft-sql-server-error/

1. The issue may be due to firewall.
2. SQL browser service is not running
3. SQL Server is not configured to allow remote connection
4. Check whether tcp/ip protocol is enabled for communication in sql server configuration manager -> Client and server protocols
5. Check tcp/ip protocols are enabled and active.
6. check whether you sql server logins are properly created and provided necessary level of authentication.

Create windows authentication user in SQL Server

Creating/Configuring Windows authetication in SQL Server:

I got a peculiar query in one of the SQL forum and I wish to write a detailed article on this. In SQL Server, there are 3 types of authentication to enter into your database.

1. Windows authentication. (Will take the in built windows account as an authentication)
2. SQL Server authenticaiton (We need to specify the username and password to enter into the server)
3. Mixed mode authentication (Combination of windows and SQL authentication)

Goto Server Explorer -> Security -> Login ->New Login

Click on the Search button and a window will popup to search for the available users in the machine. In the below screenshot, my machine name is venkat and santhi is an administrator in my machine. I am trying to create windows authentication for this user.

Select the "Server Roles", am trying to make this use as sysadmin.

Select the database to which the user needs proper access.

On clicking "OK" the user is created.

The xp_cmdshell proxy account information cannot be retrieved or is invalid. Verify that the '##xp_cmdshell_proxy_account##' credential exists and contains valid information Resolved issues

The xp_cmdshell proxy account information cannot be retrieved or is invalid. Verify that the '##xp_cmdshell_proxy_account##' credential exists and contains valid information

In one of our recent migrations, we got the following error when the client tried to fire xp_cmdshell system stored procedure through some client code.

Msg 15153, Level 16, State 1, Procedure xp_cmdshell, Line 1
The xp_cmdshell proxy account information cannot be retrieved or is invalid. Verify that the '##xp_cmdshell_proxy_account##' credential exists and contains valid information.

When xp_cmdshell is called by a user that is not a member of the sysadmin fixed server role, xp_cmdshell connects to Windows by using the account name and password stored in the credential named ##xp_cmdshell_proxy_account##. If this proxy credential does not exist, xp_cmdshell will fail.

The proxy account credential can be created by executing sp_xp_cmdshell_proxy_account. As arguments, this stored procedure takes a Windows user name and password. For example, the following command creates a proxy credential for Windows domain user SHIPPING\KobeR that has the Windows password sdfh%dkc93vcMt0.

So this is what we did:

1	EXEC sp_xp_cmdshell_proxy_account 'HOCBASE\admin', 'account_password';
2	GO

The effect:

And things worked fine.

 A few more things:

How do you drop the proxy credential?

1	EXEC sp_xp_cmdshell_proxy_account NULL;
2	GO

How can you find all the users (except sysadmins) who can execute or have access to xp_cmdshell?

1	Use master
2	EXEC sp_helprotect 'xp_cmdshell'

And who are all the sysadmins on the box?

1	Use master
2	EXEC sp_helpsrvrolemember 'sysadmin'

To see list of Login used objects or login Dependent objects like SPs,views,tables,udf in SQL

To see Login used objects like SPs,views,tables,udf in SQL:

Select [UserName],[UserType],[PermissionType],[ObjectType],[ObjectName] from 
(
SELECT  
    [UserName] = CASE princ.[type] 
                    WHEN 'S' THEN princ.[name]
                    WHEN 'U' THEN ulogin.[name] COLLATE Latin1_General_CI_AI
                 END,
    [UserType] = CASE princ.[type]
                    WHEN 'S' THEN 'SQL User'
                    WHEN 'U' THEN 'Windows User'
                 END,  
    [DatabaseUserName] = princ.[name],       
    [Role] = null,      
    [PermissionType] = perm.[permission_name],       
    [PermissionState] = perm.[state_desc],       
    [ObjectType] = obj.type_desc,--perm.[class_desc],       
    [ObjectName] = OBJECT_NAME(perm.major_id),
    [ColumnName] = col.[name]
FROM    
    --database user
    sys.database_principals princ  
LEFT JOIN
    --Login accounts
    sys.login_token ulogin on princ.[sid] = ulogin.[sid]
LEFT JOIN        
    --Permissions
    sys.database_permissions perm ON perm.[grantee_principal_id] = princ.[principal_id]
LEFT JOIN
    --Table columns
    sys.columns col ON col.[object_id] = perm.major_id 
                    AND col.[column_id] = perm.[minor_id]
LEFT JOIN
    sys.objects obj ON perm.[major_id] = obj.[object_id]
WHERE 
    princ.[type] in ('S','U')
UNION
--List all access provisioned to a sql user or windows user/group through a database or application role
SELECT  
    [UserName] = CASE memberprinc.[type] 
                    WHEN 'S' THEN memberprinc.[name]
                    WHEN 'U' THEN ulogin.[name] COLLATE Latin1_General_CI_AI
                 END,
    [UserType] = CASE memberprinc.[type]
                    WHEN 'S' THEN 'SQL User'
                    WHEN 'U' THEN 'Windows User'
                 END, 
    [DatabaseUserName] = memberprinc.[name],   
    [Role] = roleprinc.[name],      
    [PermissionType] = perm.[permission_name],       
    [PermissionState] = perm.[state_desc],       
    [ObjectType] = obj.type_desc,--perm.[class_desc],   
    [ObjectName] = OBJECT_NAME(perm.major_id),
    [ColumnName] = col.[name]
FROM    
    --Role/member associations
    sys.database_role_members members
JOIN
    --Roles
    sys.database_principals roleprinc ON roleprinc.[principal_id] = members.[role_principal_id]
JOIN
    --Role members (database users)
    sys.database_principals memberprinc ON memberprinc.[principal_id] = members.[member_principal_id]
LEFT JOIN
    --Login accounts
    sys.login_token ulogin on memberprinc.[sid] = ulogin.[sid]
LEFT JOIN        
    --Permissions
    sys.database_permissions perm ON perm.[grantee_principal_id] = roleprinc.[principal_id]
LEFT JOIN
    --Table columns
    sys.columns col on col.[object_id] = perm.major_id 
                    AND col.[column_id] = perm.[minor_id]
LEFT JOIN
    sys.objects obj ON perm.[major_id] = obj.[object_id]
UNION
--List all access provisioned to the public role, which everyone gets by default
SELECT  
    [UserName] = '{All Users}',
    [UserType] = '{All Users}', 
    [DatabaseUserName] = '{All Users}',       
    [Role] = roleprinc.[name],      
    [PermissionType] = perm.[permission_name],       
    [PermissionState] = perm.[state_desc],       
    [ObjectType] = obj.type_desc,--perm.[class_desc],  
    [ObjectName] = OBJECT_NAME(perm.major_id),
    [ColumnName] = col.[name]
FROM    
    --Roles
    sys.database_principals roleprinc
LEFT JOIN        
    --Role permissions
    sys.database_permissions perm ON perm.[grantee_principal_id] = roleprinc.[principal_id]
LEFT JOIN
    --Table columns
    sys.columns col on col.[object_id] = perm.major_id 
                    AND col.[column_id] = perm.[minor_id]                   
JOIN 
    --All objects   
    sys.objects obj ON obj.[object_id] = perm.[major_id]
WHERE
    --Only roles
    --roleprinc.[type] = 'R' AND
    --Only public role
    --roleprinc.[name] = 'username' AND
    --Only objects of ours, not the MS objects
    obj.is_ms_shipped = 0

)as a
where 1=1 
and [ObjectName] in ('spname')
and a.[UserName] = 'username'

How to solve orphaned users in SQL & How to fix Orphan User in absense of login

Understanding and dealing with orphaned users in a SQL Server database

Refer:

http://www.mssqltips.com/sqlservertip/1590/understanding-and-dealing-with-orphaned-users-in-a-sql-server-database/

Attaching and restoring databases from one server instance to another are common tasks executed by a DBA. After attaching or restoring of a database, previously created and configured logins in that database do not provide access. The most common symptoms of this problem are that the application may face login failed errors or you may get a message like the user already exists in the current database when you try to add the login to the database. This is a common scenario when performing an attach or a restore, so how do you resolve this?

Solution

When a database is moved from one server to another server the login ids stored in the master database do not align with the login ids stored in each user database. As mentioned above some of the errors you may see after attaching or restoring a database include:

Msg 229, Level 14, State 1
%s permission denied on object %.*s, database %.*s, owner %.*s

or

Microsoft SQL-DMO (ODBC SQLState: 42000) Error 15023: User or role '%s' already exists in the current database.

Without proper understanding and appropriate planning you may face this problem. You could delete and recreate the user, but you will loose all of the configured permissions. So a proper linking mechanism is required, so that permissions are retained.

Some more possible error messages that you may see include

Before getting to the solution for this problem, it would be better to have a glance at the backend issue. SQL Server logins, which are stored in the master database are mapped against individual databases. SQL Server logins access individual databases using a database user that is mapped to the appropriate SQL Server login. There are two exceptions, the guest account and Microsoft Windows group memberships. The SQL Server 2005 logins on a server instance are visible in the sys.server_principals system catalog view and the sys.syslogins view. For SQL Server 2000 you may get SQL Server login information in sysxlogins table.

On the other hand the mapping information to a database user is stored within the database in the system tablesysusers. It includes the name of the database user and the SID of the corresponding SQL Server login. The permissions of this database user are used for authorization purposes in the database.

So we can say that every time we create a SQL Server login, it will be seen in the sys.server_principals system catalog view or sys.syslogins views on SQL Server 2005 or in the sysxlogins table for SQL Server 2000. The entries inthe sysusers table of a database are linked to SQL Server logins shown in the above picture. This link is created through a column named SID.

If we move our database to another SQL Server instance through any process, the new server might or might not have the same logins and the SIDs of these logins would probably be different from the SIDs of these logins in the original server. What this means is that, the sysusers table in the moved database has SIDs that are not matched with the login info in the master database on this new server. Therefore we get orphaned users.

As an example, I have created and configured four users with permissions in the AdventureWorks database. The users are TestUser1, TestUser2, TestUser3 and TestUser4. When I restored the backup of this database onto another SQL Server 2005 instance, although the users were present in the AdventureWorks database and the logins existed on the new server, but none of these logins had access to the newly restored database.

So keeping this scenario in mind, let us run some queries to see the difference between the SQL Server login SIDs (if SQL Server login is present) and database user SID for TestUser3.

--Script to view difference in SID

USE MASTER
GO 
SELECT name as SQLServerLogIn,SID as SQLServerSID FROM sys.syslogins
WHERE [name] = 'TestUser3'
GO

USE AdventureWorks
GO 
SELECT name DataBaseID,SID as DatabaseSID FROM sysusers
WHERE [name] = 'TestUser3'
GO

The result below shows the SID of the SQL Server login and that of the user database id differ and this is what causes the problem.

Now that we have a better understanding of the problem, it is time to get to some useful commands for analysis and a solution.

I have restored the AdventureWorks database from one instance to another with the four above users. Now to analyze how many orphaned users there are in my restored database, I will run the following T-SQL command which produces a listing of all the orphaned users and in our case all four users are orphaned.

--Command to generate list of orphaned users

USE adventureWorks
GO

sp_change_users_login @Action='Report'
GO

Now that we have the list of the orphaned users we can begin to fix the problem. To overcome this problem, you need to link the SIDs of the users (from sysusers) to the SIDs of the valid logins in the master database. The following command remaps the server login account specified by TestUser1 with the database user specified by TestUser1.

--Command to map an orphaned user

USE AdventureWorks
GO

sp_change_users_login @Action='update_one', 
@UserNamePattern='TestUser1', 
@LoginName='TestUser1'
GO

Or if you are sure that SQL Server login name is the same as the orphaned user of database being mapped, then you may use a shorter command such as this for TestUser2..

--Command to map an orphaned user

EXEC sp_change_users_login 'Auto_Fix', 'TestUser2'
GO

Both commands will map the users to the logins and they will be not orphaned any longer.

If a login name does not exists, you would have to create it first before doing the mapping. A quick way to do this is to use the following command which will create the login and then map the login to the user.

--Command to map an orphaned user to a login that is not present but will be created

EXEC sp_change_users_login 'Auto_Fix', 'TestUser3', null,'pwd'
GO

Summarizing the T-SQL Used

In the above process, the stored procedure sp_change_users_login is used. The variable [ @Action ] specifies the exact use of this stored procedure. It accepts a parameter as varchar(10) and can have one of the following values:

• If parameter is Auto_Fix, database user is mapped with same named SQL Server login. It may also create login, if not present.
• If parameter is Report, it lists the orphaned users and their security identifiers (SID).
• If parameter is Update_One, it links the specified database user to an existing SQL Server login.

Some considerations

• sp_change_users_login requires membership in the db_owner fixed database role. Only members of the sysadmin fixed server role can specify the Auto_Fix option.
• When the orphaned user is mapped, the SID in the master database is allotted to orphaned user, so every time a DB is attached or restored the SID will differ between SQL Server login and database user.
• If you have different Server login names mapped to a database user then do not use the command with Auto_Fixfor linking
• A user may become orphaned also if the corresponding SQL Server login is dropped
• Although obvious, but it is good to mention, after re-linking the password of SQL Server login can be used by the database user.

How to fix Orphan User in absense of login

Error
Msg 15291, Level 16, State 1, Procedure sp_change_users_login, Line 131
Terminating this procedure. The Login name ‘DJ’ is absent or invalid
What is Orphaned User?
An Orphaned User in SQL Server is a database user for which a valid SQL Server Login is not available or it is wrongly defined with the different SID in the SQL Server instance, thereby not allowing the user to get connect to the database to perform activities.
Below scenarios are mostly responsible for Orphan Users
1. A SQL Server Login was accidentally dropped
2. A database is restored with a copy of database from another SQL Server Instance
3. SID of the login is different in sys.server_principals and sys.sysusers
Steps to re-produce issue
Step 1: Creating database SQLDBPool

USE [master]
GO
CREATE DATABASE [sqldbpool] ON PRIMARY
( NAME = N’sqldbpool’, FILENAME = N’C:\sqldbpool.mdf’ , SIZE = 2048KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
LOG ON
( NAME = N’sqldbpool_log’, FILENAME = N’C:\sqldbpool_log.ldf’ , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
COLLATE SQL_Latin1_General_CP1_CI_AS
GO
Step 2:
In this example I am creating user DJ using below script. You can also create user from GUI as well
Using GUI

Right Click On Security Node and Select New Login

Using Script

USE [master]
GO
CREATE LOGIN [DJ] WITH PASSWORD=N'pune@123',
DEFAULT_DATABASE=[master],
CHECK_EXPIRATION=OFF,
CHECK_POLICY=OFF
GO

Step 3: I am assigning Data Reader and writer permission to Login DJ to database SQLDBPool

USE [sqldbpool]
GO
CREATE USER [DJ] FOR LOGIN [DJ]
GO
USE [sqldbpool]
GO
EXEC sp_addrolemember N'db_datawriter', N'DJ'
GO
USE [sqldbpool]
GO
EXEC sp_addrolemember N'db_denydatareader', N'DJ'
GO

Step 4: Dropping the login DJ and it will make the user DJ as orphan in SQLDBPool database

USE [master]
GO
DROP LOGIN [DJ]
GO

Steps to fix the issue
Step 1: You can use SP_CHANGE_USERS_LOGIN stored procedure to fix the Orphan user issue, for that you need the Database Owner permission on that database.
Syntax
sp_change_users_login [ @Action= ] 'action'
[ , [ @UserNamePattern= ] 'user' ]
[ , [ @LoginName= ] 'login' ]
[ , [ @Password= ] 'password' ]
[;]
Examples

EXEC sp_change_users_login 'Report' --Reports orphaned user
EXEC sp_change_users_login 'Auto_Fix', 'user' -- auto fix when SID mis-match issue
EXEC sp_change_users_login 'Auto_Fix', 'user', 'login', 'password'

The stored procedure SP_CHANGE_USERS_LOGIN accepts arguments AUTO_FIX, REPORT or UPDATE_ONE as @Action parameter.
AUTO_FIX: If this value is used for @Action parameter, it will create a SQL Server Login if it was not present earlier and will synchronize the SQL Server Login with that of the Database User.We have to also provide the password here.
REPORT: It will display the list of all the Orphaned Users along with the SID (Security Identifiers) value within the current database which are not linked to a SQL Server Login.
UPDATE_ONE:will synchronize the specified database user with an existing SQL Server Login.
Step 2: As first step I am checking orphan user using Report parameter, it will show me the SID and orphan user name.

List of Orphaned User

Step 3: In this scenario we have dropped the SQL Login so it is required to create the login. We can create the login using below query specifying password and mapped it to orphan user

EXEC sp_change_users_login 'Auto_Fix', 'DJ', NULL, 'pune@123'

Step 4: Executing again the report query to list out orphan users.
EXEC sp_change_users_login 'Report'
Use below link to list out and fix the Orphan User issue for all the databases.
http://sqldbpool.com/2010/03/27/script-to-fix-and-list-out-orphan-users-on-all-the-databases-on-server/

Query to find SP name and Table name and object name

Query to find SP name and Table name and object name


SELECT OBJECT_NAME(OBJECT_ID),
definition
FROM sys.sql_modules
WHERE definition LIKE '%' + 'cid' + '%'



SELECT DISTINCT OBJECT_NAME(OBJECT_ID),
object_definition(OBJECT_ID)
FROM sys.Procedures
WHERE object_definition(OBJECT_ID) LIKE '%' + 'CId' + '%'



SELECT obj.Name SPName, sc.TEXT SPText
FROM sys.syscomments sc
INNER JOIN sys.objects obj ON sc.Id = obj.OBJECT_ID
WHERE sc.TEXT LIKE '%' + 'CId' + '%'
AND TYPE = 'P'



SELECT table_catalog,table_name,column_name,data_type,column_default
FROM information_schema.columns

WHERE column_name LIKE '%' + 'CId' + '%'

To know SQL Permissions and IDENTITY Tables with row count in SQL

To know SQL Permissions and IDENTITY Tables with row count in SQL

Script:

To know SQL Permissions

SELECT USER_NAME(P.GRANTEE_PRINCIPAL_ID) AS PRINCIPAL_NAME,

DP.PRINCIPAL_ID,

DP.TYPE_DESC AS PRINCIPAL_TYPE_DESC,

P.CLASS_DESC,

OBJECT_NAME(P.MAJOR_ID) AS OBJECT_NAME,

P.PERMISSION_NAME,

P.STATE_DESC AS PERMISSION_STATE_DESC

FROM SYS.DATABASE_PERMISSIONS P

INNER JOIN SYS.DATABASE_PRINCIPALS DP

ON P.GRANTEE_PRINCIPAL_ID = DP.PRINCIPAL_ID

--WHERE P.STATE_DESC = 'DENY'

where OBJECT_NAME(P.MAJOR_ID) like '%SPname%'

To know IDENTITY Tables with row count in SQL


SELECT b.TableName,b.ColumnName,b.OriginalSeed,b.Step,b.LastValue,a.[RowCount] from 

(
SELECT
      QUOTENAME(SCHEMA_NAME(sOBJ.schema_id)) + '.' + QUOTENAME(sOBJ.name) AS [TableName],sOBJ.object_id
      , SUM(sPTN.Rows) AS [RowCount]
FROM 
      sys.objects AS sOBJ
      INNER JOIN sys.partitions AS sPTN
            ON sOBJ.object_id = sPTN.object_id
WHERE
      sOBJ.type = 'U'
      AND sOBJ.is_ms_shipped = 0x0
      AND index_id < 2 -- 0:Heap, 1:Clustered
GROUP BY 
      sOBJ.schema_id
      , sOBJ.name,sOBJ.object_id

)as a
join
(
SELECT TableName = OBJECT_NAME(OBJECT_ID) ,
       ColumnName = name ,
       OriginalSeed = seed_value ,
       Step = increment_value ,
       LastValue = last_value ,
       IsNotForReplication = is_not_for_replication,object_id
FROM sys.identity_columns
)as b

Command line parameters are invalid in SQL JOBS

Command line parameters are invalid in SQL JOBS

Solution:

Actually Reason behind this error is login information not properly given or not allocate SSIS packages to job.

Once u choose Server Name corrrect and give username and password and select appropriate Package

Then u can get Success while you run job

The server principal "login" is not able to access the database "Database" under the current security context Resolved

The server principal "login" is not able to access the database "Database" under the current security context Resolved:

The Above Error should be get in our SQL server due to following scenarios

If the user have objects like SP,Views,UDF etc to Execute permission in only restricted Database but that SP code include user doesnt have Execute permission database.

Sample.

Below user have only Testdb Execute permission only  like user mapping see below:

but User have dont have permission on FoodManagement db see below

But user code include insert code in foodmanagement db see below

So when user execute SP he will get error like see below:

The server principal "User" is not able to access the database "FoodManagement" under the current security context.

To Avoid/Resolve:

Kindly map the missing FoodManagement db to that user then he can execute that sp without any error

How to assign Database Role/Permission on New Login Creation?

How to assign Database Role/Permission on New Login Creation?

While you create new login in SQL 2005 you should see the following roles available

So first step is right click under security tab on logins like see below

now u can give name and password like see below

and now under user mapping tab kindly assign as u require database and select schema like dbo and choose Important roles like below

db_datareader-------only select values of object like tables,views

db_datawriter--------only can update/modify on any objects  in SQL server      

After successfully created new login user can login now as see below

If user have db_datareader permission then cant able to modify records but can see all tables values

 like see below bu

So If u want user should modify values then u have to assign db_datawriter permission like see below

and now user can able to update/modify records see below

That is it.

Now if user want to Execute SPs he could get following error

to sort out this issue sysadmin role user like DBA shold give execute permission to that SP as see below

Right click the stored procedures tab under programmbility tab and choose properties like see below

and now add button to choose role and assign like see below

Restore Database Error while DB in use or MDF not found Error

Exclusive access could not be obtained because the database is in use  Resolved

Tweet

Sometimes this is a common error message that we encounter, when we try to restore a SQL database, which is being used by other users.

This can occur due to various reasons. But the most common incident is, users not closing the Management Studio’s query window after they have finished the query task.

There are few ways of resolving this and restore the database.

1. Find all the active connections, kill them all and restore the database.
2. Get database to offline (And this will close all the opened connections to this database), bring it back to online and restore the database.

Method 1

Use the following script to find and kill all the opened connections to the database before restoring database.

 Collapse | Copy Code

declare @sql as varchar(20), @spid as int
select @spid = min(spid)  from master..sysprocesses  where dbid = db_id('<database_name>') 
and spid != @@spid    

while (@spid is not null)
begin
    print 'Killing process ' + cast(@spid as varchar) + ' ...'
    set @sql = 'kill ' + cast(@spid as varchar)
    exec (@sql)

    select 
        @spid = min(spid)  
    from 
        master..sysprocesses  
    where 
        dbid = db_id('<database_name>') 
        and spid != @@spid
end 

print 'Process completed...'

Method 2

Use the following code to take database offline and bring back to online so that all the active connections will be closed. And afterwards restore the database.

 Collapse | Copy Code

alter database database_name<br>set offline with rollback immediate
alter database database_name
set online
go

Method 3Setting the database to single user mode and later, setting to multi user will also close all the active connections. And this method is faster than the 'Method 2'.

use master
go
alter database <dbname>
set single_user with rollback immediate
go
alter database <dbname>
set multi_user
go

DB MDF or LDF not found Error:

When You Create new db and choose .bak file of source db and click 

 Restore DB Source db following error came as see below:

step by step see below:

To resolve this error:

Click options tab and click ellipses tab see below and choose already created mdf and ldf via