to do it correctly is not very simple, although once setup, your done.
Data users or application roles default have database scoped permissions. Database users are linked to Logins and therefore can have server wide permissions from their Login. Application roles aren't linked and can only get server wide permission by using a form of impersonation.
Any way to give these server scope permissions by means of impersonating will fail by default.This is by design.
You have 2 options:
- Tell SQL Server that you as a DBA trust any user in that database to have privilegde escalation to server wide permission if impersonation is used. You acomplish this by setting the option
TRUSTWORTHY
to ON
However if you don't fully understand what this option does first read:
SQL Server 2012 Best Practice Whitepaper (Specifically page 18 and 19 about "Database Ownership and Trust")
- Use signed stored procedures to accomplish your task.
I'll first give you the example of option 1:
CREATE LOGIN [appRoleProxy] WITH PASSWORD ='1234abcd!@'
GO
EXEC sp_addsrvrolemember @loginame='appRoleProxy',
@rolename='securityadmin'
GO
CREATE DATABASE [Stefaan]
go
ALTER DATABASE [Stefaan] SET TRUSTWORTHY ON
GO
USE [Stefaan]
GO
--add the proxy account to the dbo role since your stored procedure will be running in the context of this account and is also accessing your user database.
ALTER ROLE [db_owner] ADD MEMBER [appRoleProxy]
GO
CREATE APPLICATION ROLE appRole WITH PASSWORD ='Passw0rd!';
GO
CREATE PROCEDURE dbo.usp_testproc
@LoginName nvarchar(128),
@Password nvarchar(128),
@DBName nvarchar(128)
WITH EXECUTE AS 'appRoleProxy' --Impersonate a SQL server login with the proper server level permissions
AS
DECLARE @SQL varchar(max)
BEGIN
IF NOT EXISTS ( SELECT name
FROM sys.server_principals
WHERE name = @LoginName )
BEGIN
SET @SQL = 'CREATE LOGIN [' + @LoginName + '] WITH PASSWORD = '''
+ @Password + ''', DEFAULT_DATABASE=[' + @DBNAME
+ '], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF';
EXECUTE(@SQL);
END
IF NOT EXISTS ( SELECT name
FROM sys.database_principals
WHERE name = @LoginName )
BEGIN
SET @SQL = 'CREATE USER [' + @LoginName + '] FOR LOGIN ['
+ @LoginName + ']';
EXECUTE(@SQL);
END
IF EXISTS ( SELECT name
FROM sys.server_principals
WHERE name = @LoginName )
BEGIN
EXEC sp_addsrvrolemember @LoginName, 'securityadmin'
END
END
GO
GRANT EXECUTE ON [dbo].[usp_testproc] TO [appRole]
GO
sp_setapprole @rolename='appRole',
@password='Passw0rd!'
EXEC usp_testproc 'testlogin','password23@','stefaan'
Now for an example of option 2 I'd like to point out a answer a gave on another question:
Msg 4834 “You do not have permission to use the bulk load statement”
Thomas has explained why that stored procedure isn't capturing orphaned Windows users, but here is how you can check:
SELECT p.name
FROM database_name.sys.database_principals AS p
WHERE [type] IN (N'U', N'G')
AND NOT EXISTS
(
SELECT 1 FROM sys.server_principals AS sp
WHERE sp.sid = p.sid
);
If you need to do this for all databases, you can generate this dynamically, e.g.
DECLARE @sql NVARCHAR(MAX) = N'';
SELECT @sql += N'SELECT ''' + db.name + ''',p.name
FROM ' + QUOTENAME(db.name) + '.sys.database_principals AS p
WHERE [type] IN (N''U'', N''G'')
AND NOT EXISTS
(
SELECT 1 FROM sys.server_principals AS sp
WHERE sp.sid = p.sid
);'
FROM sys.databases AS db
WHERE [state] = 0;
EXEC sp_executesql @sql;
Best Answer
That might've been a little bit of poor wording on Microsoft's part but basically what they meant is run that query in the context of the database which you want to check if there are Orphaned Users for. So which ever database you run it under, it'll tell you only for that database of there are Orphaned Users.
They clarify that slightly, just below the example query they provide in the article you linked:
So if you have 3 databases on your server, for example, then you'd need to run that query 3 times (once in the context of each database) to verify for all of them.