SQL Server I can see stored procedure with VBA in Excel but user group cannot
I created a stored procedure and can execute it with the VBA below. I gave a user group (of which I am a member) Execute permission. All members of that group except me get the error 'could not find stored procedure' when running the VBA. The users can log in through SSMS and execute the stored procedure by right clicking it.
When I look at the Effective tab where I granted Execute permissions there is an error: Cannot execute as the server principal because the principal "MyUserGroup" does not exist, this type of principal cannot be impersonated, or you do not have permission.
The SQL admin told me that my group does have DBO level permissions. What am I missing? Why can't they find the stored procedure when they execute the Excel VBA? Or is that error telling me that I cannot grant them permission when I should be able to?
Excel VBA to call stored procedure:
Function Sproc()
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim cmd As ADODB.Command
Dim ConnectionString As String
Dim StrSproc As String
Set cnn = New ADODB.Connection
cnn.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=ThisDatabase;Data Source=ThisSource;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=ThisSourceID;Use Encryption for Data=False;Tag with column collation when possible=False"
cnn.Open ConnectionString
cnn.CommandTimeout = 900
Set rst = New ADODB.Recordset
StrSproc = "ExportListToExcel"
rst.ActiveConnection = cnn
rst.Open StrSproc
For i = 0 To rst.Fields.Count - 1
Cells(1, i + 1).Value = rst.Fields(i).Name
Next i
Cells(2, 1).CopyFromRecordset rst
Exit Function
sql-server vba stored-procedures ssms asked Jan 2 at 23:53
CrashCrash
105
add a comment |
I created a stored procedure and can execute it with the VBA below. I gave a user group (of which I am a member) Execute permission. All members of that group except me get the error 'could not find stored procedure' when running the VBA. The users can log in through SSMS and execute the stored procedure by right clicking it.
When I look at the Effective tab where I granted Execute permissions there is an error: Cannot execute as the server principal because the principal "MyUserGroup" does not exist, this type of principal cannot be impersonated, or you do not have permission.
The SQL admin told me that my group does have DBO level permissions. What am I missing? Why can't they find the stored procedure when they execute the Excel VBA? Or is that error telling me that I cannot grant them permission when I should be able to?
Excel VBA to call stored procedure:
Function Sproc()
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim cmd As ADODB.Command
Dim ConnectionString As String
Dim StrSproc As String
Set cnn = New ADODB.Connection
cnn.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=ThisDatabase;Data Source=ThisSource;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=ThisSourceID;Use Encryption for Data=False;Tag with column collation when possible=False"
cnn.Open ConnectionString
cnn.CommandTimeout = 900
Set rst = New ADODB.Recordset
StrSproc = "ExportListToExcel"
rst.ActiveConnection = cnn
rst.Open StrSproc
For i = 0 To rst.Fields.Count - 1
Cells(1, i + 1).Value = rst.Fields(i).Name
Next i
Cells(2, 1).CopyFromRecordset rst
Exit Function
asked Jan 2 at 23:53
CrashCrash
105
add a comment |
I created a stored procedure and can execute it with the VBA below. I gave a user group (of which I am a member) Execute permission. All members of that group except me get the error 'could not find stored procedure' when running the VBA. The users can log in through SSMS and execute the stored procedure by right clicking it.
When I look at the Effective tab where I granted Execute permissions there is an error: Cannot execute as the server principal because the principal "MyUserGroup" does not exist, this type of principal cannot be impersonated, or you do not have permission.
The SQL admin told me that my group does have DBO level permissions. What am I missing? Why can't they find the stored procedure when they execute the Excel VBA? Or is that error telling me that I cannot grant them permission when I should be able to?
Excel VBA to call stored procedure:
Function Sproc()
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim cmd As ADODB.Command
Dim ConnectionString As String
Dim StrSproc As String
Set cnn = New ADODB.Connection
cnn.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=ThisDatabase;Data Source=ThisSource;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=ThisSourceID;Use Encryption for Data=False;Tag with column collation when possible=False"
cnn.Open ConnectionString
cnn.CommandTimeout = 900
Set rst = New ADODB.Recordset
StrSproc = "ExportListToExcel"
rst.ActiveConnection = cnn
rst.Open StrSproc
For i = 0 To rst.Fields.Count - 1
Cells(1, i + 1).Value = rst.Fields(i).Name
Next i
Cells(2, 1).CopyFromRecordset rst
Exit Function
asked Jan 2 at 23:53
CrashCrash
105
I created a stored procedure and can execute it with the VBA below. I gave a user group (of which I am a member) Execute permission. All members of that group except me get the error 'could not find stored procedure' when running the VBA. The users can log in through SSMS and execute the stored procedure by right clicking it.
When I look at the Effective tab where I granted Execute permissions there is an error: Cannot execute as the server principal because the principal "MyUserGroup" does not exist, this type of principal cannot be impersonated, or you do not have permission.
The SQL admin told me that my group does have DBO level permissions. What am I missing? Why can't they find the stored procedure when they execute the Excel VBA? Or is that error telling me that I cannot grant them permission when I should be able to?
Excel VBA to call stored procedure:
Function Sproc()
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim cmd As ADODB.Command
Dim ConnectionString As String
Dim StrSproc As String
Set cnn = New ADODB.Connection
cnn.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=ThisDatabase;Data Source=ThisSource;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=ThisSourceID;Use Encryption for Data=False;Tag with column collation when possible=False"
cnn.Open ConnectionString
cnn.CommandTimeout = 900
Set rst = New ADODB.Recordset
StrSproc = "ExportListToExcel"
rst.ActiveConnection = cnn
rst.Open StrSproc
For i = 0 To rst.Fields.Count - 1
Cells(1, i + 1).Value = rst.Fields(i).Name
Next i
Cells(2, 1).CopyFromRecordset rst
Exit Function
asked Jan 2 at 23:53
CrashCrash
105
asked Jan 2 at 23:53
CrashCrash
105
asked Jan 2 at 23:53
CrashCrash
105
asked Jan 2 at 23:53
CrashCrash
105
asked Jan 2 at 23:53
CrashCrash
105
105
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
StrSproc = "ExportListToExcel"
Name resolution is the first thing rule out.
The name, without schema-qualification will be resolved first in the user's default schema, and then in dbo schema.
So schema-qualify the procedure name to be sure.
answered Jan 3 at 0:37
David Browne - MicrosoftDavid Browne - Microsoft
16.3k2727
Happy New Year David Browne! Thank you!
– Crash
Jan 3 at 0:55
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54014696%2fsql-server-i-can-see-stored-procedure-with-vba-in-excel-but-user-group-cannot%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
StrSproc = "ExportListToExcel"
Name resolution is the first thing rule out.
The name, without schema-qualification will be resolved first in the user's default schema, and then in dbo schema.
So schema-qualify the procedure name to be sure.
answered Jan 3 at 0:37
David Browne - MicrosoftDavid Browne - Microsoft
16.3k2727
Happy New Year David Browne! Thank you!
– Crash
Jan 3 at 0:55
add a comment |
StrSproc = "ExportListToExcel"
Name resolution is the first thing rule out.
The name, without schema-qualification will be resolved first in the user's default schema, and then in dbo schema.
So schema-qualify the procedure name to be sure.
answered Jan 3 at 0:37
David Browne - MicrosoftDavid Browne - Microsoft
16.3k2727
Happy New Year David Browne! Thank you!
– Crash
Jan 3 at 0:55
add a comment |
StrSproc = "ExportListToExcel"
Name resolution is the first thing rule out.
The name, without schema-qualification will be resolved first in the user's default schema, and then in dbo schema.
So schema-qualify the procedure name to be sure.
answered Jan 3 at 0:37
David Browne - MicrosoftDavid Browne - Microsoft
16.3k2727
StrSproc = "ExportListToExcel"
Name resolution is the first thing rule out.
The name, without schema-qualification will be resolved first in the user's default schema, and then in dbo schema.
So schema-qualify the procedure name to be sure.
answered Jan 3 at 0:37
David Browne - MicrosoftDavid Browne - Microsoft
16.3k2727
answered Jan 3 at 0:37
David Browne - MicrosoftDavid Browne - Microsoft
16.3k2727
answered Jan 3 at 0:37
David Browne - MicrosoftDavid Browne - Microsoft
16.3k2727
answered Jan 3 at 0:37
David Browne - MicrosoftDavid Browne - Microsoft
16.3k2727
16.3k2727
Happy New Year David Browne! Thank you!
– Crash
Jan 3 at 0:55
add a comment |
Happy New Year David Browne! Thank you!
– Crash
Jan 3 at 0:55
Happy New Year David Browne! Thank you!
– Crash
Jan 3 at 0:55
Happy New Year David Browne! Thank you!
– Crash
Jan 3 at 0:55
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54014696%2fsql-server-i-can-see-stored-procedure-with-vba-in-excel-but-user-group-cannot%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
