What happens if a user tries to execute some SMO object when they have low priorities?

Question

What happens if a user tries to execute some SMO object when they have low priorities?

Rod at work

SSC-Dedicated

Points: 33897
More actions
March 21, 2017 at 3:28 pm

#104479

We've got some roles defined in a database that's associated with an app we're working on. They are defined under the Database Roles, rather than Application Roles. Our intent is to define whether or not a user can execute certain things or not, based upon the role they're in. (Disabling a button if they can't do some things, etc.) At first I thought I could do this using entity framework, but now I'm thinking that won't work. Because of SQL Server Central I know of SMO. It looks like I can use SMO to get what I want. But I have no experience with SMO, so will be here, asking questions. 🙂
The first question concerns whether or not any user can executes a routine that runs SMO? For example, can any user run the code necessary to see whether or not they're in different database roles defined in the database?
Kindest Regards, Rod Connect with me on LinkedIn.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 734418 More actions · Answer 1

Normal SQL Server rights apply. SMO really will end up running some sort of query or RPC to get data, but it should fall under the SQL model. If the user can get the data from SQL Server now, they should be able to get it from SMO.

Rod at work SSC-Dedicated Points: 33897 More actions · Answer 2

So, are you saying that whether I use SMO or not, eventually a SQL SELECT will be performed?\

I posted a similar question in the EF forums on the MSDN Entity Framework forum and someone there recommended that I use the following SELECT:

SELECT p.name AS [Name] ,r.type_desc FROM sys.server_principals r INNER JOIN sys.server_role_members m ON r.principal_id = m.role_principal_id INNER JOIN sys.server_principals p ON p.principal_id = m.member_principal_id WHERE r.type = 'R' and r.name = N'sysadmin'

I've been experimenting with the code and it doesn't seem to give me what I'd hope for. In fact, running the above code exactly as it is given, returns only 1 record:

Name: sa
type_desc: SERVER_ROLE

I'm not familiar with any of those views (which is what I think they are) so I'm just poking around trying to figure them out.

Kindest Regards, Rod Connect with me on LinkedIn.

Rod at work SSC-Dedicated Points: 33897 More actions · Answer 3

Another quick question; am I correct in believing that sys.server_principals and sys.server_role_members are system views that return data relevant to the server and not one of the databases? I'm wondering because we've defined the roles at the database level only. When I perform a SELECT * against both of those views I do not see our database roles at all. I do see server roles listed.

I should mention that I'm doing this on a SQL Server 2008 R2 instance.

Kindest Regards, Rod Connect with me on LinkedIn.

Steve Jones - SSC Editor SSC Guru Points: 734418 More actions · Answer 4

Not necessarily a SELECT. It could be an RPC or other method of accessing the data, but the rights of the user apply. If you have rights to read data, such as the rights of a role, then you can access the data.

Are you trying to determine what rights a particular role has? Whatever account the application has would need rights to access sys.objects, sys.database_principals, and sys.permissions if you want to query. This gets the object, permissions, and type of right. You would really want to add schema in here:
SELECT TOP 10 o.name, dpe.permission_name, dpe.state_desc FROM sys.database_principals dp INNER JOIN sys.database_permissions dpe ON dp.principal_id = dpe.grantee_principal_id INNER JOIN sys.objects o ON dpe.major_id = o.object_id WHERE dp.name = 'SalesUsers';

Rod at work SSC-Dedicated Points: 33897 More actions · Answer 5

I know this website is for DBAs, but I thought I'd share with you all what I've found so far. I'm pursuing using SMO, primarily because it looks like it will give me what I need for databases, not just at the server level. I've found 2 links that are helpful from Microsoft's MSDN website.

Managing Uses, Roles, and Logins and Create a Visual C# SMO Project in Visual Studio .NET. You need to look at those 2 links. After you've done that I'd like to share with you how I've changed the code for C# in the first link. Here it is:

using System;
using System.Data;
using Microsoft.SqlServer.Management.Smo;
namespace SmoListLogins
{
    class Program
    {
        static void Main(string[] args)
        {
            ListLogins();
        }
        static private void ListLogins()
        {
            //Server srv = new Server(); //local database - i.e.: localhost
            Server srv = new Server("YOURDATABASEINSTANCE");
            //Iterate through each database and display.
            foreach (Database db in srv.Databases)
            {
                Console.WriteLine("========");
                Console.WriteLine("Login Mappings for the database: " + db.Name);
                Console.WriteLine(" ");
                //Run the EnumLoginMappings method and return details of database user-login mappings to a DataTable object variable.
                DataTable d;
                try
                {
                    d = db.EnumLoginMappings();
                    //Display the mapping information.
                    foreach (DataRow r in d.Rows)
                    {
                        foreach (DataColumn c in r.Table.Columns)
                        {
                            Console.WriteLine(c.ColumnName + " = " + r[c]);
                        }
                    }
                }
                catch (Exception ex)
                {
                    Console.WriteLine($"Error processing database: {db.Name}");
                    Console.WriteLine($"Error: {ex.Message}");
                    Console.WriteLine();
                }
            }
        }
    }
}

Kindest Regards, Rod Connect with me on LinkedIn.

Rod at work SSC-Dedicated Points: 33897 More actions · Answer 6

Steve, I didn't see your response from March 22 until just now. That does look promising. Your cautionary statements about the user's permissions is making me wonder if it wouldn't be better to have some sort of intermediary service, like a WebAPI service, that has the necessary rights to either read the views you've included or run the SMO. That way we could make it so that the user could pass in a string, which would be their Windows login, then the API would just return a POCO of database roles the user is a member of. I'm pretty sure that not all users would have the necessary rights to run either the SQL query or SMO, so I'm thinking this might be a better solution.

Kindest Regards, Rod Connect with me on LinkedIn.