Apr 24, 2014

Default schema is not changing from dbo event that setting default of schema sql server user

Sometime we may face problem that default schema setting is not working. We changed the default schema of user but it is still pointing to dbo.

Most common reason is user role. That is user to which you have assigned you schema belongs to sysadmin.
And any users with sysadmin role ignore the schema setting.
Solution:
Remove the user from sysadmin role. In sql server management studio (SSMS) you can get it from:

Security - > <YourUserName> -> Properties - > Server roles

If this not reason then you must be assigning default schema to user in correctly. This script can help you:

USE [master]
GO

CREATE LOGIN [Ritesh]
WITH PASSWORD=N'yourpassword',
DEFAULT_DATABASE=[ExactHelp],
CHECK_EXPIRATION=OFF,
CHECK_POLICY=OFF

USE [ExactHelp]
GO

CREATE USER [Ritesh]
FOR LOGIN [Ritesh]

ALTER USER [Ritesh]
WITH DEFAULT_SCHEMA=[rk]

EXEC sp_addrolemember N'db_owner', N'Ritesh'

How to set default schema of user using sql server management studio (SSMS):

Step 1: General Tab, Create a new user.

 a. Login Name: Write any user name.
 b. Password: Write any password
 c. Database Default: Choose your default database.


Step 2: Server roles Tab

 a. Choose public role
 b. Remove sysadmin role if it has already selected.


Step 3: User Mapping Tab:


a. Map: Select your database. In this example it is ExactHelp.
b. User: It should be user name which we are creating.
c. Default Schema: Chose your default schema. In this case it rk


Now click on OK button. Now log in to sql server as a new user. I hope it will fix this issue. If you have any other question put your note in the comment section.   

No comments:

Post a Comment