Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Server Permissions in a Stored Procedure with a Certificate Expand / Collapse
Author
Message
Posted Thursday, February 17, 2011 4:00 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, October 24, 2013 3:18 AM
Points: 84, 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?
Post #1065544
Posted Friday, February 18, 2011 4:56 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, October 24, 2013 3:18 AM
Points: 84, 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.........
Post #1066269
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse