SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Server Permissions in a Stored Procedure with a Certificate


Server Permissions in a Stored Procedure with a Certificate

Author
Message
Pridmore_James
Pridmore_James
SSC Veteran
SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)

Group: General Forum Members
Points: 214 Visits: 185
Hi all,

I'm just learning about signing stored procedures with certificates and I've been through multiple examples of how to set Server level permissions on a Stored Procedure. I'm really struggling to get Create Login permissions working. I'm sure it'll be something daft that I'm doing, but I can't see it at the minute and it's driving me crazy!

The basic problem, is that I can create a Login/User with the correct privelages and execute the code as that login, and it works fine.
If I create the login from a certificate from master database, grant him Alter Any Login permissions, export/import the certificate into my database and sign a stored procedure with the certificate, I have no permissions to create a login when executing the stored procedure with a database user. I've even tried in desperation elevating the certificate login to sysAdmin (on my development server of course) and still the permissions aren't filtering through.

Here's some code to demonstrate the problem I have:

-- Set Up Environment

Use Master;
-- Create the certificate in the Master db
CREATE CERTIFICATE permissions_test
ENCRYPTION BY PASSWORD = 'W0bJr&xQ4eGsaFJ4WMVWcy'
WITH SUBJECT = 'Test signing stored proc to create server login',
EXPIRY_DATE = '01/01/2025';
go

-- Create login and assign Alter Any Login permissions
CREATE LOGIN permissions_test_Login FROM CERTIFICATE permissions_test
go

Grant ALTER ANY LOGIN TO permissions_test_Login
go

-- Export Certificate
BACKUP CERTIFICATE permissions_test TO FILE = 'C:\temp\permissions_test.cer'
WITH PRIVATE KEY (FILE = 'C:\temp\permissions_test.pvk' ,
ENCRYPTION BY PASSWORD = 'BackupKey123',
DECRYPTION BY PASSWORD = 'W0bJr&xQ4eGsaFJ4WMVWcy');
go

CREATE DATABASE permissions_test_db
go

Use permissions_test_db
go

-- Import Certificate
CREATE CERTIFICATE permissions_test FROM FILE = 'C:\temp\permissions_test.cer'
WITH PRIVATE KEY (FILE = 'C:\temp\permissions_test.pvk',
DECRYPTION BY PASSWORD = 'BackupKey123',
ENCRYPTION BY PASSWORD = 'W0bJr&xQ4eGsaFJ4WMVWcy')
go

-- Clean up files
EXEC xp_cmdshell 'DEL C:\temp\permissions_test.*'
go

-- In case we need database permissions too
CREATE USER permissions_test_User FOR LOGIN permissions_test_Login;
go

-- Create procedure that tried to create a login using dynamic sql
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create PROCEDURE [dbo].[Test_Create_Logon]
AS
BEGIN

SET NOCOUNT ON;

DECLARE @sql nvarchar(500)

Select @sql = 'CREATE LOGIN AAAAAAAA WITH PASSWORD = ''Letmein123'''
Exec sp_executesql @sql

END
go

-- Create a test user and grant execure permissions
CREATE USER execute_proc_user WITHOUT LOGIN
go

Grant Execute On [dbo].[Test_Create_Logon] To execute_proc_user
go




-- Test the code without signing

Execute as User = 'execute_proc_user';
go
select current_user
go
Execute [dbo].[Test_Create_Logon]
go
-- Fails with permission problem
--Msg 15247, Level 16, State 1, Line 1
--User does not have permission to perform this action.



-- Test the code after signing

revert
go
select current_user
go

ADD SIGNATURE TO [dbo].[Test_Create_Logon] BY CERTIFICATE permissions_test
WITH PASSWORD = 'W0bJr&xQ4eGsaFJ4WMVWcy'
go

-- Test with the signature on the stored procedure
Execute as User = 'execute_proc_user';
go
select current_user
go
Execute [dbo].[Test_Create_Logon]
go
-- Still no permissions to execute.....
--Msg 15247, Level 16, State 1, Line 1
--User does not have permission to perform this action.
revert
select current_user





-- Tidy up

Drop User execute_proc_user
go
Drop PROCEDURE [dbo].[Test_Create_Logon]
go
Drop User permissions_test_User
go
Drop CERTIFICATE permissions_test
go

Use Master
Go
Drop Login permissions_test_Login
go
Drop CERTIFICATE permissions_test
go
Drop DATABASE permissions_test_db



Any help on where I'm going wrong would be really appreciated!!

Can anyone tell me if they get a "User does not have permission to perform this action." after signing the certificate too?
Pridmore_James
Pridmore_James
SSC Veteran
SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)

Group: General Forum Members
Points: 214 Visits: 185
Managed to fix this myself, if anyone else has the same problem here's the problem with my code.

In order for the server permissions to work using certificates, you need to create database user from a login, and instead of Executing as User = .... you need to Execute as Login = .....

Hey presto, no permissions problems.

The code below works :-)


Use Master;
go

-- Create a Server Login, this will be the login that executes the stored procedure
CREATE LOGIN execute_proc_login WITH PASSWORD = 'tmrRaYP2nXVNhUJMw&z766vO9v4A1Ra3dRDuBcjkDd'
go

-- Create our test database
CREATE DATABASE permissions_test_db
go

-- Create the certificate in the Master db
CREATE CERTIFICATE permissions_test
ENCRYPTION BY PASSWORD = 'W0bJr&xQ4eGsaFJ4WMVWcy'
WITH SUBJECT = 'Test signing stored proc to create server login',
EXPIRY_DATE = '01/01/2025';
go

-- Create login and assign Alter Any Login permissions
CREATE LOGIN permissions_test_Login FROM CERTIFICATE permissions_test
go

Grant ALTER ANY LOGIN TO permissions_test_Login
go

-- Export Certificate
BACKUP CERTIFICATE permissions_test TO FILE = 'C:\temp\permissions_test.cer'
WITH PRIVATE KEY (FILE = 'C:\temp\permissions_test.pvk' ,
ENCRYPTION BY PASSWORD = 'BackupKey123',
DECRYPTION BY PASSWORD = 'W0bJr&xQ4eGsaFJ4WMVWcy');
go


-- Switch to test database
Use permissions_test_db
go

-- Create a user in the database from the login that will run the sp
CREATE USER execute_proc_user For LOGIN execute_proc_login
go

-- Create procedure that tries to create a login using dynamic sql
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create PROCEDURE [dbo].[Test_Create_Logon]
AS
BEGIN

SET NOCOUNT ON;

DECLARE @sql nvarchar(500)

Select @sql = 'CREATE LOGIN AAAAAAAA WITH PASSWORD = ''Letmein123'''
Exec sp_executesql @sql

END
go

-- Grant permissions to execute the stored procedure
Grant Execute On [dbo].[Test_Create_Logon] To execute_proc_user
go

-- Import Certificate
CREATE CERTIFICATE permissions_test FROM FILE = 'C:\temp\permissions_test.cer'
WITH PRIVATE KEY (FILE = 'C:\temp\permissions_test.pvk',
DECRYPTION BY PASSWORD = 'BackupKey123',
ENCRYPTION BY PASSWORD = 'W0bJr&xQ4eGsaFJ4WMVWcy')
go

-- Clean up files
EXEC xp_cmdshell 'DEL C:\temp\permissions_test.*'
go

-- In case we need database permissions too, eg. Grant select on schema......
CREATE USER permissions_test_user FOR CERTIFICATE permissions_test
go

ADD SIGNATURE TO [dbo].[Test_Create_Logon] BY CERTIFICATE permissions_test
WITH PASSWORD = 'W0bJr&xQ4eGsaFJ4WMVWcy'
go


Execute as Login = 'execute_proc_login';
go
select current_user
go
Execute [dbo].[Test_Create_Logon]
go
-- This time no error!!!!!!

revert
go
Select current_user
go
Drop User execute_proc_user
go
Drop PROCEDURE [dbo].[Test_Create_Logon]
go
Drop User permissions_test_User
go
Drop CERTIFICATE permissions_test
go

Use Master
Go
Drop Login permissions_test_Login
go
Drop LOGIN execute_proc_login
go
Drop LOGIN AAAAAAAA -- Need to clean this up too now :-)
go
Drop CERTIFICATE permissions_test
go
Drop DATABASE permissions_test_db



Knew it would be something simple.........
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum







































































































































































SQLServerCentral


Search