August 31, 2013 at 5:38 am
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
September 2, 2013 at 11:01 pm
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.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply