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


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


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

Author
Message
Rod
Rod
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16745 Visits: 2405
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. Smile

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.
Steve Jones
Steve Jones
SSC Guru
SSC Guru (350K reputation)SSC Guru (350K reputation)SSC Guru (350K reputation)SSC Guru (350K reputation)SSC Guru (350K reputation)SSC Guru (350K reputation)SSC Guru (350K reputation)SSC Guru (350K reputation)

Group: Administrators
Points: 350206 Visits: 20195
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.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Rod
Rod
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16745 Visits: 2405
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
Rod
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16745 Visits: 2405
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
Steve Jones
SSC Guru
SSC Guru (350K reputation)SSC Guru (350K reputation)SSC Guru (350K reputation)SSC Guru (350K reputation)SSC Guru (350K reputation)SSC Guru (350K reputation)SSC Guru (350K reputation)SSC Guru (350K reputation)

Group: Administrators
Points: 350206 Visits: 20195
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';


Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Rod
Rod
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16745 Visits: 2405
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
Rod
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16745 Visits: 2405
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.
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