I like using Policy-Based Management to do some simple "everything's okay" sanity checks and email me if something goes out of spec. Typically, I validate the time since the last full backup, both to verify backups are running on schedule, and also to make sure that newly created databases are being included in backups. This works perfectly fine on a standalone server. However, we recently deployed a two-node Always On Availability Group. It's configured to run backups on the current primary node. As you're probably aware, backup history is stored in msdb on the server that performs the backup. This causes a problem with the @LastBackupDate property, which only checks the local backup history for the server the policy is being evaluated on. After a failover, the backup policies almost immediately go out of compliance, as the backups have been running from the other server for however long it was primary (most likely for longer than the span that the policy is checking). Is there any reasonably simple way to make these policy checks Availability-Group-aware? Or am I going to have to look for some other backup monitoring solution?

How can I create a policy to prevent a field from being updated? I am thinking something like this:

CREATE POLICY "test" ON posts.created_at FOR UPDATE WITH CHECK (exists(created_at));

Obviously this does not work, but I would like a way for this to work as long as there is no created_at coming into the UPDATE fields. J

I have a database structure with lots of tables related to eachother. For the sake of this question let's simplify it a bit to a project-management idea: Organizations have Departments, Departments have Projects and Projects have Tasks. And then there's users, which are member of an Organization. Here's an Entity Relationship Diagram to visualize it. https://drawsql.app/teams/riskchallenger/diagrams/policy-example Now for the problem: I want to use RLS to make sure all data is properly protected. Users should always only be able to select/insert/update/delete data for their own organization. So I started out with the following Postgres policy:

CREATE POLICY "Query data of own organization only" ON "Organizations"
AS PERMISSIVE FOR ALL
TO public
USING (auth.uid() = user_id)

Note: auth.uid() is the user-id retrieved from the session (using Supabase). Now how do I protect the other tables such as Tasks?

I have a jump\terminal server to administer 100 SQL Servers and want to evaluate my locally created policies across all the servers by using the Central Management Server but WITHOUT: 1) creating the policies on the target servers and 2) using the SSMS. I need it to be run either with Powershell or T-SQL, doesn't really matter. I was trying to achieve this by iterating through a CSV file for each line holding the hostnames or by querying msdb.dbo.sysmanagement_shared_registered_servers system table but so far no luck. Any way to accomplish this feat?

I want to apply a specific rule when somebody create new column in SQL Server 2016 Enterprise. For example: **NR**_NameColumn = **int** **IS**_Deleted = **bit** **DT**_update = **Date** **ID**_User = **int** and etc... How can I apply it?

I'm trying to establish the best practice standards for configuring service account permissions in Group Policy. Based on Microsoft documentation, SQL Server setup requests permissions for the per-service SIDs or local Windows groups used by SQL Server components. E.g. SQL Server Database Engine: **************************** The Default instance: NT SERVICE\MSSQLSERVER.Named instance: NT Service\MSSQL$ is granted the permissions below during SQL Server setup. The local group policy permissions are visible under user rights assignment. · Log on as a service (SeServiceLogonRight) ·Replace a process-level token (SeAssignPrimaryTokenPrivilege) · Bypass traverse checking (SeChangeNotifyPrivilege) ·Adjust memory quotas for a process (SeIncreaseQuotaPrivilege) SQL Server Agent ***************** Default instance: NT Service\SQLSERVERAGENT. Named instance: NT Named instance: NT Service\SQLAGENT$.) is granted the following permissions during setup. ·Log on as a service (SeServiceLogonRight) · Replace a process-level token (SeAssignPrimaryTokenPrivilege) ·Bypass traverse checking (SeChangeNotifyPrivilege) ·Adjust memory quotas for a process (SeIncreaseQuotaPrivilege) I also noticed that the SQL Service and Agent start-up accounts are also automatically granted “Log on as a service” permissions during setup. Question ********** Is anyone using domain level group policy to apply the above permissions to the service start-up accounts-(e.g. gMSA’s) + per service SID? Trying to determine if there is any benefit of using domain group policy to enforce these permissions on SQL Server VM’s rather than relying on the local group policy permissions assigned by SQL Server during setup ?

I am using row level security with function, for example

CREATE POLICY person_select_policy ON core.person FOR SELECT USING (core.person_policy_check(id, 'read') = TRUE);

Is it possible to pass **whole row** to the function? So the function would have access to all columns of the row without having to name them all as arguments. That way the function could accept core.person as parameter or at least a record type.

So i'm trying to configure the different accesses for mutliple users to my postgres/postgis database. Basically I have 1 manager user and 4 weak users accessing the db. What I would like to do is to prevent a weak user to be able to delete entries that have been made by an another user in the database. BUT all users should still be able to see all entries from all users in the db. So i'm wondering what combination of permissive and restrictive rls policies i should set up to make it work? Thanks for your help guys!

I used PBM to create a condition to blocking create tables if nomenclature no haven't the prefix tbl: EXEC msdb.dbo.sp_syspolicy_update_condition @condition_id=24, @description=N'Necessário acrescentar o prefixo ''tbl''', @facet=N'IMultipartNameFacet', @expression=N' Bool LIKE 2 String Name String System.String tbl% ', @is_name_condition=2, @obj_name=N'tbl%' GO I applied this condition into my tables in the DB "cadastro" EXEC msdb.dbo.sp_syspolicy_update_target_set @target_set_id=65, @enabled=False EXEC msdb.dbo.sp_syspolicy_update_target_set_level @target_set_id=65, @type_skeleton=N'Server/Database/Sequence', @condition_name=N'' EXEC msdb.dbo.sp_syspolicy_update_target_set_level @target_set_id=65, @type_skeleton=N'Server/Database', @condition_name=N'' ... EXEC msdb.dbo.sp_syspolicy_update_target_set @target_set_id=72, @enabled=False EXEC msdb.dbo.sp_syspolicy_update_target_set_level @target_set_id=72, @type_skeleton=N'Server/Database/XmlSchemaCollection', @condition_name=N'' EXEC msdb.dbo.sp_syspolicy_update_target_set_level @target_set_id=72, @type_skeleton=N'Server/Database', @condition_name=N'' GO EXEC msdb.dbo.sp_syspolicy_update_policy @policy_id=37, @execution_mode=1, @schedule_uid=N'00000000-0000-0000-0000-000000000000' GO I setted **Evaluation Mode** like **On change: prevent**. If I try execute this script: create table tblteste( col1 int ); SSMS will return this error: Policy 'NomeDeTabela' has been violated by 'SQLSERVER:\SQL\XXX-XXX-XX\DEFAULT\Databases\cadastro\Tables\dbo.tblteste'. This transaction will be rolled back. Policy condition: '@Name LIKE 'tbl%'' Policy description: '' Additional help: 'Ausência de prefixo 'tbl' no nome da tabela' : '' Statement: 'create table tblteste( col1 int )'. Msg 515, Level 16, State 2, Procedure msdb.sys.sp_syspolicy_execute_policy, Line 69 [Batch Start Line 0] Cannot insert the value NULL into column 'target_query_expression', table 'msdb.dbo.syspolicy_policy_execution_history_details_internal'; column does not allow nulls. INSERT fails. The statement has been terminated. The nomenclature of table meets established policy. I followed step-by-step from Microsoft SQL Server documentation (https://learn.microsoft.com/en-us/sql/relational-databases/policy-based-management/lesson-2-create-and-apply-a-naming-standards-policy?view=sql-server-ver15) but the problem persist. Name and version information: Microsoft SQL Server Management Studio 14.0.17289.0 Microsoft Analysis Services Client Tools 14.0.1016.283 Microsoft Data Access Components (MDAC) 10.0.14393.0 Microsoft MSXML 3.0 6.0 Microsoft Internet Explorer 9.11.14393.0 Microsoft .NET Framework 4.0.30319.42000 Operating System 6.3.14393 Anywhone pass this problem before?

I used PBM to manage my policy against my user DBs. The Condition is: Declare @condition_id int EXEC msdb.dbo.sp_syspolicy_add_condition @name=N'SP_Naming_convention', @description=N'', @facet=N'StoredProcedure', @expression=N' Bool LIKE 2 String Name String System.String usp_% ', @is_name_condition=2, @obj_name=N'usp_%', @condition_id=@condition_id OUTPUT Select @condition_id GO The policy is: Declare @object_set_id int EXEC msdb.dbo.sp_syspolicy_add_object_set @object_set_name=N'SP_Naming_ObjectSet', @facet=N'StoredProcedure', @object_set_id=@object_set_id OUTPUT Select @object_set_id Declare @target_set_id int EXEC msdb.dbo.sp_syspolicy_add_target_set @object_set_name=N'SP_Naming_ObjectSet', @type_skeleton=N'Server/Database/StoredProcedure', @type=N'PROCEDURE', @enabled=True, @target_set_id=@target_set_id OUTPUT Select @target_set_id EXEC msdb.dbo.sp_syspolicy_add_target_set_level @target_set_id=@target_set_id, @type_skeleton=N'Server/Database/StoredProcedure', @level_name=N'StoredProcedure', @condition_name=N'', @target_set_level_id=0 EXEC msdb.dbo.sp_syspolicy_add_target_set_level @target_set_id=@target_set_id, @type_skeleton=N'Server/Database', @level_name=N'Database', @condition_name=N'', @target_set_level_id=0 GO Declare @policy_id int EXEC msdb.dbo.sp_syspolicy_add_policy @name=N'SP_Naming', @condition_name=N'SP_Naming_convention', @policy_category=N'', @description=N'Get out of my box you DEV!', @help_text=N'Stop doing this!', @help_link=N'', @schedule_uid=N'00000000-0000-0000-0000-000000000000', @execution_mode=1, @is_enabled=True, @policy_id=@policy_id OUTPUT, @root_condition_name=N'', @object_set=N'SP_Naming_ObjectSet' Select @policy_id GO I tried to create an SP named usp_whatever: create proc usp_whatever as begin select @@VERSION end But the policy still blocked mine... Any thought? Thanks,

We have evaluated the Microsoft "best practice" policies (located at C:\Program Files (x86)\Microsoft SQL Server\number\Tools\Policies\DatabaseEngine\1033) on each of our SQL Server instances (example here ). The following issue has been flagged up to us: This provides a link to a Microsoft page which states the following as being best practice: > Do not grant server permissions to the server public role. On reviewing the security, we can see that the public role has server permissions to view any database by default. We have run the following and re-evaluated the policy, but the issue is still flagged up. REVOKE VIEW ANY DATABASE TO [public] AS [sa]; The only other permissions attributed to this server role are endpoint permissions to be able to connect via TCP/VIA/Named Pipes/Local Machine. If we remove these, surely the role will be superfluous. **QUESTIONS** - Should we be concerned about this? Is this something we really should look to address? - What else do we need to do to properly resolve this issue? - Are there any implications for doing this or not doing this?

In SQL Server 2012, I had a policy set to not allow spaces in a table name. However, when I use the same policy in SQL Server 2016, I get an error. Here is the code for the condition: DECLARE @condition_id INT EXEC msdb.dbo.sp_syspolicy_add_condition @name=N'No Spaces', @description=N'No spaces in table names.', @facet=N'IMultipartNameFacet', @expression=N' Bool NOT_LIKE 2 String Name String System.String % % ', @is_name_condition=4, @obj_name=N'% %', @condition_id=@condition_id OUTPUT SELECT @condition_id Here is the code for the policy: DECLARE @object_set_id INT EXEC msdb.dbo.sp_syspolicy_add_object_set @object_set_name=N'Table Names_ObjectSet', @facet=N'IMultipartNameFacet', @object_set_id=@object_set_id OUTPUT SELECT @object_set_id DECLARE @target_set_id INT EXEC msdb.dbo.sp_syspolicy_add_target_set @object_set_name=N'Table Names_ObjectSet', @type_skeleton=N'Server/Database/Sequence', @type=N'SEQUENCE', @enabled=False, @target_set_id=@target_set_id OUTPUT SELECT @target_set_id EXEC msdb.dbo.sp_syspolicy_add_target_set_level @target_set_id=@target_set_id, @type_skeleton=N'Server/Database', @level_name=N'Database', @condition_name=N'', @target_set_level_id=0 EXEC msdb.dbo.sp_syspolicy_add_target_set_level @target_set_id=@target_set_id, @type_skeleton=N'Server/Database/Sequence', @level_name=N'Sequence', @condition_name=N'', @target_set_level_id=0 EXEC msdb.dbo.sp_syspolicy_add_target_set @object_set_name=N'Table Names_ObjectSet', @type_skeleton=N'Server/Database/StoredProcedure', @type=N'PROCEDURE', @enabled=False, @target_set_id=@target_set_id OUTPUT SELECT @target_set_id EXEC msdb.dbo.sp_syspolicy_add_target_set_level @target_set_id=@target_set_id, @type_skeleton=N'Server/Database', @level_name=N'Database', @condition_name=N'', @target_set_level_id=0 EXEC msdb.dbo.sp_syspolicy_add_target_set_level @target_set_id=@target_set_id, @type_skeleton=N'Server/Database/StoredProcedure', @level_name=N'StoredProcedure', @condition_name=N'', @target_set_level_id=0 EXEC msdb.dbo.sp_syspolicy_add_target_set @object_set_name=N'Table Names_ObjectSet', @type_skeleton=N'Server/Database/Synonym', @type=N'SYNONYM', @enabled=False, @target_set_id=@target_set_id OUTPUT SELECT @target_set_id EXEC msdb.dbo.sp_syspolicy_add_target_set_level @target_set_id=@target_set_id, @type_skeleton=N'Server/Database', @level_name=N'Database', @condition_name=N'', @target_set_level_id=0 EXEC msdb.dbo.sp_syspolicy_add_target_set_level @target_set_id=@target_set_id, @type_skeleton=N'Server/Database/Synonym', @level_name=N'Synonym', @condition_name=N'', @target_set_level_id=0 EXEC msdb.dbo.sp_syspolicy_add_target_set @object_set_name=N'Table Names_ObjectSet', @type_skeleton=N'Server/Database/Table', @type=N'TABLE', @enabled=True, @target_set_id=@target_set_id OUTPUT SELECT @target_set_id EXEC msdb.dbo.sp_syspolicy_add_target_set_level @target_set_id=@target_set_id, @type_skeleton=N'Server/Database', @level_name=N'Database', @condition_name=N'', @target_set_level_id=0 EXEC msdb.dbo.sp_syspolicy_add_target_set_level @target_set_id=@target_set_id, @type_skeleton=N'Server/Database/Table', @level_name=N'Table', @condition_name=N'', @target_set_level_id=0 EXEC msdb.dbo.sp_syspolicy_add_target_set @object_set_name=N'Table Names_ObjectSet', @type_skeleton=N'Server/Database/UserDefinedFunction', @type=N'FUNCTION', @enabled=False, @target_set_id=@target_set_id OUTPUT SELECT @target_set_id EXEC msdb.dbo.sp_syspolicy_add_target_set_level @target_set_id=@target_set_id, @type_skeleton=N'Server/Database', @level_name=N'Database', @condition_name=N'', @target_set_level_id=0 EXEC msdb.dbo.sp_syspolicy_add_target_set_level @target_set_id=@target_set_id, @type_skeleton=N'Server/Database/UserDefinedFunction', @level_name=N'UserDefinedFunction', @condition_name=N'', @target_set_level_id=0 EXEC msdb.dbo.sp_syspolicy_add_target_set @object_set_name=N'Table Names_ObjectSet', @type_skeleton=N'Server/Database/UserDefinedType', @type=N'TYPE', @enabled=False, @target_set_id=@target_set_id OUTPUT SELECT @target_set_id EXEC msdb.dbo.sp_syspolicy_add_target_set_level @target_set_id=@target_set_id, @type_skeleton=N'Server/Database', @level_name=N'Database', @condition_name=N'', @target_set_level_id=0 EXEC msdb.dbo.sp_syspolicy_add_target_set_level @target_set_id=@target_set_id, @type_skeleton=N'Server/Database/UserDefinedType', @level_name=N'UserDefinedType', @condition_name=N'', @target_set_level_id=0 EXEC msdb.dbo.sp_syspolicy_add_target_set @object_set_name=N'Table Names_ObjectSet', @type_skeleton=N'Server/Database/View', @type=N'VIEW', @enabled=False, @target_set_id=@target_set_id OUTPUT SELECT @target_set_id EXEC msdb.dbo.sp_syspolicy_add_target_set_level @target_set_id=@target_set_id, @type_skeleton=N'Server/Database', @level_name=N'Database', @condition_name=N'', @target_set_level_id=0 EXEC msdb.dbo.sp_syspolicy_add_target_set_level @target_set_id=@target_set_id, @type_skeleton=N'Server/Database/View', @level_name=N'View', @condition_name=N'', @target_set_level_id=0 EXEC msdb.dbo.sp_syspolicy_add_target_set @object_set_name=N'Table Names_ObjectSet', @type_skeleton=N'Server/Database/XmlSchemaCollection', @type=N'XMLSCHEMACOLLECTION', @enabled=False, @target_set_id=@target_set_id OUTPUT SELECT @target_set_id EXEC msdb.dbo.sp_syspolicy_add_target_set_level @target_set_id=@target_set_id, @type_skeleton=N'Server/Database', @level_name=N'Database', @condition_name=N'', @target_set_level_id=0 EXEC msdb.dbo.sp_syspolicy_add_target_set_level @target_set_id=@target_set_id, @type_skeleton=N'Server/Database/XmlSchemaCollection', @level_name=N'XmlSchemaCollection', @condition_name=N'', @target_set_level_id=0 GO DECLARE @policy_id INT EXEC msdb.dbo.sp_syspolicy_add_policy @name=N'Table Names', @condition_name=N'No Spaces', @policy_category=N'', @description=N'', @help_text=N'', @help_link=N'', @schedule_uid=N'00000000-0000-0000-0000-000000000000', @execution_mode=1, @is_enabled=True, @policy_id=@policy_id OUTPUT, @root_condition_name=N'', @object_set=N'Table Names_ObjectSet' SELECT @policy_id GO In SQL Server 2012 and 2014, this gives the expected results: CREATE TABLE [test table] (Id INT NULL) > Policy 'Table Names' has been violated by > 'SQLSERVER:\SQL\LSRSQL07\SQL2012\Databases\test\Tables\dbo.test > table'. This transaction will be rolled back. Policy condition: '@Name > NOT LIKE '%[- .]%' AND @Name NOT LIKE '%[^A-Za-z0-9[_]]%'' Policy > description: '' Additional help: '' : '' Statement: 'CREATE TABLE > [test table] (Id INT NULL) '. > Msg 3609, Level 16, State 1, Procedure > sp_syspolicy_dispatch_event, Line 65 [Batch Start Line 48] The > transaction ended in the trigger. The batch has been aborted. And if I run the following code, I get no error: CREATE TABLE [testtable] (Id INT NULL) However, if I run any CREATE TABLE statement, with the policy enabled, on SQL Server 2016, I get the following error: > Policy 'Table Names' has been violated by > 'SQLSERVER:\SQL\LSRSQL07\SQL2016\Databases\test\Tables\dbo.testtable'. > This transaction will be rolled back. Policy condition: '@Name NOT > LIKE '% %'' Policy description: '' Additional help: '' : '' Statement: > 'CREATE TABLE [testtable] (Id INT NULL)'. Msg 515, Level 16, State 2, > Procedure sp_syspolicy_execute_policy, Line 69 [Batch Start Line 44] > Cannot insert the value NULL into column 'target_query_expression', > table 'msdb.dbo.syspolicy_policy_execution_history_details_internal'; > column does not allow nulls. INSERT fails. The statement has been > terminated. In SQL Server 2016, I *cannot create any table*, whether it passes the condition or not. This is SQL Server 2016, SP1, CU3. Any ideas on this? Edit: I am needing the evaluation mode to be "On change: prevent"

I am creating a new policy which shows sproc creation dates. I am based on Stored Procedure facet and appropriate condition is created. Now I want this policy to include only one database in an instance When I select new policy from the dropdown menu, which is next to every keyword, it shows this But my point is not to create a new condition for all databases. My question is, is it possible to to run a policy only for one database in an instance, and if yes, then how? Thanks for your time!

Currently I have a simple schema, a sites table and an items table. Sites own many items, items has a siteId column to store the reference of the site which owns it. I'd like to be able to enforce a condition when querying items, meaning returning an error if it is not queried with a siteId - in practice: - select * from items would fail - select * from items where siteId = 1 would succeed, albeit a site record with id 1 exists This can easily be achieved on an application level. I recently started investigating further into row level policies, roles, functions, etc… which I found very interesting as they enable lower level setups. That is, I'm still new about it and lack understanding/knowledge. I was wondering if the use case described above could simply be solved within SQL (via a combination of policies, functions, etc… or any other Postgres builtin tools)?

I have done some searching but haven't found a solution to my problem. I started with some databases where users didn't care to name PK constraints, foreign keys or indexes, ending up with system generated names like PK__CarRenta__3213E83F2E5BD364. On mssqltips there's a great article by Aaron Bertrand about how to rename these objects using a stored procedure. So that's great...but how do I avoid users or applications creating new objects without names which are then assigned a system generated name by SQL Server? I have already read an article by the same great Aaron Bertrand on how to use policy based management to enforce naming conventions. However it seems that this is only good for notification after the horse has already bolted (or the object has been created). Can you think of a way to automatically roll back statements that try to create an object without a name?

I created a PBM condition and policy in SSMS. The policy runs a T-SQL script using the ExecuteSql function against the Server facet to determine whether all databases that are expected to be a member of an availability group are in fact members of the group. The expected values are stored in a table. SQL below; it executes fine. When I evaluate the policy (right-click | Evaluate) it shows success. The actual and expected values are zero. So far, so good. The policy is configured to be in the Availability Replica warnings category so it can be evaluated by the High Availabiltiy dashboard (or the Test-SqlAvailabilityReplica cmdlet). When I run the High Availabilty dashboard it reports a policy execution running the policy but gives no further detail. See 2nd screen capture in image. When I run the Test-SqlAvailabilityReplica cmdlet in Powershell it reports a value of False for the policy. All other policies report True (3rd screen capture). When I run the cmdlet with the -Verbose parameter I don't see the SQL for my policy being executed but I do see the SQL for the other policies. It appears that is returns False without being evaluated. I'm running as a user with sysadmin. I'm stumped. Why is it returning False when it should be True? Any insight appreciated. Condition: ExecuteSql('numeric', 'SELECT count(*) FROM myDB.dbo.AvailabilityGroups ag INNER JOIN myDB.dbo.AvailabilityGroupDatabases agd ON ag.AvailabilityGroupId = agd.AvailabilityGroupId LEFT JOIN ( SELECT name, database_name FROM master.sys.availability_groups ag INNER JOIN master.sys.availability_databases_cluster adc ON ag.group_id = adc.group_id ) sag ON ag.AvailabilityGroupName = sag.name AND agd.DatabaseName = sag.database_name WHERE sag.name IS NULL AND sag.database_name IS NULL')