error:Setuser failed because of one of the following reasons: the database principal 'schemaUser' does not exist,

• mssqlnoob

  Ten Centuries

  Points: 1245

  More actions

  August 31, 2013 at 5:38 am

  #297398

  with reference to http://technet.microsoft.com/en-us/library/ms191534(v=sql.100).aspx

  I copy the commands word by word

  E. Granting VIEW DEFINITION permission on an XML schema collection

  SETUSER

  GO

  USE master

  GO

  if exists( select * from sysdatabases where name='permissionsDB' )

  drop database permissionsDB

  GO

  if exists( select * from sys.sql_logins where name='schemaUser' )

  drop login schemaUser

  GO

  CREATE DATABASE permissionsDB

  GO

  CREATE LOGIN schemaUser WITH PASSWORD='Pass#123',DEFAULT_DATABASE=permissionsDB

  GO

  GRANT CONNECT SQL TO schemaUser

  GO

  USE permissionsDB

  GO

  CREATE USER schemaUser WITH DEFAULT_SCHEMA=dbo

  GO

  CREATE XML SCHEMA COLLECTION MySC AS '

  <schema xmlns="http://www.w3.org/2001/XMLSchema" targetNamespace="http://ns"

  xmlns:ns="http://ns">

  <simpleType name="ListOfIntegers">

  <list itemType="integer"/>

  </simpleType>

  <element name="root" type="ns:ListOfIntegers"/>

  <element name="gRoot" type="gMonth"/>

  </schema>

  '

  GO

  -- schemaUser cannot see the contents of the collection.

  SETUSER 'schemaUser'

  GO

  SELECT XML_SCHEMA_NAMESPACE(N'dbo',N'MySC')

  GO

  -- Grant schemaUser VIEW DEFINITION and REFERENCES permissions

  -- on the xml schema collection.

  SETUSER

  GO

  GRANT VIEW DEFINITION ON XML SCHEMA COLLECTION::dbo.MySC TO schemaUser

  GO

  GRANT REFERENCES ON XML SCHEMA COLLECTION::dbo.MySC TO schemaUser

  GO

  -- Now schemaUser can see the content of the collection.

  SETUSER 'schemaUser'

  GO

  SELECT XML_SCHEMA_NAMESPACE(N'dbo',N'MySC')

  GO

  -- Revoke schemaUser VIEW DEFINITION permissions

  -- on the xml schema collection.

  SETUSER

  GO

  REVOKE VIEW DEFINITION ON XML SCHEMA COLLECTION::dbo.MySC FROM schemaUser

  GO

  -- Now schemaUser cannot see the contents of

  -- the collection anymore.

  SETUSER 'schemaUser'

  GO

  SELECT XML_SCHEMA_NAMESPACE(N'dbo',N'MySC')

  GO

  every thing is working until

  SETUSER

  GO

  REVOKE VIEW DEFINITION ON XML SCHEMA COLLECTION::dbo.MySC FROM schemaUser

  GO

  -- Now schemaUser cannot see the contents of

  -- the collection.

  setuser 'schemaUser'

  I encountered the following error:Setuser failed because of one of the following reasons: the database principal 'schemaUser' does not exist, its corresponding server principal does not have server access, this type of database principal cannot be impersonated, or you do not have permission.

  why is this is so? Is there any solution.

  thanks

• masoudk1990

  SSCommitted

  Points: 1651

  More actions

  September 2, 2013 at 11:01 pm

  #1646854

  Im not sure if my theory is correct, but according to error you gave to us if 'schemaUser' database exists its good to try this:

  1. Login to "sql server management studio" with sql authentication.

  2. Find security folder in object explorer.

  3. Open it and find Login folder.

  4. Right click on your user and choose properties.

  5. On "User mapping" tab give appropriate permissions to your user for 'schemaUser' database.

  Hope this help.

  ___________________________________
  Computer Enterprise Masoud Keshavarz
  I don't care about hell.
  If I go there I've played enough Diablo to know how to fight my way out.