We have set up SQL Server and our Application in such a way that an SQL Server login has … well no permissions when accessing the database using any tool, except our application. This is needed because some people might fire up access (or SQL Server Management Studio) and would be able to manipulate the data in that way.
So we set up an Application Role and gave that Application Role the necessary permissions. Everything works nicely, users can't access our tables unless they use our Application.
Now comes the problem. In our application it should be possible to Create additional logins / users, or set some specific users as being Administrators (only admins can do user related stuff). I'm not 100% certain but in that case I think the user needs SecurityAdmin role. So we have some code in our application which looks like this :
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
The problem is of course that the server_principals is in the Master Database, and there is no way I can give my Application Role those permissions. Is there any way to work-around this problem or an easy way to solve this ?
Well … actually my problem is I will need a good example on how to set this up I guess. For now I think I can set everything up with an AppUser application role and give that role all insert / select / update / execute rights for an application.
But what permissions should I give to the AppAdmin then in order to allow it to Create LOGINs and USERs on the SQL Server and allow them to set others as Admins and change other users passwords. Any suggestions on that, since that involves some stuff in the Master database which is outside of the scope of my Application Role. Or am I missing something?
With my current setup I'm getting : Msg 15151, Level 16, State 1, Line 1 Cannot alter the login 'test.gebruiker', because it does not exist or you do not have permission.