Hi.
I am trying to create a new schema in my database (currently only dbo exists). And this is being done via series of stored Procedures that I call from .NET.
My issue is that after creating this new schema, and the new user - i am not able to test SP by impersonating it. Here are the steps:
Create new schema user
exec ('CREATE USER ' + @s_NewSchemaUser + ' WITHOUT LOGIN WITH DEFAULT_SCHEMA = ' + @s_NewSCHEMA)
print 'User created'
exec ('GRANT VIEW DEFINITION ON SCHEMA::' + @s_SRCSCHEMA + ' TO ' + @s_NewSchemaUser)
exec ('GRANT CREATE PROCEDURE TO ' + @s_NewSchemaUser)
exec ('GRANT CREATE TABLE TO ' + @s_NewSchemaUser)
exec ('GRANT CREATE VIEW TO ' + @s_NewSchemaUser)
exec ('GRANT CREATE FUNCTION TO ' + @s_NewSchemaUser)?
Create new schema
exec ('CREATE SCHEMA ' + @NewSchemaName + ' AUTHORIZATION ' + @NewSchemaUser)?
Problem is that I cannot test it. In SSMS I run the following command:
EXECUTE AS LOGIN = @NewSchemaUser;
exec @NewSchemaName.[uspGetReportMyCustomReport]
REVERT;
I get the error:
Cannot execute as the server principal because the principal "new schema user name" does not exist, this type of principal cannot be impersonated, or you do not have permission.
I read on google about the difference between users and logins, and I realize that I cannot impersonate as this user because it does not have an associated login. At least not the way I created it. But I am also not able to map existing login to this user since SSMS complains that there is already a user associated with login.
Any idea what the right way for creating this user will be? so i can impersonate it (it will have lesser privileges then dbo).
May 21, 2021 at 3:39 pm
If you want to be able to impersonate it, you're going to need to create Logins (and now you have a new problem). If you just want different schemas to be accessible by the same user, you don't need new users or impersonation, you just need to set appropriate permissions on the schema.
Did you try 'EXECUTE AS USER ='? Would that help you here?
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
May 21, 2021 at 8:16 pm
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply